Database Backup Encryption in SQL Server 2014 a Step by Step Implementation Guide

This is Page 2 of Database Backup Encryption in SQL Server 2014 a Step by Step Implementation Guide click here to read from the beginning.

Backup Database Using Encryption Feature of SQL Server 2014

Now that you have completed the prerequisites such as Database Master Key and Backup Certificate you can go ahead and create the encrypted database backups in SQL Server 2014.

/* Execute the below TSQL code to create encrypted database backup in SQL Server 2014 */

BACKUP DATABASE [HumanResource]
TO DISK = N'X:\Backups\HumanResource_Encrypted.bak'
WITH
COMPRESSION,
ENCRYPTION
(
ALGORITHM = AES_256,
SERVER CERTIFICATE = BackupEncryptionCert
),
STATS = 5
GO

Once the above TSQL script is executed successfully the backup file will be Encrypted. Learn more about Database Compression Feature.

How to Restore an Encrypted Backup on the Same Instance

Execute the below script to restore encrypted backup of the database on the same instance of SQL Server 2014.

RESTORE DATABASE [HumanResource]
FROM DISK = N'X:\Backups\HumanResource_Encrypted.bak'
WITH REPLACE, RECOVERY

How to restore Encrypted Backup on Different Instance of SQL Server were Certificate is not present

You will receive the below mentioned error message received while restoring encrypted database on the destination server when certificate is not found.

Error Message

Msg 33111, Level 16, State 3, Line 2
Cannot find server certificate with thumbprint ''.
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.

In such scenarios you need to Restore Master Key and Certificate on Destination Server. Read the steps mentioned below to restore master key and certificate on another server.

How to Restore Master Key and Certificate on Destination Server

Execute the below script to create master key on the destination server.

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'C&mpl£xP@$$Wrd';

Execute the below script to create backup certificate on the destination server.

CREATE CERTIFICATE BackupEncryptionCert
FROM FILE = 'X:\BackupCertificates\BackupEncryptionCert.bak'
WITH PRIVATE KEY (FILE = 'X:\BackupCertificates\BackupEncryptionCertKey.bak',
DECRYPTION BY PASSWORD = 'Certi%yC&mpl£xP@$$Wrd');

Once the Master Key and Certificate is created on Destination Server execute the restore database script.

RESTORE DATABASE [HumanResource]
FROM DISK = N'X:\Backups\HumanResource_Encrypted.bak'
WITH REPLACE, RECOVERY

Conclusion

As a DBA you must importantly back up the certificate or asymmetric key, preferably to a different location than the backup file it was used to encrypt. Without the certificate or asymmetric key, you will not be able to restore the backup, resulting in a non-usable database backup file. Hence before implementing this feature on a Production database test it thoroughly and document all the TSQL scripts in a Source Control.