4

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 set up 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

6 Answers 6

11

I shudder at the thought of detaching a database, copying its files to a different location, and then reattaching it on the production server (and attaching the copy on the test server). Backup is designed to provide a complete copy of the database without any downtime. It also doesn't introduce opportunity for the source database's files to be accidentally moved/deleted/damaged, which I've seen happen several times (due to user error -- typo or mouse slip) during detach/attach operations. Also, transferring the database+logs could potentially result in a much larger transfer file size than just a backup.

Just make a backup, and then copy/move the backup file to the destination server. It's much safer to do that, from the production standpoint.

3
  • Thanks Steman, two confusions in my experience, 1. I find the speed of detach/attach is much faster than backup/restore. I have a 100G database, and it takes me only 5-10 seconds to attach/detach, but 20-30 mins to make full backup/restore. I think faster is pros of attach/detach, any comments? 2. I am confused that backup does not copy any log file? If not copy any log file, how could we ensure database consistent (suppose when we make backup, a transaction is in the middle). Any ideas?
    – George2
    Sep 26, 2009 at 17:04
  • I can't argue that detach/attach is much faster than backup/restore... but they're designed for completely separate purposes. Detachment is designed primarily to handle situations where databases are being moved/removed. Backup/restore is the easiest method for copying databases between servers. Also, does your 5-10 second estimate include the time it takes to copy the files to the appropriate backup server? Full backups (with or without COPY, I believe) include only committed transactions. If there is a tran running during backup, it can't include it... how would it know what to include?
    – aNullValue
    Sep 28, 2009 at 1:47
  • If your question about the backups including log files is a result of my comment about backups being a much smaller file size, that's because of the way SQL Server's log files grow and allocate space... too involved for this comment, search on technet. Using a full backup file will not exclude any data that has been committed as of the moment that the backup transaction began.
    – aNullValue
    Sep 28, 2009 at 1:52
3

Hopefully a DBA can chime in here as I am not a DB guy. But I do know that I have ran into schema related issues doing detatch/attach rather then backup/restore. I always think the safer route is to backup and restore.

2
  • "schema related issues" -- could you share more details please? Thanks!
    – George2
    Sep 26, 2009 at 17:05
  • I've see problems because with de/at because the owner should always be sa, but then ends up being the user that attached the database, because they didn't specify sa as the owner Sep 28, 2009 at 13:31
2

Detach/attach is potentially faster, depending on your recovery model and whether or not you're using compression in your backup. The detach and attach are nearly instant whereas it takes a while to write the backup file on the source server and then to write the db files on the destination server.

1
  • 1
    How to set compression option in SQL Server full backup?
    – George2
    Sep 26, 2009 at 17:05
1

Backup and restore will lower the downtime on your source server while detach and attach will be faster overall since you won't need first to copy the backup to the target system and restore it (unless you're using a physical media to transfer the backup file from one server to the other).

I'd still go with backup/restore: it's simple to use and understand and uptime is always a desirable property on a production system. Additionally, you can use that to validate your backup strategy by pulling a regular backup for the restore instead or creating a new one.

Once the DB has been mounted, you will still need to go through permissions and schema to adjust them to the new location. In both case, you might need to transfert any and all keys used for encryption before attempting to restore the data.

0

I always use Backkup/Restore - it's less invasive, it keep your DB online and it's fairly idiot proof - I am living testament to that.

I assume you are running backups daily/weekly/whatever... what could be easier than simply copying the backups to your test server? [Obviously, at 100GB, it will take a while!]

0

I don't know exactly what you are trying to accomplish here but you also have the ability to do a snapshot replication in Enterprise that would allow you to update your development environment or staging environment on a regular basis.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .