Get started Bring yourself up to speed with our introductory content.

Microsoft Azure Blob Storage database backup and restore

With Microsoft Azure Blob Storage Service, you can easily perform database backup and restore. Here is a step-by-step approach to using the service.

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.

Creating a storage account
Figure 1: 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.

Figure 2: Creating a storage account
Figure 2: Creating a storage account

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).

Figure 3: Showing online status after creating a storage account
Figure 3: Showing online status after creating a storage account

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.

Figure 4: Storage account information needed to create stored credentials
Figure 4: Storage account information needed to create stored credentials

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).

Figure 5: Create a new container
Figure 5: Create a new container

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.

Figure 6: The New Container dialog box
Figure 6: The New Container dialog box

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.

Figure 7: The storage account dashboard showing the newly created container
Figure 7: The storage account dashboard showing the newly created container

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:

Figure 8: Template for the Create Credential statement
Figure 8: Template for the Create Credential statement

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:

Figure 9: An example of the Create Credential statement
Figure 9: An example of the Create Credential statement

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:

Figure 10: Using BACKUP database T-SQL statement to perform a full database backup
Figure 10: Using BACKUP database T-SQL statement to perform a full database backup

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).

Figure 11: The Azure Management Portal showing the newly created backup file
Figure 11: The Azure Management Portal showing the newly created backup file

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:

Figure 12: Using RESTORE database T-SQL statement to perform a full database backup
Figure 12: Using RESTORE database T-SQL statement to perform a full database backup

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.

Next Steps

Performance tests rank Microsoft Azure Blob first in cloud storage

Getting to know the many Azure management tools

A complete guide to Microsoft Azure

This was last published in August 2015

Dig Deeper on SQL Server Backup and Recovery

Join the conversation

1 comment

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

Does your organization use Microsoft Azure Blob Storage Service? Why or why not?
Cancel

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close