Requirement for SQL Server Backup Encryption
Database Master Key (DMK)
Certificate with private Key
Create DMK
Check if Database Master Key (DMK) using following
command
SELECT * FROM master.sys.symmetric_keys;
If the ##MS_DatabaseMasterKey## row does not exist, use the following query to create it:
CREATE MASTER KEY ENCRYPTION BY PASSWORD='StrongPassword';
Create Certificate
USE master
GO
CREATE CERTIFICATE DBBackupEncryptionCertificate
WITH SUBJECT = 'This Cert will be used in db backup encription';
GO
Backup Database
Now you are ready to backup the database
BACKUP DATABASE [AdventureWorks]
TO DISK = N'D:\AdventureWorksEncryptedBackup.bak'
WITH
COMPRESSION,
ENCRYPTION
(
ALGORITHM = AES_256,
SERVER CERTIFICATE = DBBackupEncryptionCertificate
)
GO
Part3 will soon be posted