1

We're being encouraged to move our database backups to Azure blobs, which we access through the Microsoft Azure Storage Explorer. We're running SQL Server 2017.

Our backup times are fine-- 20 minutes for a ~15GB database that is compressed, encrypted, and checksum'd.

However, we're getting killed on restore times. That 15GB database takes over 2 hours to restore!

I can download the full backup file from Azure to my local PC in about a minute or two, so it seems like network performance is fine.

It only takes about 12 minutes to restore the backup locally (same file, but on another VM rather than in Azure).

Any ideas or recommendations about what could cause restoring a database from Azure (via URL) to take hours and hours, and what we can do about it? Glad to provide more information if it'll help. I appreciate all of your help!

Improve this question
0

1 Answer 1

Reset to default
3

You can also turn on Trace Flag 3051 when doing the restore to generate a detailed log. This log file is placed in the log directory, and is named using the format: BackupToUrl---action-.log. The log file includes information about each round trip to Azure Storage including timing that can be helpful in diagnosing the issue.

https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/restoring-from-backups-stored-in-microsoft-azure?view=sql-server-ver15#optimizing-restores

And you can also use AzCopy to download the backup to a local disk first.

Or overwrite a daily backup on a local disk in addition to the Azure backups.

Improve this answer
2
  • +1 for AzCopy which has had some huge improvements in the past year to make it a great choice for downloading backups really fast.
    – LowlyDBA - John M
    May 14, 2020 at 14:43
  • 1
    Thank you very much! I anticipate that this will get us where we need. I really appreciate your help!
    – Eluros
    May 16, 2020 at 2:10

Your Answer

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

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