Windows Azure Blob storage service enables you to backup SQL Server databases to a URL. The backup and restore functionality are same or similar to when using DISK or TAPE, with a few differences that will be presented in this article.
Security
When creating a container for the Windows Azure Blob storage service, it is recommended you set the access to private. Setting the access to private restricts the access to users or accounts able to provide the necessary information to authenticate to the Windows Azure account.
The user account that is used to issue BACKUP or RESTORE commands should be in the db_backup operator database role with Alter any credential permissions.
Introduction to Key Components and Concepts
The following two sections introduce the Windows Azure Blob storage service, and the SQL Server components used when backing up to or restoring from the Windows Azure Blob storage service. It is important to understand the components and the interaction between them to do a backup to or restore from the Windows Azure Blob storage service.
Creating a Windows Azure account is the first step to this process. SQL Server uses the Windows Azure storage account name and its access key values to authenticate and write and read blobs to the storage service. The SQL Server Credential stores this authentication information and is used during the backup or restore operations.
Windows Azure Blob Storage Service
Storage Account: The storage account is the starting point for all storage services. To access the Windows Azure Blob Storage service, first create a Windows Azure storage account. The storage account name and its access key properties are required to authenticate to the Windows Azure Blob Storage service and its components.
Container: A container provides a grouping of a set of Blobs, and can store an unlimited number of Blobs. To write a SQL Server backup to the Windows Azure Blob service, you must have at least the root container created.
Blob: A file of any type and size. There are two types of blobs that can be stored in the Windows Azure Blob storage service: block and page blobs. SQL Server backup uses page Blobs as the Blob type. Blobs are addressable using the following URL format: https://
SQL Server Components
URL: A URL specifies a Uniform Resource Identifier (URI) to a unique backup file. The URL is used to provide the location and name of the SQL Server backup file. In this implementation, the only valid URL is one that points to a page Blob in a Windows Azure storage account. The URL must point to an actual Blob, not just a container. If the Blob does not exist, it is created. If an existing Blob is specified, BACKUP fails, unless the “WITH FORMAT” option is specified.
Here is a sample URL value: http[s]://ACCOUNTNAME.Blob.core.windows.net/
Credential: A SQL Server credential is an object that is used to store authentication information required to connect to a resource outside of SQL Server. Here, SQL Server backup and restore processes use credential to authenticate to the Windows Azure Blob storage service. The Credential stores the name of the storage account and the storage account access key values. Once the credential is created, it must be specified in the WITH CREDENTIAL option when issuing the BACKUP/RESTORE statements.
Limitations
- The maximum backup size supported is 1 TB.
- You can issue backup or restore statements by using TSQL, SMO, or PowerShell cmdlets. A backup to or restoring from the Windows Azure Blob storage service by using SQL Server Management Studio Backup or Restore wizard is not currently enabled.
- Creating a logical device name is not supported. So adding URL as a backup device using sp_dumpdevice or through SQL Server Management Studio is not supported.
- Appending to existing backup blobs is not supported. Backups to an existing Blob can only be overwritten by using the WITH FORMAT option.
- Backup to multiple blobs in a single backup operation is not supported.
- Specifying a block size with BACKUP is not supported.
- Specifying MAXTRANSFERSIZE is not supported.
- Specifying backupset options – RETAINDAYS and EXPIREDATE are not supported.
- SQL Server has a maximum limit of 259 characters for a backup device name. The BACKUP TO URL consumes 36 characters for the required elements used to specify the URL – ‘https://.blob.core.windows.net//.bak’, leaving 223 characters for account, container, and blob names put together.
| Backup/Restore Statement | Supported | Exceptions | Comments |
|---|---|---|---|
| BACKUP | Yes | BLOCKSIZE, and MAXTRANSFERSIZE are not supported. | Requires WITH CREDENTIAL specified |
| RESTORE | Yes | Requires WITH CREDENTIAL specified | |
| RESTORE FILELISTONLY | Yes | Requires WITH CREDENTIAL specified | |
| RESTORE HEADERONLY | Yes | Requires WITH CREDENTIAL specified | |
| RESTORE LABELONLY | Yes | Requires WITH CREDENTIAL specified | |
| RESTORE VERIFYONLY | Yes | Requires WITH CREDENTIAL specified | |
| RESTORE REWINDONLY | No |
Support for Backup Arguments
| Argument | Supported | Exceptions | Comments |
|---|---|---|---|
| DATABASE | Yes | ||
| LOG | Yes | ||
| TO (URL) | Yes | Unlike DISK and TAPE, URL does not support specifying or creating a logical name. | This argument is used to specify the URL path for the backup file. |
| MIRROR TO | No | ||
| WITH OPTIONS: | |||
| CREDENTIAL | Yes | WITH CREDENTIAL is only supported when using BACKUP TO URL option to back up to the Windows Azure Blob storage service. | |
| DIFFERENTIAL | Yes | ||
| COPY_ONLY | Yes | ||
| COMPRESSION|NO_COMPRESSION | Yes | ||
| DESCRIPTION | Yes | ||
| NAME | Yes | ||
| EXPIREDATE | RETAINDAYS | No | ||
| NOINIT | INIT | No | This option is ignored if used. Appending to blobs is not possible. To overwrite a backup use the FORMAT argument. | |
| NOSKIP | SKIP | No | ||
| NOFORMAT | FORMAT | Yes | This option is ignored if used. A backup taken to an existing blob fails unless WITH FORMAT is specified. The existing blob is overwritten when WITH FORMAT is specified. | |
| MEDIADESCRIPTION | Yes | ||
| MEDIANAME | Yes | ||
| BLOCKSIZE | No | ||
| BUFFERCOUNT | Yes | ||
| MAXTRANSFERSIZE | No | ||
| NO_CHECKSUM | CHECKSUM | Yes | ||
| STOP_ON_ERROR | CONTINUE_AFTER_ERROR | Yes | ||
| STATS | Yes | ||
| REWIND | NOREWIND | No | ||
| UNLOAD | NOUNLOAD | No | ||
| NORECOVERY | STANDBY | Yes | ||
| NO_TRUNCATE | Yes | ||
Support for Restore Arguments
| Argument | Supported | Exceptions | Comments |
|---|---|---|---|
| DATABASE | Yes | ||
| LOG | Yes | ||
| FROM (URL) | Yes | The FROM URL argument is used to specify the URL path for the backup file. | |
| WITH OPTIONS: | |||
| CREDENTIAL | Yes | WITH CREDENTIAL is only supported when using RESTORE FROM URL option to restore from Windows Azure Blob Storage service. | |
| PARTIAL | Yes | ||
| RECOVERY | NORECOVERY | STANDBY | Yes | ||
| LOADHISTORY | Yes | ||
| MOVE | Yes | ||
| REPLACE | Yes | ||
| RESTART | Yes | ||
| RESTRICTED_USER | Yes | ||
| FILE | Yes | ||
| PASSWORD | Yes | ||
| MEDIANAME | Yes | ||
| MEDIAPASSWORD | Yes | ||
| BLOCKSIZE | Yes | ||
| BUFFERCOUNT | No | ||
| MAXTRANSFERSIZE | No | ||
| CHECKSUM | NO_CHECKSUM | Yes | ||
| STOP_ON_ERROR | CONTINUE_AFTER_ERROR | Yes | ||
| FILESTREAM | Yes | ||
| STATS | Yes | ||
| REWIND | NOREWIND | No | ||
| UNLOAD | NOUNLOAD | No | ||
| KEEP_REPLICATION | Yes | ||
| KEEP_CDC | Yes | ||
| ENABLE_BROKER | ERROR_BROKER_CONVERSATIONS | NEW_BROKER | Yes | ||
| STOPAT | STOPATMARK | STOPBEFOREMARK | Yes | ||
Using Backup Task in SQL Server Management Studio
The Backup task in SQL Server Management Studio has been enhanced to include URL as one of the destination options, and other supporting objects required to backup to Windows Azure storage like the SQL Credential.
The following steps describe the changes made to the Back Up Database task to allow for backing up to Windows Azure storage.
- Start SQL Server Management Studio and connect to the SQL Server instance. Select a database you want to backup, and right click on Tasks, and select Back Up… This opens the Back Up Database dialog box.
- On the general page the URL option is used to create a backup to Windows Azure storage. When you select this option, you see other options enabled on this page:
a. File Name: Name of the backup file.
b. SQL Credential: You can either specify an existing SQL Server Credential, or can create a new one by clicking on the Create next to the SQL Credential box.
c. Azure storage container: The name of the Windows Azure storage container to store the backup files.
d. URL prefix: This is built automatically using the information specified in the fields described in the previous steps. If you do edit this value manually, make sure it matches with the other information you provided previously. For example if you modify the storage URL, make sure the SQL Credential is set to authenticate to the same storage account.
SQL Server Backup to URL Using Maintenance Plan Wizard
The Maintenance Plan Wizard in SQL Server Management Studio has been enhanced to include URL as one of the destination options, and other supporting objects required to backup to Windows Azure storage like the SQL Credential.
Restoring from Windows Azure storage Using SQL Server Management Studio
If you are restoring a database using SSMS 2014, URL is included as the device to restore from. Following steps describe the changes in the Restore task to allow restoring from Windows Azure storage:
- When you select Devices in the General page of the Restore task in SQL Server Management Studio, this takes you to the Select backup devices dialog box which includes URL as a backup media type.
- When you select URL and click Add, this opens the Connect to Azure storage dialog. Specify the SQL Credential information to authenticate to Windows Azure storage.
- SQL Server then connects to Windows Azure storage using the SQL Credential information you provided and opens the Locate Backup File in Windows Azure dialog. The backup files residing in the storage are displayed on this page. Select the file you want to use to restore and click OK. This takes you back to the Select Backup Devices dialog, and Clicking OK on this dialog takes you back to the main Restore dialog where you will be able complete the restore.