SQL Server Backup Encryption

By:   |   Comments (6)   |   Related: > Backup


Information security is one of the top priorities of a DBA and that includes backup security. In previous versions of SQL Server in order to encrypt a backup you needed third party tools. Many of these tools offered some interesting features. In this tip I will show you how to issue encrypted backups, a new feature in SQL Server 2014.


SQL Server 2014 has many new and exciting features and amongst them is the capability to perform encryption of data at backup time. In other words, when data goes outside the scope of the Database Engine it is encrypted.

SQL Server 2014 Backup Encryption Considerations

This feature is supported on the Enterprise, Business Intelligence and Standard versions of SQL Server 2014, but an encrypted backup can be restored on the Web and Express editions of SQL Server.

To encrypt a backup we need to configure an encryption algorithm (supported encryption algorithms are: AES 128, AES 192, AES 256, and Triple DES) and an encryptor (a certificate or asymmetric key). So if you don't have a Database Master Key defined you need to create one.

Asymmetric keys used to encrypt backups must be from a cryptographic provider in Extensible Key Management.

Something to keep in mind is that encrypted backups cannot be appended to an existing backup set.

An encrypted backup can be restored on another server as long as the server has the appropriate certificate.

You should perform a backup of the encryption certificate and keys then keep them safe because an encrypted backup cannot be restored without the certificate used for encryption.

In the next section of this tip, I will show you how to create an encrypted backup with the SQL Server Management Studio Backup Database Wizard and Transact-SQL code.

Setting up a sample SQL Server database and encryption configurations

First we need to create a new database to perform a backup of it. You can omit this step if you have a database you want to use.

  (NAME = SampleDB_file1,
    FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Data\SampleDB_1.mdf',
          SIZE = 100MB,          
          FILEGROWTH = 10%),

  ( NAME = SampleDB_MemoryOptimized,
    FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Data\SampleDB_MemoryOptimized')

  ( NAME = SampleDB_log_file1,
    FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Data\SampleDB_1.ldf',
          SIZE = 100MB,          
          FILEGROWTH = 10%)

As I previously mentioned, in order to create an encrypted backup we must create a Database Master Key.

USE SampleDB



We must create a certificate to act as an encryptor.

Use SampleDB

CREATE CERTIFICATE SampleDB_Backup_Certificate
   WITH SUBJECT = 'SQL Server 2014';

Now we are ready to create encrypted backups.

Creation of Encrypted Backup with the SSMS Back Up Database Wizard

To access the Back Up Database Wizard in SSMS, navigate to SQL Server Management Studio | expand the root node | expand the "Databases" folder | right click on the database you want to backup | select the "Tasks" option and select the "Back Up..." option.  On the first page of the wizard we choose the backup type and destination as shown below.

Step 1 - Backup General Page

Since encrypted backups cannot be appended to an existing backup set on the "Media Options" page of the wizard we must choose "Back up to a new media set, and erase all existing backup sets". New media set name and description are optional.

Step 2 - Backup Media Options

The last step of the wizard enables us to encrypt the backups by checking the "Encrypt Backup" option. After we check this option we must select an encryption algorithm and a certificate or asymmetric key as shown below.

Step 3 - Backup Options

Creation of Encrypted SQL Server Backups with Transact-SQL

To create an encrypted backup you must add the ENCRYPTION option to the BACKUP statement.

Here is the statement to backup the SampleDB database with encryption using a certificate.

USE master

BACKUP DATABASE [SampleDB] TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\SampleDB.bak'
  ALGORITHM = AES_256, --  { AES_128 | AES_192 | AES_256 | TRIPLE_DES_3KEY }
  SERVER CERTIFICATE = [SampleDB_Backup_Certificate]

To use an asymmetric key instead of a certificate the statement is as follows.

USE master

BACKUP DATABASE [SampleDB] TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\SampleDB.bak'
  ALGORITHM = AES_256, --  { AES_128 | AES_192 | AES_256 | TRIPLE_DES_3KEY }

Restoring an Encrypted SQL Server Backup

The restore process is as usual. You don't have to specify any extra options, but the certificate or key used to encrypt the backup must be available on the instance.

USE master

FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\SampleDB.bak'

Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Daniel Farina Daniel Farina was born in Buenos Aires, Argentina. Self-educated, since childhood he showed a passion for learning.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Tuesday, November 11, 2014 - 7:26:24 AM - Perry Whittle Back To Top (35252)

Since the cert will be stored in the master database you also create the database master key in the master database not the user database. The DMK is used to protect the private key for the cert you will use to encrypt your database backups. See more at my article here on SSC


Regards Perry

Friday, September 19, 2014 - 6:16:19 AM - Georg Back To Top (34618)

Katerina is right, the certificate must be created in master database.

Otherweise the backup command can not be find the certificate...

USE master



Best regards,

Georg Pongracz

Sunday, April 20, 2014 - 11:00:45 PM - Daniel Farina Back To Top (30173)

Hi Katerina,

You need to create a database master key on the database you want to encrypt its backups. Are you performing your backup to a new media set?

Best Regards


Friday, April 18, 2014 - 5:53:48 AM - katerina Back To Top (30099)

Thanks for useful information but it didn't work..  I can't see a certificate in backup page. I think master key should be created only master database... 

Thursday, February 6, 2014 - 5:57:00 PM - Daniel Farina Back To Top (29363)


You can create a maintenance plan with a backup task and in Options tab check "Backup Encryption"


Thank you for reading!

Monday, February 3, 2014 - 12:38:13 PM - MRSKINNMAN Back To Top (29319)

Will these features be available in maintenance plans or as in previous versions will I be creating T-SQL to get the feature i.e. password protect backups?

get free sql tips
agree to terms