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

24 March,2016 by Jack Vamvas

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

 


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on SQL Server Backup – Restore vs Detach – Attach : which method is faster?


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer