Reviewed by: Rajesh Setlem, Xiaochen Wu
Historically, SQL Server supported two backup destinations, disk and tape. In SQL Server 2014, a new destination named URL was added. With this destination, SQL Server backs up a database to an Azure Blob Storage container and blob specified in the URL. In SQL Server 2016, Backup to URL was significantly improved, and added support for block blobs in addition to page blobs supported earlier. As customers migrate their SQL Server workloads to Azure IaaS VMs or to Managed Instance, Backup to URL is becoming a very common (and in the case of Managed Instance, the only) way to back up databases.
At the same time, customers are sometimes confused by the differences between the two types of Backup to URL that are available (page blobs and block blobs). The table below describes these differences as a quick summary reference, to help customers choose the right type of Backup to URL for their scenario.
Page blobs (old)
Block blobs (new and preferred)
SQL Server 2012 SP1 CU2 or later
SQL Server 2016 or later
Single blob, up to 1 TB
Up to 64 blobs (stripes), up to ~195 GB each
Target throughput: 60 MB/s
Target throughput: (60 MB/s * number of blobs)
Maximum backup size: 1 TB
Maximum backup size: ~12 TB (with 64 stripes)
Credential holds storage account key
Credential holds SAS token
Credential name is arbitrary
Credential name is the container URL
Syntax: BACKUP/RESTORE … TO/FROM URL = ‘…’ WITH CREDENTIAL = ‘…’;
Syntax: BACKUP/RESTORE … TO/FROM URL = ‘…’;
Further details about Backup to URL are available in documentation.
Based on our work with customers using Backup to URL, we can also mention several recommendations:
Use a well-formed SAS token
The most common issue is including the leading ‘?’ in the token string. This must be removed. Other common problems are:
· expired or not yet valid token
· insufficient permissions (Read/Write/Delete/List permissions are required for both backup and restore)
· an overly restrictive IP address filter
Use Standard Storage
Premium Storage is not supported in Backup to URL.
Use backup compression
If using block blobs, stripe the backup over multiple blobs
This improves backup/restore throughput, and is required to back up larger databases.
If using block blobs, use MAXTRANSFERSIZE = 4194304 and BLOCKSIZE = 65536
This is required for larger databases to avoid the ~195 GB per blob limit, and achieves better backup compression ratio.
Use a container-level SAS token vs. account-level SAS token
Do this to follow the principle of least privilege. Azure Storage Explorer can be used to create container-level SAS tokens.
Have a question about Backup to URL not covered in this article or in documentation? Please post a comment.