SQL Server backup/restore v.s. detach/attach

Question

Hello everyone,

I have one database which contains the most recent data, and I want to replicate the database content into some other servers. Dues to non-technical reasons, I can not directly use replicate function or sync function to sync to other SQL Server instances.

Now, I have two solutions, and I want to learn the pros and cons for each solution. Thanks!

Solution 1: detach the source databse which contains the most recent data, then copy to the destination servers which need the most recent data, and attach database at the destination servers;

Solution 2: make a full backup of source server for the whole database, then copy data to destination servers and take a full recovery at the destination server side.

thanks in advance, George

Answer 1

The Detach / Attach is often quicker than performing a backup as it doesn't have to create a new file. Therefore therefore the time from server a to server b is almost purely the file copy time.

The Backup / Restore option allows you to perform a full backup, restore than, then perform a differential backup which means your down time can be reduced between the two.

If its data replication you're after, does that mean you want the database functional in both locations? In that case, you probably want the backup / restore option as that will leave the current database fully functional.

EDIT: Just to clarify a few points. By downtime, I mean that if you're migrating a database from one serer to another. You generally will be stopping people using it whilst it's in transit. Therefore, from the "stop" point on Server A up to the "start" point on Server B this could be considered downtime. Otherwise, any actions performed on the database on server A during transit will not be replicated onto server B.

In regard to the "create a new file". If you detach a database you can copy the MDF file immediately. It's already there ready to be copied. However, if you perform a backup, you have to wait for the .BAK file to be created and then move it to it's new location for a restore. Again this all comes down to is this a snapshot copy or a migration.

Answer 2

Solution 2 would be my choice... Primarily becuase it won't create any downtime on the source database. The only disadvatage i can see is that depending on the database recovery model, the transaction log will be truncated meaning if you wanted to restore any data from the transaction log you'd be stuffed, you'd have to use your backup file.

EDIT: Found a nice link; http://sql-server-performance.com/Community/forums/p/5838/35573.aspx