Wednesday, May 2, 2012

Moving databases: Detach/Attach method vs. Backup/Restore

Personally, when moving a database from one server to another, I prefer using backup/restore since if I encounter any issues with SQL Server versions or something else, I can always restore backup somewhere else and I will also have a working copy of the database on the source server, while if anything goes wrong during the detach/attach process, I will not have a copy of the database elsewhere. My second reason is that backup/restore requires no downtime, which is always a big plus.

So far I have encountered only one case where using detach/attach scenario was better then backup/restore - I needed to move a database from the default instance to a named instance on the same server and I did not want to use backup/restore since I wanted mdf and ldf files to stay at the same location on the server. Also detach/attach is much faster than backup/restore since it only takes minutes to detach database, copy the files to another server and attach the database, and both backup and restore processes can take hours.

No comments:

Post a Comment