Best methods for moving sql server database to new server for least amount of down time
Hi. It's been a long time since i've had to move a sql server database and the sql server connections were always already set up for us by the Network techs. This time, sql server will be installed on the new server (same version 2008 R2) but I will probably have to set up the connection and initial security etc. I've been looking at the methods now available to do this and having a hard time figuring out the best one to use.. We will be keeping the old server/sql server running (being used) while we set everything up on the new. In a few days when we know everything is working, we will want to bring the new up to date with the old, disconnect the old server and start everyone using the new. So which would be the best method to use knowing that I need to do it again in a few days and keep the down time very low?
1. Using the Move/Copy wizard.. This option looks as if I'll be able to copy the database to the new server as long as I have the initial security ... owner and sysadmin rights set etc. It appears that I can also bring over the log ins etc.
2. Full Backup/Restore method.. It looks as if this will NOT bring over all the log ins, security etc. True?
3. Detach/Attach method?
If I was able to use #1 and copy to the new server/instance etc., what would be the best way to bring the data up to current when we go live in a few days? I guess it all depends too if I have any problems initially with anything but I'm just looking for anyone with experience to point me in the right direction. Thanks!
1. Using the Move/Copy wizard.. This option looks as if I'll be able to copy the database to the new server as long as I have the initial security ... owner and sysadmin rights set etc. It appears that I can also bring over the log ins etc.
2. Full Backup/Restore method.. It looks as if this will NOT bring over all the log ins, security etc. True?
3. Detach/Attach method?
If I was able to use #1 and copy to the new server/instance etc., what would be the best way to bring the data up to current when we go live in a few days? I guess it all depends too if I have any problems initially with anything but I'm just looking for anyone with experience to point me in the right direction. Thanks!
You can run sp_help_revlogin on the old server to generate a script for creating all logins on the new server. I also have an upcoming migration and I plan on using this and performing backup/restore operations to get the databases over to the new server.
Overall the downtime required depends on how long it will take to move the databases over to the new server then to configure any applications to point to the new server name.
good tips... so do you think if I'm able to do the backup/restore to new server initially and get the logins scripted/set properly etc... then I should be able to run backup/restore again in few days without too much trouble?
You might want to test it twice to make sure you have the steps working correctly. For example, the first time you move the databases over, they wont exist on the new server. But the second time you try, they will exist on the new server so you might have to do something different to overwrite them. That is the only condition I can think of but other than that it's should go pretty easily.
Make the new install exactly the same version and patch level as the current version. [You can add additional SPs/patches later if you need them.]
Restore the master and msdb dbs. If you have custom things in the model db, restore it also, or script out the items in model from the old db to add them to the new.
You can restore user dbs in steps: first restore the full backup, ahead of time; then, at point-in-time of cutover, just create a differential backup and restore it. Typically diff backups are extremely fast to create and apply. Be sure to use compression for all backups if available.
To test that:
1) Take a full backup of a sample user db(s).
2) Restore it(them) to the new server WITH NO_RECOVERY.
3) Allow 4 hours of so of new processing on that(those) db(s), take a differential backup.
4) Apply the differential backup to the new server WITH NO_RECOVERY.
5) If you want, also apply specific tran log backup(s).
6) When that's ready, do a final RESTORE [db_name] WITH RECOVERY; to make the db useable. Check it to make sure it has all current data.
At cut-over time, be sure to stop all other backups that could run so you don't get another backup coming in, which would force you to use that recover from rather than the one you already have pre-restored and ready to recover.
Restore the master and msdb dbs. If you have custom things in the model db, restore it also, or script out the items in model from the old db to add them to the new.
You can restore user dbs in steps: first restore the full backup, ahead of time; then, at point-in-time of cutover, just create a differential backup and restore it. Typically diff backups are extremely fast to create and apply. Be sure to use compression for all backups if available.
To test that:
1) Take a full backup of a sample user db(s).
2) Restore it(them) to the new server WITH NO_RECOVERY.
3) Allow 4 hours of so of new processing on that(those) db(s), take a differential backup.
4) Apply the differential backup to the new server WITH NO_RECOVERY.
5) If you want, also apply specific tran log backup(s).
6) When that's ready, do a final RESTORE [db_name] WITH RECOVERY; to make the db useable. Check it to make sure it has all current data.
At cut-over time, be sure to stop all other backups that could run so you don't get another backup coming in, which would force you to use that recover from rather than the one you already have pre-restored and ready to recover.
Premium Content
You need an Expert Office subscription to comment.Start Free Trial