As part of our regular development cycle we need to take a copy of production into development databases.

To do this, we take a copy of production backups and move them to the Development Server. Then we use the RESTORE WITH MOVE function to define the where the data and logs should be stored.

This restore is very time consuming, and generally takes 2 to 3 times as long as a standard restore. This is an expensive overhead for us, as it often has to be done several times during the day.

I was wondering whether there is a more efficient way to perform this task.

I have tried writing the backup to several files to improve performance, and whilst this speeds up the backup, the RESTORE WITH MOVE seems unaffected.

Any help is greatly appreciated.

  • Prod and dev database are named FOO. Restore prod to dev as FOO_RESTORE. Once restore is complete, kick everyone out of FOO and swap FOO_RESTORE into its place.
    – billinkc
    Dec 4, 2013 at 16:01
  • I am assuming that you are restoring over the network and the network bandwidth would be your issue. Please can I clarify whether the BAK file is on the Production server or Development server? I would suggest moving the file to the Development server and then restoring it locally.
    – user31270
    Dec 4, 2013 at 16:16
  • which version of sql server?
    – swasheck
    Dec 4, 2013 at 17:18
  • Please check everything Kin mentioned, what is the disk configuration of dev? Probably much slower than prod, is Dev in a VM that is on shared disks? Overwriting the old database is faster than dropping the old DB and starting from scratch, are you explicitly dropping first? Dec 4, 2013 at 17:57
  • I have used all restore optimisation that fits my situation. My problem is that if I take a backup of the development area and restore it, it runs much faster than restoring the production backup, even though the db size is identical (and restoring from the same directory – so no hardware discrepancy). I'm trying to find a way to get the restore of production to run as quickly as the restore of development. For some reason, RESTORE WITH MOVE seems to be far slower than a standard RESTORE.
    – Braska
    Dec 5, 2013 at 9:26

1 Answer 1


The restore speed will depend on the hardware you use for DEV environment.

I would suggest to turn on Instant File Initialization. When backups are restored the space has to be pre-allocated before a restore can occur and IFI is a boon for such scenarios. Instant file initialization only affects MDF and NDF files, not LDF files. In other words, transaction log files can’t take advantage of instant file initialization.

Also, you can play with BufferCount and MaxTransferSize. Read up Optimizing Database Restores.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.