Tutorial: SQL Server Backup and Restore to Azure Blob Storage Service

APPLIES TO: yesSQL Server noAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

This tutorial helps you understand how to write backups to and restore from the Azure Blob Storage Service. The tutorial explains how to create an Azure Blob Container, credentials for accessing the storage account, writing a backup to the blob service, and then performing a simple restore.

Prerequisites

To complete this tutorial, you must be familiar with SQL Server backup and restore concepts and T-SQL syntax. To use this tutorial, you need an Azure storage account, SQL Server Management Studio (SSMS), access to a server that's running SQL Server, and an AdventureWorks database. Additionally, the account used to issue the BACKUP and RESTORE commands should be in the db_backupoperator database role with alter any credential permissions.

Create Azure Blob Container

A container provides a grouping of a set of blobs. All blobs must be in a container. An account can contain an unlimited number of containers, but must have at least one container. A container can store an unlimited number of blobs.

To create a Container, follow these steps:

  1. Open the Azure Portal.

  2. Navigate to your Storage Account.

    1. Select the storage account, scroll down to Blob Services.
    2. Select Blobs and then select +Container to add a new container.
    3. Enter the name for the container and make note of the container name you specified. This information is used in the URL (path to backup file) in the T-SQL statements later in this tutorial.
    4. Select OK.

    New container

Note

Authentication to the storage account is required for SQL Server backup and restore even if you choose to create a public container. You can also create a container programatically using REST APIs. For more information, see Create container

Create a SQL Server 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 credentials to authenticate with 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. For more information about credentials, see Credentials.

Important

The requirements for creating a SQL Server credential described below are specific to SQL Server backup processes (SQL Server Backup to URL, and SQL Server Managed Backup to Microsoft Azure). SQL Server uses the storage account name and access key information when accessing Azure storage to write or read backups.

Access keys

Since the Azure Portal is still open, save the access keys necessary for creating the credential.

  1. Navigate to the Storage Account in the Azure Portal.

  2. Scroll down to Settings and select Access Keys.

  3. Save both the key and connection string to use later in this tutorial.

    Access keys

Create a SQL Server credential

Using the access key you saved, create the SQL Server credential following the steps below.

  1. Connect to your SQL Server using SQL Server Management Studio.
  2. Select the AdventureWorks2016 database and open a New Query window.
  3. Copy, paste, and execute the following example into the query window, modifying as needed:
CREATE CREDENTIAL mycredential   
WITH IDENTITY= 'msftutorialstorage', -- this is the name of the storage account you specified when creating a storage account   
SECRET = '<storage account access key>' -- this should be either the Primary or Secondary Access Key for the storage account 
  1. Execute the statement to create the credential.

Backup database to the Windows Azure Blob Storage Service

In this section, you will use a T-SQL statement to perform a full database backup to the Windows Azure Blob Storage service.

  1. Connect to your SQL Server using SQL Server Management Studio.
  2. Select the AdventureWorks2016 database and open a New Query window.
  3. Copy and paste the following example into the query window, modifying as needed:
BACKUP DATABASE [AdventureWorks2016] 
TO URL = 'https://msftutorialstorage.blob.core.windows.net/sql-backup/AdventureWorks2016.bak' 
/* URL includes the endpoint for the BLOB service, followed by the container name, and the name of the backup file*/ 
WITH CREDENTIAL = 'mycredential';
/* name of the credential you created in the previous step */ 
GO
  1. Execute the statement to back up your AdventureWorks2016 database to URL.

Restore database from Windows Azure Blob Storage Service

In this section, you will use a T-SQL statement to restore the full database backup.

  1. Connect to your SQL Server using SQL Server Management Studio.
  2. Open a New Query window.
  3. Copy and paste the following example into the query window, modifying as needed:
RESTORE DATABASE AdventureWorks2016 
FROM URL = 'https://msftutorialstorage.blob.core.windows.net/sql-backup/AdventureWorks2016.bak' 
WITH CREDENTIAL = 'mycredential',
STATS = 5 -- use this to see monitor the progress
GO

See also

Following is some recommended reading to understand the concepts and best practices when using Azure Blob storage service for SQL Server backups.