Skip to content

Latest commit

 

History

History
128 lines (90 loc) · 5.5 KB

create-an-encrypted-backup.md

File metadata and controls

128 lines (90 loc) · 5.5 KB
title description author ms.author ms.reviewer ms.date ms.service ms.subservice ms.topic
Create an encrypted backup
This article shows you how to create an encrypted backup in SQL Server using Transact-SQL. You can back up to disk or to Azure Storage.
MashaMSFT
mathoma
randolphwest
04/25/2024
sql
backup-restore
how-to

Create an encrypted backup

[!INCLUDE SQL Server]

This article describes the steps necessary to create an encrypted backup using Transact-SQL. For an example using [!INCLUDE ssManStudioFull], 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 [!INCLUDE ssde-md], 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

Back up to disk

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 [!INCLUDE ssnoversion-md] 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).

Back up to Azure Storage

If you're creating a backup to Azure storage using the SQL Server Backup to URL option, the encryption steps are the same, but you must use URL as the destination and a SQL Credential to authenticate to the Azure storage. If you want to configure [!INCLUDE ss-managed-backup] with encryption options, see Enable SQL Server managed backup to Azure.

Create SQL Server credential

To create a SQL Server credential, connect to the [!INCLUDE ssde-md], open a new query window, and copy and paste the following example and select Execute.

Replace <mystorageaccount> with the name of the storage account you specified when creating a storage account, and <storage account access key> with either the Primary or Secondary Access Key for the storage account.

CREATE CREDENTIAL mycredential
WITH IDENTITY = '<mystorageaccount>',
SECRET = '<storage account access key>';

Back up the database

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

In this example, mycredential is the name of the credential created previously, <mystorageaccountname> is the name of your storage account, and <mycontainername> is your storage container name.

BACKUP DATABASE AdventureWorks2022
TO URL = 'https://<mystorageaccountname>.blob.core.windows.net/<mycontainername>/AdventureWorks2022.bak'
WITH CREDENTIAL = 'mycredential',
COMPRESSION,
ENCRYPTION (
    ALGORITHM = AES_256,
    SERVER CERTIFICATE = MyTestDBBackupEncryptCert
),
STATS = 10;
GO

Related content