SQL Server backup/restore vs. detach/attach

Question

I have one database which contains the most recent data, and I want to replicate the database content into some other servers. Due 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 database 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 option is often quicker than performing a backup as it doesn't have to create a new file. 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 that, then perform a differential backup which means your down time can be reduced between the two.

If it's 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 server 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 regards 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

Backing up and restoring makes much more sense, even if you might eek out a few extra minutes from a detach attach option instead. You have to take the original database offline (disconnect everyone) prior to a detach, and then the db 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. And with the most recent versions of SQL Server the backups are compressed.

And just to correct something: DB backups and differential backups do not truncate the log, and do not break the log chain.

In addition, the COPY_ONLY functionality only matters for the differential base, not for the LOG. All log backups can be applied in sequence from any backup assuming there was no break in the log chain. There is a slight difference with the archive point, but I can't see where that matters.