Back up Encryption

Published On: 2018-09-05By:

Nowadays security breaches happen way to frequently. As DBA’s we should all take extra care with sensitive data and ensure we are encrypting correctly. But some of us forget to take it a step farther and don’t encrypt our backups. For those working in an ultra data sensitive environment I highly recommend you go this extra step.

As of SQL 2014, SQL Server now has the ability to encrypt your data during the backup process. Not only that but they have made the process simple. Prior to 2014, the only way to encrypt backups was Transparent Data Encryption (TDE), and that requires Enterprise Edition which made this out of reach for most.

First steps you must create a MASTER KEY and a Certificate ( or asymmetric key). You will notice that the Certificate has an expiration date. If this expires don’t fret, you will still be able to restore you backup however you will not be able to perform new backups until you create a new certificate. Depending on your environment you may have to have a routine process of managing your certificates and renewals. If you don’t, take care in what you set this date to to avoid this.

USE Master; 


USE Master; 
   WITH SUBJECT = 'This is a Test',  
   EXPIRY_DATE = '20200831'; 

To verify it created you can run the below.

 FROM sys.certificates AS C
 WHERE = 'ExampleCert1';

After that is done you’re ready to create your encrypted backup. Before you do you must however make sure you have the VIEW DEFINITION permission for the Certificate.

use [Master]

Using GUI

Under the MASTER Database go to Security then User.

Choose the User you want to grant permission to then go to properties.

Click on Securables, you will note its blank. You need to choose search.


Check the Certificate you are grant rights to and check GRANT for VIEW DEFINITION

Now try backing up your database. Note the Encryption option and the server certificate name.

BACKUP DATABASE [AdventureWorks2014] 
TO DISK = N'C:\temp\AWEncrypt.bak' 
   ALGORITHM = AES_256, 
   SERVER CERTIFICATE = [ExampleCert1] 
  STATS = 10 

Or using GUI

If you don’t have the correct permission, you will see this error when you try to perform the back up.

Now that you have a backup we need to see if we can restore it. But first this is important, so I am going to quote Microsoft directly here from MSDN, without the key and certificate YOU CANNOT RESTORE!

“Create a backup of the encryption certificate and keys to a location other than your local machine where the instance is installed. To account for disaster recovery scenarios, consider storing a backup of the certificate or key to an off-site location. You cannot restore an encrypted backup without the certificate used to encrypt the backup.”

Restoring is just a normal restore as long as the key and certificate are present on that server.

USE [master]

RESTORE DATABASE [AdventureWorks2014]
FROM DISK = N'C:\temp\AWEncrypt.bak' WITH  FILE = 1,  NOUNLOAD,  STATS = 5

It’s a pretty simple process to ensure your backups are encrypted. The tricky part is to make sure you back up the Master Key and Certificate and safe guard them. If you don’t have them you can’t restore (it’s worth repeating).  So take the extra step if you can and make your data even more secure by encrypting you backups.

Contact the Author | Contact DCAC


Globally Recognized Expertise

As Microsoft MVP’s and Partners as well as VMware experts, we are summoned by companies all over the world to fine-tune and problem-solve the most difficult architecture, infrastructure and network challenges.

And sometimes we’re asked to share what we did, at events like Microsoft’s PASS Summit 2015.

Awards & Certifications

Microsoft Partner   Denny Cherry & Associates Consulting LLC BBB Business Review    Microsoft MVP    Microsoft Certified Master VMWare vExpert
INC 5000 Award for 2020    American Business Awards People's Choice    American Business Awards Gold Award    American Business Awards Silver Award    FT Americas’ Fastest Growing Companies 2020   
Best Full-Service Cloud Technology Consulting Company       Insights Sccess Award    Technology Headlines Award    Golden Bridge Gold Award    CIO Review Top 20 Azure Solutions Providers
Share via
Copy link