Edit

Create an encrypted backup

  • Article

Applies to: SQL Server

This article describes the steps necessary to create an encrypted backup using Transact-SQL. For an example using SQL Server Management Studio, see Create a Full Database Backup.

Caution

To restore an encrypted database, you need access to the certificate or asymmetric key used to encrypt that database. Without the certificate or asymmetric key, you can't restore that database. Save the certificate used to encrypt the database encryption key for as long as you need to save the backup. For more information, see SQL Server Certificates and Asymmetric Keys.

Prerequisites

  • Storage for the encrypted backup. Depending on which option you choose, one of:

    • A local disk or to storage with adequate space to create a backup of the database.
    • An Azure Storage account and a container. For more information, see Create a storage account.

  • A database master key (DMK) for the master database, and a certificate or asymmetric key on the instance of SQL Server. For encryption requirements and permissions, see Backup encryption.

Create a database master key (DMK)

Choose a password for encrypting the copy of the DMK that will be stored in the database. Connect to the Database Engine, start a new query window, copy and paste the following example, and select Execute.

Replace <master key password> with a strong password, and make sure you keep a copy of both the DMK and the password in a secure location.

USE master;
GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<master key password>';
GO

Create a backup certificate

Create a backup certificate in the master database. Copy and paste the following example into the query window and select Execute.

Use master;
GO

CREATE CERTIFICATE MyTestDBBackupEncryptCert
    WITH SUBJECT = 'MyTestDB Backup Encryption Certificate';
GO

Back up the database with encryption

There are two main options for creating an encrypted backup:

  • Back up to disk
  • Back up to Azure Storage

Use the following steps to create an encrypted backup of a database to a local disk. This example uses a user database called MyTestDB.

Specify the encryption algorithm and certificate to use. Copy and paste the following example into the query window and select Execute.

Replace <path_to_local_backup> to a local path that SQL Server has permission to write to. For example, this path might be D:\SQLBackup.

BACKUP DATABASE [MyTestDB]
TO DISK = N'<path_to_local_backup>\MyTestDB.bak'
WITH
COMPRESSION,
ENCRYPTION (
    ALGORITHM = AES_256,
    SERVER CERTIFICATE = MyTestDBBackupEncryptCert
),
STATS = 10;
GO

For an example of encrypting a backup protected by Extensible Key Management (EKM), see Extensible Key Management Using Azure Key Vault (SQL Server).

Related content