Quickstart: SQL backup and restore to Azure Blob Storage

Applies to: SQL Server 2016 (13.x) and later Azure SQL Managed Instance

This quickstart helps you understand how to write backups to and restore from the Azure Blob Storage. The article explains how to create an Azure Blob Storage container, write a backup to Azure Blob Storage, and then perform a restore.

Note

SQL Server 2012 SP1 CU2 introduced support for back up to Azure Blob storage. SQL Server 2014 and prior does not support the Shared Access Signature (SAS) that is described in this quickstart article.

For SQL Server 2014 and prior, use Tutorial: SQL Server 2014 Backup and Restore to Microsoft Azure Blob storage.

Prerequisites

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

• Get a free Azure Account.
• Create an Azure storage account.
• Install SQL Server Management Studio.
• Install SQL Server 2017 Developer Edition or deploy Azure SQL Managed Instance with connectivity established through an Azure SQL virtual machine or point-to-site.
• Assign the user account to the role of db_backupoperator and grant alter any credential permissions.

Create Azure Blob Storage container

A container provides a grouping of a set of blobs. All blobs must be in a container. A storage 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.

3. Select the storage account, scroll down to Blob Services.

4. Select Blobs and then select + Container to add a new container.

5. 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 quickstart.

6. Select OK.

   

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 programmatically using REST APIs. For more information, see Create container

Create a test database

In this step, create a test database using SQL Server Management Studio (SSMS).

1. Launch SQL Server Management Studio (SSMS) and connect to your SQL Server instance.
2. Open a New Query window.
3. Run the following Transact-SQL (T-SQL) code to create your test database. Refresh the Databases node in Object Explorer to see your new database. Newly created databases on SQL Managed Instance automatically have TDE enabled so you'll need to disable it to proceed.

USE [master]
GO

-- Create database
CREATE DATABASE [SQLTestDB]
GO

-- Create table in database
USE [SQLTestDB]
GO
CREATE TABLE SQLTest (
    ID INT NOT NULL PRIMARY KEY,
    c1 VARCHAR(100) NOT NULL,
    dt1 DATETIME NOT NULL DEFAULT getdate()
)
GO

-- Populate table 
USE [SQLTestDB]
GO

INSERT INTO SQLTest (ID, c1) VALUES (1, 'test1')
INSERT INTO SQLTest (ID, c1) VALUES (2, 'test2')
INSERT INTO SQLTest (ID, c1) VALUES (3, 'test3')
INSERT INTO SQLTest (ID, c1) VALUES (4, 'test4')
INSERT INTO SQLTest (ID, c1) VALUES (5, 'test5')
GO

SELECT * FROM SQLTest
GO

-- Disable TDE for newly-created databases on SQL Managed Instance 
USE [SQLTestDB];
GO
ALTER DATABASE [SQLTestDB] SET ENCRYPTION OFF;
GO
DROP DATABASE ENCRYPTION KEY
GO

Create credential

Use the GUI in SQL Server Management Studio to create the credential by following the steps below. Alternatively, you can create the credential programmatically as well.

1. Expand the Databases node within Object Explorer of SQL Server Management Studio(SSMS).

2. Right-click your new SQLTestDB database, hover over Tasks and then select Back up... to launch the Back Up Database wizard.

3. Select URL from the Back up to destination drop-down, and then select Add to launch the Select Backup Destination dialog box.

   

4. Select New container on the Select Backup Destination dialog box to launch the Connect to a Microsoft Subscription window.

   

5. Sign in to the Azure portal by selecting Sign In... and then proceed through the sign-in process.

6. Select your subscription from the drop-drown.

7. Select your storage account from the drop-down.

8. Select the container you created previously from the drop-down.

9. Select Create Credential to generate your Shared Access Signature (SAS). Save this value as you'll need it for the restore.

   

10. Select OK to close the Connect to a Microsoft Subscription window. This populates the Azure storage container value on the Select Backup Destination dialog box. Select OK to choose the selected storage container, and close the dialog box.

11. At this point, you can either skip ahead to step 4 in the next section to take the backup of the database, or close the Back up Database wizard if you want to proceed with using Transact-SQL to back up the database instead.

Back up database

In this step, back up the database SQLTestDB to your Azure Blob storage account using either the GUI within SQL Server Management Studio, or Transact-SQL (T-SQL).

SSMS

1. If the Back Up Database wizard is not already open, expand the Databases node within Object Explorer of SQL Server Management Studio(SSMS).

2. Right-click your new SQLTestDB database, hover over Tasks and then select Back up... to launch the Back Up Database wizard.

3. Select URL from the Back up to drop down, and then select Add to launch the Select Backup Destination dialog box.

   

4. Select the container you created in the previous step in the Azure storage container drop-down.

   

5. Select OK on the Back Up Database wizard to back up your database.

6. Select OK once your database is backed up successfully to close all backup-related windows.

   Tip

   You can script out the Transact-SQL behind this command by selecting Script at the top of the Back Up Database wizard:

Transact-SQL

Back up your database using Transact-SQL by running the following command:

USE [master]

BACKUP DATABASE [SQLTestDB] 
TO  URL = N'https://msftutorialstorage.blob.core.windows.net/sql-backup/sqltestdb_backup_2020_01_01_000001.bak' 
WITH  COPY_ONLY, CHECKSUM
GO

Delete database

In this step, delete the database before performing the restore. This step is only necessary for the purpose of this tutorial, but is unlikely to be used in normal database management procedures. You can skip this step, but then you'll either need to change the name of the database during the restore on a managed instance, or run the restore command WITH REPLACE to restore the database successfully on-premises.

SSMS

1. Expand the Databases node in Object explorer, right-click the SQLTestDB database, and select delete to launch the Delete object wizard.
2. On a managed instance, select OK to delete the database. On-premises, check the checkbox next to Close existing connections and then select OK to delete the database.

Transact-SQL

Delete the database by running the following Transact-SQL command:

USE [master]
GO
DROP DATABASE [SQLTestDB]
GO

-- If connections currently exist on-premises, you'll need to set the database into single user mode first
USE [master]
GO
ALTER DATABASE [SQLTestDB] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
USE [master]
GO
DROP DATABASE [SQLTestDB]
GO

Restore database

In this step, restore the database using either the GUI in SQL Server Management Studio, or with Transact-SQL.

SSMS

1. Right-click the Databases node in Object Explorer within SQL Server Management Studio and select Restore Database.

2. Select Device and then select the ellipses (...) to choose the device.

   

3. Select URL from the Backup media type drop-down and select Add to add your device.

   

4. Select the container from the drop-down and then paste in the Shared Access Signature (SAS) you saved when creating the credential.

   

5. Select OK to select the backup file location.

6. Expand Containers and select the container where your backup file exists.

7. Select the backup file you want to restore and then select OK. If no files are visible, then you may be using the wrong SAS key. You can regenerate the SAS key again by following the same steps as before to add the container.

   

8. Select OK to close the Select backup devices dialog box.

9. Select OK to restore your database.

Transact-SQL

To restore your on-premises database from Azure Blob storage, modify the following Transact-SQL command to use your own storage account and then run it within a new query window.

USE [master]
RESTORE DATABASE [SQLTestDB] FROM 
URL = N'https://msftutorialstorage.blob.core.windows.net/sql-backup/sqltestdb_backup_2020_01_01_000001.bak'

See also

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

• SQL Server Backup and Restore with Microsoft Azure Blob Storage
• SQL Server Backup to URL Best Practices and Troubleshooting