Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
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 '13 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 '13 at 16:16
    
which version of sql server? –  swasheck Dec 4 '13 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? –  Ali Razeghi Dec 4 '13 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 '13 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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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