Quickstart: SQL backup and restore to S3-Compatible Object Storage

Applies to: SQL Server 2022 (16.x)

This quickstart helps you understand how to write backups to and restore from S3-compatible object storage.

Note

SQL Server 2022 introduced support for backing up to, and restoring from, S3-compatible object storage. SQL Server 2019 and previous versions do not support this capability.

Prerequisites

To complete this quickstart, you must be familiar with SQL Server backup and restore concepts and Transact-SQL (T-SQL) syntax. You need an S3 endpoint, 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, and have CREATE DATABASE permissions to RESTORE to a new database, or be a member of either the sysadmin and dbcreator fixed server role, or owner (dbo) of the database if restoring over an existing database.

• Create an S3 endpoint.
• Install SQL Server Management Studio.
• Install SQL Server 2022 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.
• Assign the user account to the sysadmin or dbcreator fixed role, or make the user an owner of the existing database.

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

To create the SQL Server credential for authentication, follow these steps:

1. Launch SQL Server Management Studio (SSMS) and connect to your SQL Server instance.

2. Open a New Query window.

3. Create a server level credential. The name of the credential depends on the S3-compatible storage platform. Unlike PolyBase database-scoped credentials, backup/restore credentials are stored at the instance level. When used with S3-compatible storage, the credential must be named according to the URL path.

   CREATE CREDENTIAL [s3://<endpoint>:<port>/<bucket>]
   WITH
           IDENTITY    = 'S3 Access Key',
           SECRET      = '<AccessKeyID>:<SecretKeyID>';
   GO
   

Note

For more examples of server credentials S3-compatible storage, see CREATE CREDENTIAL (Transact-SQL).

Back up database

In this step, back up the database SQLTestDB to your S3-compatible object storage using T-SQL.

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

USE [master];
GO

BACKUP DATABASE [SQLTestDB]
TO      URL = 's3://<endpoint>:<port>/<bucket>/SQLTestDB.bak'
WITH    FORMAT /* overwrite any existing backup sets */
,       STATS = 10
,       COMPRESSION;

Delete database

In this step, delete the database before performing the restore. This step is only necessary for the purpose of this tutorial, and 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 Azure SQL 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 Azure SQL 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 are currently open, you'll need to set the database into single user mode first. This will immediately end all other sessions and allow the database to be dropped.

-- If connections are currently open, 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
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 dropdown and select Add to add your device.

   

4. Enter the virtual host URL and paste in the Secret Key ID and Access Key ID for the S3-compatible object storage.

   

5. Select OK to select the backup file location.

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

7. 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];
GO
RESTORE DATABASE SQLTestDB
FROM    URL = 's3://<endpoint>:<port>/<bucket>/SQLTestDB.bak'
WITH    REPLACE /* overwrite existing database */
,       STATS  = 10;

Related content

Following is some recommended reading to understand the concepts and best practices when using S3-compatible object storage for SQL Server backups.

• SQL Server backup to URL for S3-compatible object storage
• SQL Server back up to URL for S3-compatible object storage best practices and troubleshooting