SQL Server Backup – Restore vs Detach – Attach : which method is faster?

A colleague phoned me yesterday , with a problem related to moving and restoring a very large database. They a one-off situation where they need to copy a database to another server.

The question was whether a Backup and Restore would be faster or a detach and attach.They were wondering which method would be faster . 

There are many factors in-play , such as : network speed, disk io throughput , memory – in deciding which method to use.

So the first task is to assess the capacity of the system resources

The second task is to review any other methods available, such as snapshots

The third task is to decide whether it’s acceptable to have the database offline for a period of time , if employing the Detach – Attach method.

Assuming you’re left with the Backup – Restore or the detach – attach method , then generally the detach – attach method can be quicker to use . Why?

1. You don’t need to create the .BAK file. Therefore you can start the copy over the network earlier.

This needs to be weighed against

1. In a backup only used pages are backed up
2. In a detach – attach method , the whole MDF needs to be copied
3. In the detach – attach method , you need to keep track of the files

Read More on SQL Server Backups and Restore

Database backup does not cause blocking (SQL Server DBA)

Backup history for a single SQL Server database - SQL Server DBA

VM Snapshot Backup Review for databases - SQL Server DBA