SQL Server supports backups to and restores from Microsoft Azure Blob Storage Service. This feature was first introduced...
in SQL Server 2012 Cumulative Update 2, and initially was supported only through Transact-SQL backup and restore statements, PowerShell cmdlets and SQL Server Management Objects (SMOs). Now, SQL Server 2014 supports this feature in SQL Server 2014 Management Studio (SSMS 2014) via the backup and restore screens, as well as the Maintenance Plan wizard.
This feature supports a maximum backup size of 1 TB. The feature does not support backups to multiple blobs in a single backup operation or appending to existing blobs. The main benefits of this feature include flexible, reliable -- three-copies geo-DR -- and limitless off-site storage. This article provides step-by-step instructions for performing a database backup and restore to and from Microsoft Azure Blob Storage Service.
First, sign into the Microsoft Azure Management Portal using your Azure account. If you do not have an Azure account, you can start with a with a one-month free trial of Microsoft Azure.
Create a storage account
A storage account provides access to the Azure Blob, Table and Queue services within a geographic region. A storage account created on or after Jun. 8, 2012, can contain 200 TB of data. Storage accounts created before Jun. 8, 2012, can contain up to 100 TB of data.
To create a storage account, if you do not already have one, click "New." Then, click "Data Services." After that, click "Storage." And then, click "Quick Create." A short form is displayed (Figure 1), which you can fill out to create a storage account.
In the form's URL text box, enter a name for your storage account. Remember, storage account names must be between three and 24 characters, and can only use numbers and lowercase letters. I'm going to call my storage account europestorage01.
Next, in the Location/Affinity Group dropdown list, select a location for your storage account. You can choose any location; however, the recommended choice is the location closest to you or your customers. I'm choosing North Europe as the location for my storage account.
Finally, in the Replication dropdown list, choose the preferred Azure storage replication option of your storage account. I'm choosing Geo-Redundant, which is the recommended replication option for my storage account. This option provides maximum durability for your data. For more details about various Azure storage replication options, see Azure Storage Replication.
Once you have made your selections, click "Create Storage Account" to create your storage account. Note that it may take a few minutes to create your storage account. To check the status, you can monitor the notifications at the bottom of the Microsoft Azure Management Portal website. After the storage account has been created, your new storage account has online status and is ready for use (Figure 3).
After the storage account is created, click "Manage Access Keys" at the bottom center of the webpage to view the storage account information (Figure 4). Copy the storage account name, as well as the primary and secondary access keys. You need this information to create SQL Stored Credentials, which are used by SQL Server to access the storage account and create backups.
Create a blob container
Now that you have created the storage account, you need to create a container. Containers provide a logical grouping for blobs stored in the Microsoft Azure Blob service. When you upload a blob to the Blob service, you must specify a container for that blob. The container forms part of the URI that uniquely identifies the blob. A storage account can contain any number of blob containers, but must have at least one.
You use the Containers page to create a new container, view its contents, configure its properties and metadata, or delete the container. Note that when you delete a container, any blobs that it contains are deleted and cannot be retrieved.
To create a container, select the storage account. Next, activate the Containers tab and then click "Create A Container" at the bottom of the screen, which opens a New Container dialog box (Figure 5).
In the New Container dialog box, type the name of the container. A valid container name should be between three and 63 characters. Additionally, a valid container must contain only lowercase letters, numbers and hyphens, and must begin with a letter or number. The container name cannot contain two consecutive hyphens. I'm naming my container europecontainer01.
Next, select the Access Type for the container. There are three Access Type options: Private, Public Blob and Public Container. By default, the container is private and can be accessed only by the account owner. To allow public read access to the blobs in the container, but not the container properties and metadata, use the Public Blob option. To allow full public read access for the container and blobs, use the Public Container option. Microsoft recommends creating private containers to secure your backup files. I'll select Private for Access Type.
Clicking "OK" will create the container, which will be visible in the storage account dashboard (Figure 6). Make note of the container URL, because you need this information in the path to the backup file in the T-SQL statements.
Create a SQL Server credential
After you create the blob container, you need to create a SQL Server credential.
To create a SQL Server credential, launch SSMS 2014. Next, in Object Explorer, connect to the instance of Database Engine with database you want to back up to Azure Blob Storage. For example, I'll be backing up the OUTLANDER database, which is hosted on the SQL Server instance running on my machine to Microsoft Azure Blob Storage Service.
On the standard toolbar, click "New Query." Then, type and use the Create Credential statement to create the SQL Server credential. The general syntax for the Create Credential command is as follows:
Modify the Create Credential syntax identity_name parameter with the name of the storage account and secret parameter you created with the primary or secondary Access Key for your storage account. For example, in my case, the Create Credential statement is as follows:
Performing full database backup to Azure Blob Storage Service
To perform the backup to Microsoft Azure Blob Storage Service, you can use either a Transact-SQL BACKUP statement, or the backup screen and Maintenance Plan wizard of the SQL Server 2014 Management Studio. I'm using the BACKUP database Transact-SQL statement to perform the full OUTLANDER database backup to Microsoft Azure Blob Storage Service. To do this, run the following code in the SSMS 2014 Query Editor window:
As you see from the Backup Database statement, the URL includes the endpoint for the BLOB service, followed by the container name and the name of the backup file. The credential includes the credential name created in the previous step.
After the backup command completes successfully, you can view the newly created backup file in the Microsoft Azure Management Portal (Figure 11).
Perform a restore from a full database backup
To restore from a backup file created in Microsoft Azure Blob Storage Service, you can use either Transact-SQL RESTORE statement, or the SSMS 2014 restore screen. I'm using the restore database Transact-SQL statement to restore from a full database backup of the OUTLANDER database created in the previous step. To do this, run the following code in the SSMS 2014 Query Editor window:
As you see from the Restore Database statement, the URL includes the endpoint for the BLOB service, followed by the container name and the name of the backup file. The credential includes the name of the credential I created in the previous step.
Performance tests rank Microsoft Azure Blob first in cloud storage
Getting to know the many Azure management tools
A complete guide to Microsoft Azure