SQL Server Administration: 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:
-
Create a master key with a password
-
Create a certificate or asymmetric key
-
Then backup the database and set your algorithm and the certificate that was created.
USEmaster;
GO
CREATEMASTERKEYENCRYPTIONBYPASSWORD='MK_Pwd_123!';
GO
--Create a certificate or asymmetric key
USEmaster;
GO
CREATECERTIFICATE Bkup_Cert_for_My_DB
WITHSUBJECT='Backup Cert For My Database'
GO
--Backup the database
BACKUPDATABASE [MYDB]
TODISK=N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\MYDB.bak'
WITH
COMPRESSION,
ENCRYPTION
(
ALGORITHM=AES_256,
SERVERCERTIFICATE= Bkup_Cert_for_My_DB
),
STATS= 10
GO
SQLRx has worked hard to minimize the impact of monitoring SQL Servers to the point that we are able to monitor high transaction systems with little impact (1% load) on the target system. Contact us today to help you monitor your SQL servers!
| 
