SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Migration: detach & copy vrs backup & restore


Migration: detach & copy vrs backup & restore

Author
Message
dan-572483
dan-572483
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3743 Visits: 2000
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?
DNA_DBA
DNA_DBA
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3817 Visits: 2826
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...
sqlmunkee
sqlmunkee
Right there with Babe
Right there with Babe (783 reputation)Right there with Babe (783 reputation)Right there with Babe (783 reputation)Right there with Babe (783 reputation)Right there with Babe (783 reputation)Right there with Babe (783 reputation)Right there with Babe (783 reputation)Right there with Babe (783 reputation)

Group: General Forum Members
Points: 783 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 :-)

sqlmunkee
Bringing joy and happiness via SQL Server since 1998
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)

Group: General Forum Members
Points: 101089 Visits: 33014
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 and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search