We are in an environment where we need to restore the production db the size of around 600GB from daily backup to our UAT server.

We have currently do full backup and restore and as database grows it has slowed down the overall performance. Im not a DBA and wonder whats the best strategy to optimise this process.

Occasionally we also have experience very slow restore even after I enabled the instant file initialisation. Its processing in a normal speed but all of sudden from like 50 percent it processes extremely slow. Any idea where to begin the investigation?

The backup is compressed. The database only has about 43 VLFs. We just want to get production data in UAT. I have heard from someone it's better to use differential back up for our scenario but one thing I am not still clear is why it randomly slows down in the middle of restore.

  • If you're open to other suggestions: Is this on a SAN? Does it support VSS snapshots? If so, take a snapshot of the LUNs and clone those over to be attached to your UAT environment. If you aren't, then to maximize the restore throughput you'll need to stripe across multiple files in the backup and preferably have multiple filegroups. – Sean says Remove Sara Chipps Sep 11 '16 at 2:22
  • Are you running on a virtual machine? Are you running with High Performance mode in Windows Power Settings? What type of disks are you restoring to? – Randolph West Sep 11 '16 at 3:57
  • 1
    What version and edition of sql server you are using ? – Kin Shah Sep 12 '16 at 2:18
  • Differential backups may help improve your backups but restores will still take as long if not longer, because you still need to restore a full backup and then apply the differentials to get up to date. – Sir Swears-a-lot Sep 17 '16 at 8:35
  • It may be worth reviewing what data you need in UAT. Does it really need to be the whole db? Or could you just copy a few important tables? – Sir Swears-a-lot Sep 17 '16 at 8:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.