Moving Database from one SQL Server to Another 1

[bgfs]

I Plan to move a database from one SQL server to another using backup and restore. Can anyone think of any issues I might come up against

 

[zircon06]

Just use method of Deattach and attaching method of database you should be fine

 

[ptheriault]

No matter which method you use your logins will be out of sync between the two servers. you will need to run...

--run from user db
sp_change_users_login 'REPORT'

once you restore the database. it will show you the logins where the sid in the db doesn't match the sid in the master db. You can correct them with

sp_change_users_login 'update_one', 'login_name', 'login_name'

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[fuzzyocelot]

Also, if the SQL logins from the source server aren't on the destination server, you'll need to recreate those logins. To do so, you can run sp_help_revlogin.

use master
go
sp_help_revlogin

If you don't have it, you can get it at:

http://support.microsoft.com/kb/246133

Please note that it lists the code for SQL 2000. There is a link on the same page, though, that will take you to the SQL 2005 version.

 

[ptheriault]

I have written an FAQ that has both versions

faq962-6608

If you do move your logins using sp_help_revlogin you won't need to run sp_change_users_login

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[fuzzyocelot]

I didn't realize you had written an FAQ on it, Paul. Very cool!