Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question

migrated from stackoverflow.com Jan 21 '12 at 16:48

This question came from our site for professional and enthusiast programmers.

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.

share|improve this answer
    
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. – Thomas Rushton Aug 9 '13 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.

share|improve this answer

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

share|improve this answer

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.

share|improve this answer
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 '09 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 '09 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 
    -Q "BACKUP DATABASE %1 TO DISK = '%1_COPYONLY.BAK' WITH COPY_ONLY,INIT;"
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.