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.