SQL Server backup/restore vs. detach/attach

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.

Leave a Comment