Microsoft SQL Server Standard takes 30 minutes to restore an 8 GB database

Question

Hello, I have a virtual machine in Azure with Windows Server 2012 R2, 8 vcpu, 32GB of RAM, 128GB disk in S.O. and 512 GB disk for Data both Premium SSD. I am doing the task of applying a backup to the base that is 8GB in size and then I perform a restore, the time it takes is 37 minutes, is this normal or can I improve the restore task?

Any idea?

Thanks.

Answer 1

Hi @Hugo Fernando Miranda Morin ,

> is this normal or can I improve the restore task?

No, it is not normal. Backup and restore operations are I/O intensive. Backup/Restore throughput depends on how well the underlying I/O subsystem is optimized to handle the I/O volume.

Here’s a list of things you can do to make restoring a full backup go faster:

• Ensure that instant file initialization is enabled on the SQL Server instance performing the restore operation, to avoid spending time zero-initializing any data files that must be created. This can save hours of downtime for very large data files.
• Consider backup compression, which can speed up both backup and restore operations, and save disk space and storage costs.
• Consider using multiple backup files, each on a separate volume. SQL Server will recognize this situation and use parallel write threads (one per volume) to write to the files during the backup, and to read from them during the restore – speeding things up. If you have multiple database data files, a similar I/O parallelism will occur – providing even more of a speed boost.
• Try to avoid having long-running transactions that will take time to roll back.
• Manage your transaction log to avoid having an excessive number of virtual log files, so if there are transactions to roll back, the roll back will go as fast as possible.

Refer to the blog SQLskills SQL101: Why is restore slower than backup to get more information.

---

If the response is helpful, please click "Accept Answer" and upvote it, thank you.

Answer 2

Hello, thanks for the tips, I will start to apply them, I will tell you the result.