Moving DB steps | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Moving DB steps

I have a SQL 2K user database running on Machine A (call it db_A). I need<br />to create/move this user database to Machine B (call it db_B).<br /><br />Machine B already has an instance of SQL Server running on it.<br /><br />Would the following steps move the database on Machine A to Machine B?<br /><br />1. Full backup of db_B on Machine B.<br />2. Run sp_detach_db ‘db_B'<br />3. Copy the data and log files from Machine B to location Machine A (E<img src=’/community/emoticons/emotion-7.gif’ alt=’:s’ />qldata).<br />4. Run sp_attach_db ‘db_B’, ‘E:sqldatadb_B_data.mdf’, ‘E:sqldatadb_B_log.ldf'<br />5. Full backup of master<br />6. Full backup of msdb<br />
That’d do it. You could also use backup/restore but your method will be quicker. Don’t forget to transfer logins to – , fix users on – , set windows groups up on – (as appropriate) on the new server. Tom Pullen
DBA, Oxfam GB
Also update statistics after 6).
Luis Martin
Moderator One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided “AS IS” with no warranties for accuracy.
quote:Don’t forget to transfer logins

I only have two logins, on a very simplistic database. Is there a best method to avoid orphaning? Can I just manually create the logins after the sp_attach_db? Or should I create the logins before sp_attach_db?
Best to create them before. SQL Logins will need to be fixed by running (in the attached db), exec sp_change_users_login auto_fix, loginname If it’s Windows users or groups, simply add their logins and there’s no need to do anything else. Tom Pullen
DBA, Oxfam GB
I prefer to run the logins after I attach the databases. Run sp_help_revlogin on the original server. After the databases are attached, run the resulting script on the destination server. This always works, so it’s my preferred method for transferring the logins. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.
There were many threads in this forum about the same topic, search for more information.
If you have few logins to migrate then simply write a script to drop and add them when the restore is finished on other server, for ease. Satya SKJ
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.