TDE is an encyption technique supported only on Enterpise Edition but where as SQL 2014 backup encryption is supported in Standard, Business Intelligence and Enterprise editions. Also with TDE, it implies some CPU overhead and the backup file compression ratio is then very low. So if an organizations using TDE just for sake of encrypting backup files or using any other 3rd party tool, then this backup encryption can be an alternative solution.
The supported SQL Server 2014 backup encryption algorithms are: AES 128, AES 192, AES 256, and Triple DES.
/*****Create Database Master Key on Master DB*****/
USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'xxxxxxxxx'
--Create an encryption certificate
Use Master
GO
CREATE CERTIFICATE SQLbackupEncrptCert
WITH SUBJECT = 'SQL Backup Encryption Certificate';
/*****Backup Service Master Key:*****/
USE master
GO
BACKUP SERVICE MASTER KEY
TO FILE = 'E:\Program Files\Microsoft SQL Server\MSSQL12.SQLTWO\MSSQL\Backup\SQLTest_SMK.key'
ENCRYPTION BY PASSWORD = 'xxxxxxxxx';
GO
/*****Backup Database Master Key*****/
BACKUP MASTER KEY
TO FILE = 'E:\Program Files\Microsoft SQL Server\MSSQL12.SQLTWO\MSSQL\Backup\SQLTest_DMK.key'
ENCRYPTION BY PASSWORD = 'xxxxxxxxx';
GO
/*****Backup Certificate*****/
Use Master
GO
BACKUP CERTIFICATE SQLbackupEncrptCert
TO FILE = 'E:\Program Files\Microsoft SQL Server\MSSQL12.SQLTWO\MSSQL\Backup\SQLbackupEncrptCert.cert'
WITH PRIVATE KEY
(
FILE = 'E:\Program Files\Microsoft SQL Server\MSSQL12.SQLTWO\MSSQL\Backup\SQLbackupEncrptCert_PK.key',
ENCRYPTION BY PASSWORD = 'xxxxxxxxx'
);
GO
/*****Backup database with encryption*****/
BACKUP DATABASE TestDb
TO DISK = 'E:\Program Files\Microsoft SQL Server\MSSQL12.SQLTWO\MSSQL\Backup\TestDB_SQL2014_Encrypt.bak'
WITH COMPRESSION,
ENCRYPTION
(
ALGORITHM = AES_256,
SERVER CERTIFICATE = SQLbackupEncrptCert
)
Appending to an existing backup set option is not supported for encrypted backups.
SQL Server Express and SQL Server Web do not support backup encryption but an encrypted backup can be restored.
