Take the 2-minute tour ×
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. It's 100% free, no registration required.

I am using SQL Server 2008 Enterprise. 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) making a full backup at source server/restore at destination server; (2) making detach at source server/attach at destination server.

Any pros and cons compared of the two solutions according to my requirements?

thanks in advance, George

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.

6 Answers 6

Backup/restore should normally be your method of choice.

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

See this SO question too, where the backup/restore vs detach/attach is mentioned

share|improve this answer
6  
Make sure you add the WITH COPY_ONLY option so it does not break the existing maintenance plan backup chain. –  Remus Rusanu Sep 25 '09 at 21:55
1  
+1 to Remus - that is so important. –  Andrew Sep 26 '09 at 0:43
    
Thanks gbn! If downtime is not an issue, I think using attach/detach is better because it is faster than backup/restore? –  George2 Sep 26 '09 at 9:28
2  
Backup/restore will be quicker in most situations. –  gbn Sep 27 '09 at 13:45
1  
backup-copy-restore vs detach-copy-attachx2? You took one minute to copy a 100 GB MDF from one server to another and the attach it...? –  gbn Sep 27 '09 at 17:23

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.

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
    
Thanks Brimstedt! If downtime is not an issue, I think using attach/detach is better because it is faster than backup/restore? –  George2 Sep 26 '09 at 9:29
1  
No @George2, I disagree. What happens if the file becomes corrupt somewhere between the start of the detach, the copy, the attach, etc.? What is your goal, to keep the database intact, or to finish the move as quickly and risky as possible? 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. –  Aaron Bertrand Jan 21 '12 at 17:06
    
When attaching/detaching, it's never a good idea to MOVE the files. You should always be COPYING them. I never trust Windows to move important files. –  SomeGuy May 7 '13 at 15:58
  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. Backup/restore will be faster than detach/attach

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
1  
A full backup will not include the log, but only the inflight transactional records that occured during the backup so that it can bring the database into a transactionally consistent state. It doens't take the whole log with it, there is no need. If it took the log and make VLF's inactive so that they cleared, your transactional log backup chain would be broken and you would be at significant risk. Only think that changes the log in that respect, is the log backup. –  Andrew Sep 25 '09 at 22:18
1  
Backups do not contain loads of unused space. SQL only writes data from used pages into the backups so empty space in the data file does not make its way into the backup. They do compress very well, but that's not because they're mostly empty –  GilaMonster Sep 26 '09 at 8:11
    
Thanks CodeToaster, for backup file, how to "loads and loads of unused space"? Any special commands to use to free space while making backup? –  George2 Sep 26 '09 at 9:34
    
"They do compress very well, but that's not because they're mostly empty" -- empty space is not unused space? –  George2 Sep 26 '09 at 9:42
    
@Gilamonster & @Andrew - thanks for the feedback; I've updated my answer. –  Bob Black Sep 28 '09 at 13:47

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 "copyonly" backup using this method from a DOS shell (so that you dont interrupt transaction logs):

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

backup.bat SQLDBNAME

where

backup.bat =

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

So it looks like the main benefit to the backup/restore route is that it leaves the original instance of the database up and running, resulting in no actual downtime. However, wouldn't you still have to have a switchover date for the users? It seems like you would lose all the work they've done between when you did the backup/restore and when you cut them over. And if you wait until the switchover date to do the backup/restore, it seems like you would be able to accomplish it a lot quicker with detach/reattach.

share|improve this answer

Your Answer

 
discard

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