SQL 2014 Backup Encryption

Available in SQL Server 2014 Enterprise Edition and Standard Edition

Backup encryption has been introduced in SQL 2014 to allow encrypting backups without being forced to encrypt the entire database. Several encryption algorithms are supported, including Advanced Encryption Standard (AES) 128, AES 192, AES 256, and Triple DES. You can also compress the encrypted backup. Encryption will cause higher CPU utilization so you must make sure that CPU’s can handle backup along with workload.

Here is what to do to encrypt your backup:

1. Create a master key with a password
2. Create a certificate or asymmetric key
3. Then backup the database and set your algorithm and the certificate that was created.

USE master;

GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘MK_Pwd_123!’;

GO

–Create a certificate or asymmetric key

USE master;

GO

CREATE CERTIFICATE Bkup_Cert_for_My_DB

WITH SUBJECT = ‘Backup Cert For My Database’

GO

–Backup the database

BACKUP DATABASE [MYDB]

TO DISK = N’C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\MYDB.bak’

WITH

COMPRESSION,

ENCRYPTION

(

ALGORITHM = AES_256,

SERVER CERTIFICATE = Bkup_Cert_for_My_DB

),

STATS = 10

GO