Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Migration: detach & copy vrs backup & restore Expand / Collapse
Posted Tuesday, December 3, 2013 10:42 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Thursday, October 20, 2016 11:40 AM
Points: 626, Visits: 1,948
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?
Post #1519324
Posted Tuesday, December 3, 2013 10:52 AM



Group: General Forum Members
Last Login: Yesterday @ 4:48 AM
Points: 2,960, Visits: 2,716
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...
Post #1519327
Posted Tuesday, December 3, 2013 11:06 AM



Group: General Forum Members
Last Login: Tuesday, May 3, 2016 3:29 AM
Points: 455, Visits: 286
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

Bringing joy and happiness via SQL Server since 1998
Post #1519331
Posted Wednesday, December 4, 2013 6:40 AM



Group: General Forum Members
Last Login: Yesterday @ 12:17 PM
Points: 17,173, Visits: 32,139
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
The Scary DBA
Author of:
SQL Server Query Performance Tuning
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1519602
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse