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.


5 Answers 5


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.

  • SQL 2008 Enterprise introduced backup compression; chances are that the compressed backup will be significantly smaller than 100GB, and thus quicker to write out / copy / load than copying over the MDF/LDFs. Aug 9, 2013 at 15:27
  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.


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 ;-)


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.

  • 5
    The DBMS will not delete the database on detach. What kind of shop are you in if detach deletes files and restore has issues?
    – gbn
    Sep 25, 2009 at 18:15
  • @Will: sp_detach_db is not DROP: 2 separate and unrelated commands that have to be issued separately. A detached databases can not be DROPped or files deleted via SQL. A dropped database can not be detached. Detach does not have the "delete files" option via code or via SSMS. So, I can justify my first comment because you have to deliberately choose the option to delete the files on DROP. Not detach
    – gbn
    Sep 27, 2009 at 17:33

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 

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.