December 3, 2013 at 10:42 am
I'm planning a migration of a third party app's databases to another server. The vendor is recommending the following steps:
I would recommend performing a test first by detaching the current databases, making copies of the database files (before reattaching them to the original server), moving the copies to the new server, attaching them, and then pointing a test client to the new server in order to test functionality. If this is successful, we can move forward with a production migration that essentially follows the same steps.
When I've done migrations before, I've backed up the databases, then restored the backups on the new server. An obvious advantage to this is that you don't need to take the application offline when you do your test migration. Is there any advantage or other reason to use the detach/copy/attach method instead?
December 3, 2013 at 10:52 am
If it's a huge database then the advantage may be that you save time as you just need to copy the data and log files rather than backing up, copying the backup and then restoring. But it would have to be pretty big not to just do a backup...
December 3, 2013 at 11:06 am
If you have backup compression as an option, that may prove quicker to move to the new server and restore than copying the data files. Either way, backup / restore means you don't have to take the live application offline 🙂
[font="Courier New"]sqlmunkee[/font]
[font="Courier New"]Bringing joy and happiness via SQL Server since 1998[/font]
December 4, 2013 at 6:40 am
I prefer backup and restore where I can do it (primary limitation would be disk space) because you don't ever risk the fundamental data files. Detach and attach, you take a risk that during one of the three operations, detaching, copying, attaching, something could go south and that's your database. In short, you'd need a backup in place as a paranoia point anyway.
Plus, with backups, you can combine a log shipping process with backups to arrive at a place where you have very minimal down time. Just the time to disconnect the users, do a final backup of the log, restore it, and bring the users back online.
Safe, fast, and flexible with backups, not so much with detach/attach.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply