Attach/detach vs. backup/restore

Question

I need to transfer the database (as a whole) to another server, to make a duplicate database to setup another test environment.

I have two choices:

1. Make a full backup at source server/restore at destination server;
2. Detach at source server/attach at destination server.

What are the pros and cons of the two solutions according to my requirements?

I am using SQL Server 2008 Enterprise.

Answer 1

Backup/restore should normally be your method of choice. It will be quicker in most situations.

You can use it consistently, also for production to test too.

See this related question as well, where the backup/restore vs detach/attach is mentioned:

SQL Server Migration restore backup vs copy data and log files

Make sure you add the WITH COPY_ONLY option to the backup so it does not break the existing maintenance plan backup chain.

Answer 2

1. Detaching the database will take it offline. Make a backup if you need the database to remain online while you copy it to another server.
2. Moving and restoring a backup file (.bak) may be simpler/easier than moving and attaching multiple mdf/ldf files (as you would if you detached the database).
3. On paper, a database detach/attach may be faster technically, but in practice, a backup/restore is likely to be quicker and easier. When you detach a database you first have to take the original database offline (disconnect everyone and everything), and then the database is unavailable until you reattach. You also have to keep track of all of the files, whereas with a backup all of the files are grouped.

If you decide to backup/restore, use the WITH COPY_ONLY option during the backup to ensure that any existing maintenance plan's backup chain isn't broken.

A .bak file compresses well, so if you decide to go with making a backup, compressing the backup before moving it might save some transfer time.

Answer 3

I would go for backup/restore as it leaves the original database in an operational state.

Especially if you're doing a 'production to test' conversion, it's important that the production database stays online.

Backup/restore is also a safer option: What happens if the file becomes corrupt somewhere between the start of the detach, the copy, the attach, etc.? At least if you perform a backup and the file becomes corrupt, you can start over. If that happens with a detach, your database is gone.

Also, to me (although its more a feeling than anything else), backup/restore is "everyday work" whereas detach/attach is something you do in exceptional circumstances. Don't ask me where I got this idea though ;-)

Answer 4

I've always had issues with the "restore" part of backup/restore. I can't cite specifics as I eventually gave up on it and have been detatching/copying/attaching ever since.

The only thing about detach is that you HAVE HAVE HAVE to make sure you make sure the DBMS isn't also going to delete the database as well. Have had this happen, and its not a pretty sight.

Answer 5

I recommend a copy_only backup using this method from a DOS shell (so that you don't interrupt transaction logs):

Run from C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\Backup directory:

backup.bat SQLDBNAME

Where backup.bat contains (line break added for readability):

sqlcmd.exe -U username -P xxxxxxx -S SQL-SERVERNAME 
    -Q "BACKUP DATABASE %1 TO DISK = '%1_COPYONLY.BAK' WITH COPY_ONLY,INIT;"