question

HugoFernandoMirandaMorin-0312 avatar image
0 Votes"
HugoFernandoMirandaMorin-0312 asked Cathyji-msft commented

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

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.

sql-server-generalwindows-server
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I copied a 650 GB database by means of backup / restore on Azure VM a few weeks ago. That took five hours! I don't know the spec of that machine.

Keep in mind that in an Azure VM, it pays off to have bigger disks - they are faster.

One thing that can matter is if the service account for SQL Server have been granted the permission Perform Volume Maintenance Operations. Without this permission, SQL Server must zero out the space for the database file. In my case, I believe this was the most time-consuming part of the operation.

0 Votes 0 ·
HugoFernandoMirandaMorin-0312 avatar image
0 Votes"
HugoFernandoMirandaMorin-0312 answered Cathyji-msft commented

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

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @HugoFernandoMirandaMorin-0312,

Any update for this thread? Did the reply could help you? If the response helped, do "Accept Answer". If it is not, please let us know. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.

0 Votes 0 ·
Cathyji-msft avatar image
0 Votes"
Cathyji-msft answered Cathyji-msft commented

Hi @HugoFernandoMirandaMorin-0312,

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.



· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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

0 Votes 0 ·
Cathyji-msft avatar image Cathyji-msft HugoFernandoMirandaMorin-0312 ·

Hi @HugoFernandoMirandaMorin-0312,

Thanks for your posting, waiting for your good news.

0 Votes 0 ·