SQL Server Performance Forum – Threads Archive
restoreing database to a new server
suppose we are restoring the database to a new server, the logins are getting created.So when an user want to login to this new database , what ever he used before to getinto that database should have to use the same password.?? So when we restore a database only, tables , logins are created, or what are the other things getting craeted like stored proc, jobs??? thanks in advance
– your users are NOT created by default !!!!<br /><br />select ‘exec sp_addlogin [‘<br />+ name<br />+ ‘],'<br />, password<br />, ‘, @encryptopt=skip_encryption'<br />from master..sysxlogins<br /><br /><br />- You should migrate them yourself and synchronize them !!<br /><br />print ‘print @@servername + ” / ” + db_name()'<br />print ‘go'<br />go<br /><br />declare @username varchar(12<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /><br />declare @Musername varchar(12<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /><br />declare @IsNtName bit<br />declare @sql_stmt varchar(500)<br /><br />–cursor returns with names of each username to be tied to its respective<br />DECLARE user_cursor CURSOR FOR<br /> SELECT su.name as Name, msu.name as MasterName , su.isntname <br />FROM sysusers su<br />left join master.dbo.sysxlogins msu<br /> on upper(su.name) = upper(msu.name)<br /> WHERE su.sid > 0x00<br /> ORDER BY Name<br /><br />–for each user:<br />OPEN user_cursor<br />FETCH NEXT FROM user_cursor INTO @username, @Musername, @IsNtName<br />WHILE @@FETCH_STATUS = 0<br />BEGIN<br /> IF @username NOT IN (‘dbo’, ‘list of names you want to avoid’) — enkel aanvullen indien je een ID niet wenst de synchroniseren<br /> BEGIN<br /> if @Musername is null <br /> begin<br /> if @IsNtName = 1 <br /> begin<br />print ‘if not exists (select * from master.dbo.syslogins where loginname = N”NtDomein**’ + @username + ”’)'<br />print ‘ begin ‘<br />print ‘exec sp_grantlogin N”NtDomein**’ + @username + ””<br />print ‘exec sp_defaultdb N”NtDomein**’ + + @username + ”’, N”’+ db_name() + ””<br />print ‘ end'<br />set @sql_stmt = ‘–Windows account gehad'<br /><br />end<br /> else<br />begin<br /> SELECT @sql_stmt = ‘sp_change_users_login @Action = ”Auto_Fix”,@UserNamePattern = ”’ + @username + ””<br />end<br /> end<br /> else<br /> begin<br /> SELECT @sql_stmt = ‘sp_change_users_login @Action = ”Update_One”,@UserNamePattern = ”’ + @username + ”’, @LoginName = ”’ + @username + ””<br /> end<br /> <br /> PRINT @sql_stmt<br /> print ‘go'<br /> print ‘–*** exec stmt commented !!! ***'<br /> –EXECUTE (@sql_stmt)<br /> END<br /> FETCH NEXT FROM user_cursor INTO @username, @Musername, @IsNtName<br />END –of table-cursor loop<br /><br />–clean up<br />CLOSE user_cursor<br />DEALLOCATE user_cursor<br /><br /><br /><br />
jobs are stored in MSDB.
MSDB is a system-db. This reqieres a different procedure. See BOL ! You should script them and implement them at the new server !
When you restore master:
Your sql server configuration, logins, linked server etc will be copied..along with all users, user and system objects(Tables,procedure, views…udfs….ect…) When you restore msdb: Job, alerts, operators , dts packages, backupdevices and maintenace plans will be copied…along with all users, user and system objects(Tables,procedure, views…udfs….ect…)
User database: All users, user and system objects(Tables,procedure, views…udfs….ect…)
MohammedU.
Moderator
SQL-Server-Performance.com
quote:Originally posted by priyaram
suppose we are restoring the database to a new server, the logins are getting created.
So when an user want to login to this new database , what ever he used before to getinto that database should have to use the same password.?? So when we restore a database only, tables , logins are created, or what are the other things getting craeted like stored proc, jobs??? thanks in advance
So when an user want to login to this new database , what ever he used before to getinto that database should have to use the same password.?? So when we restore a database only, tables , logins are created, or what are the other things getting craeted like stored proc, jobs??? thanks in advance
i am not sure can u restore a master server backup of one instance to another. as far as i know you can not. refer http://msdn2.microsoft.com/en-us/library/ms175535.aspx Actually, the requirement is to restore a database from one server to another. Both have their own Login. When you restore a database it only restore database scopped objects like Table/SP/View/Function/Users/Role/etc. Jobs/Alters/Operator/Backupdevice/DTS/etc are server scopped objects. so it will not be copied when you restore only a User database. And also when u restore a user database logins are not created automatilcally. But the Database will have orphaned users. So you will have to re-map these users to the login or drop and recreate users. Refer :http://www.sqlservercentral.com/columnists/cBunch/movingyouruserswiththeirdatabases.asp
Madhu
Hi, As said , when you restore user database , all the objects of user database with users/roles created in it are also get copied. Refer articlehttp://vyaskn.tripod.com/moving_sql_server.htm by Vyas on moving sql server from one machine to another. Regards Hemantgiri S. Goswami
MS SQL Server MVP
————————-
"Humans don’t have Caliber to PASS TIME , Time it self Pass or Fail Humans" – by Hemantgiri S. Goswami http://hemantgirisgoswami.blogspot.com
Madhu’s reference is correct in this case as few of the machine names are pre-registered on its own instance. In this case you have to rebuild the master database using REBUILDM utility. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
]]>