SQL Server Backup to URL – a cheat sheet
Published Feb 15 2019 02:15 PM 18.6K Views
Microsoft

Reviewed by: Rajesh Setlem, Xiaochen Wu

 

Historically, SQL Server supported two backup destinations, disk and tape. In SQL Server 2012, 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:

Recommendation

Explanation

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

Compression improves backup/restore throughput and reduces backup size. Make sure to apply patches if using TDE with 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.

6 Comments
Version history
Last update:
‎Oct 28 2022 01:06 PM
Updated by: