Create an Encrypted Backup

THIS TOPIC APPLIES TO: yesSQL ServernoAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

This topic 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 (SQL Server).

Backup to Disk with Encryption

Prerequisites:

  • Access to a local disk or to storage with adequate space to create a backup of the database.

  • A Database Master Key for the master database, and a certificate or asymmetric key available on the instance of SQL Server. For encryption requirements and permissions, see Backup Encryption.

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

  1. Create a Database Master Key of the master database: Choose a password for encrypting the copy of the master key that will be stored in the database. Connect to the database engine, start a new query windows and copy and paste the following example and click Execute.

    -- Creates a database master key.   
    -- The key is encrypted using the password "<master key password>"  
    USE master;  
    GO  
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<master key password>';  
    GO  
    
  2. Create a Backup Certificate: Create a backup certificate in the master database. Copy and paste the following example into the query window and click Execute

    Use Master  
    GO  
    CREATE CERTIFICATE MyTestDBBackupEncryptCert  
       WITH SUBJECT = 'MyTestDB Backup Encryption Certificate';  
    GO  
    
  3. Backup the database: Specify the encryption algorithm and certificate to use. Copy and paste the following example into the query window and click Execute.

    BACKUP DATABASE [MyTestDB]  
    TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\MyTestDB.bak'  
    WITH  
      COMPRESSION,  
      ENCRYPTION   
       (  
       ALGORITHM = AES_256,  
       SERVER CERTIFICATE = MyTestDBBackupEncryptCert  
       ),  
      STATS = 10  
    GO  
    

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

Backup to Windows Azure Storage with Encryption

If you are creating a backup to Windows 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 Windows Azure storage. If you want to configure SQL Server Managed Backup to Microsoft Azure with encryption options, see Enable SQL Server Managed Backup to Microsoft Azure.

Prerequisites:

  1. Create SQL Server Credential: To create a SQL Server credential, connect to the Database Engine, open a new query window, and copy and paste the following example and click Execute.

    CREATE CREDENTIAL mycredential   
    WITH IDENTITY= 'mystorageaccount' – 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  
    
  2. Create a Database Master Key: Choose a password for encrypting the copy of the master key that will be stored in the database. Connect to the database engine, start a new query windows and copy and paste the following example and click Execute.

    -- Creates a database master key.  
    -- The key is encrypted using the password "<master key password>"  
    USE Master;  
    GO  
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<master key password>';  
    GO  
    
  3. Create a Backup Certificate: Create a Backup Certificate in the master database. Copy and paste the following example in the query window and click Execute

    USE Master;  
    GO  
    CREATE CERTIFICATE MyTestDBBackupEncryptCert  
       WITH SUBJECT = 'MyTestDBBackupEncryptCert ';  
    GO  
    
  4. Backup the database: Specify the encryption algorithm and the certificate to use. Copy and paste the following example into the query window and click Execute.

    BACKUP DATABASE [MyTestDB]  
    TO URL = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\MyTestDB.bak'  
    WITH  
      CREDENTIAL 'mycredential' – this is the name of the credential created in the first step.  
      ,COMPRESSION  
      ,ENCRYPTION   
       (  
       ALGORITHM = AES_256,  
       SERVER CERTIFICATE = MyTestDBBackupEncryptCert  
       ),  
      STATS = 10  
    GO