Restoring From Backups Stored in Microsoft Azure
Applies to: SQL Server (all supported versions)
This topic outlines the considerations when restoring a database using a backup stored in the Azure Blob storage service. This applies to backups created either by using SQL Server Backup to URL backup or by SQL Server Managed Backup to Microsoft Azure.
We recommend reviewing this topic if you have backups stored in the Azure Blob storage service that you plan to restore, and then review the topics that describe the steps on how to restore a database which is the same for both on-premises and azure backups.
The tools and methods that are used to restore a database from an on-premises backup apply to restoring a database from a cloud backup. The following sections describe these considerations and any differences you should know about when you use backups stored in the Azure Blob storage service.
Since SQL Server must connect to an external source to retrieve the backup files, SQL Credential is used to authenticate to the storage account. Consequently, the RESTORE statement requires WITH CREDENTIAL option. For more information, see SQL Server Backup and Restore with Microsoft Azure Blob Storage Service.
If you are using the SQL Server Managed Backup to Microsoft Azure to manage your backups to the cloud, you can review all the available backups in the storage, by using the smart_admin.fn_available_backups system function. This system function returns all the available backups for a database in a table. As the results are returned in a table, you can filter or sort the results. For more information, see managed_backup.fn_available_backups (Transact-SQL).
Using SQL Server Management Studio
- The restore task is used to restore a database using the SQL Server Management Studio. The backup media page now includes the URL option to show backup files stored in the Azure Blob storage service. You also must provide the SQL Credential that is used to authenticate to the storage account. The Backup sets to restore grid is then populated with the available backups in the Azure Blob storage. For more information, see Restoring from Azure storage Using SQL Server Management Studio.
To reduce restore write time, Add perform volume maintenance tasks user right to the SQL Server user account. For more information, see Database File Initialization. If restore is still slow with instant file initialization turned on, look at the size of the log file on the instance where the database was backed up. If the log is very large in size (multiple GBs), it would be expected that restore would be slow. During restore the log file must be zeroed which takes a significant amount of time.
To reduce restore times it is recommended that you use compressed backups. For backup sizes exceeding 25 GB, use AzCopy utility to download to the local drive and then perform the restore. For other backup best practices and recommendations, see SQL Server Backup to URL Best Practices and Troubleshooting.
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-<instancename>-<dbname>-action-<PID>.log. The log file includes information about each round trip to Azure Storage including timing that can be helpful in diagnosing the issue.