MSSQL .BAK Encryption

This post goes over TSQL commands to setup and test encrypted backups for MSSQL. Commands provided are intended to be ran on both a Source and Target instance. These should be ran using an SA account to avoid permission issues. If storage paths don't work, just use c:\temp\ and move accordingly.

Source Server:

--- Creates a database master key

USE master
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'valuehere'
GO

--- Backup master key

BACKUP MASTER KEY
TO FILE = 'd:\backuppath\master.key'
ENCRYPTION BY PASSWORD = 'valuehere'
GO

-- Generate Cert for use with DB Backup

USE master
GO
CREATE CERTIFICATE CertName
WITH SUBJECT = 'Test Server Encryption Certificate'
GO

-- Backup DB w/ Cert

BACKUP DATABASE [testdb]
TO DISK = N'd:\backuppath\test.bak'
WITH
   COMPRESSION,     ENCRYPTION     (     ALGORITHM = AES_256,     SERVER CERTIFICATE = CertName     ),     STATS = 10 GO

-- Backup Cert using Private Key and Path

BACKUP CERTIFICATE CertName TO FILE = 'd:\backuppath\certfilename.cer'
   WITH PRIVATE KEY     (     FILE = 'd:\pathtokey\private.key',     ENCRYPTION BY PASSWORD = 'valuehere'     ) GO

-- Drop Key/Cert from MSSQL Instance

DROP MASTER KEY
DROP CERTIFICATE CertName

Target Server:

Copy over Master/Private Keys DB and Cert from your source server before proceeding.

-- Restore Master Key

RESTORE MASTER KEY
FROM FILE = 'd:\pathto\master.key'
DECRYPTION BY PASSWORD = 'valuehere'
ENCRYPTION BY PASSWORD = 'valuehere'

-- Open Master Key

OPEN MASTER KEY
DECRYPTION BY PASSWORD = 'valuehere'

-- Import Certificate w/ Private Key Value

Create CERTIFICATE CertName
FROM FILE = 'd:\pathto\certfilename.cer'
WITH PRIVATE KEY
   (     FILE = 'd:\pathto\private.key'     DECRYPTION BY PASSWORD = 'valuehere'     ) GO

-- Restore DB w/ Overwrite

RESTORE DATABASE [test]
FROM DISK = 'd:\backup\test.bak'
WITH REPLACE
GO

-- Clear Cert/Key on Source or Target

DROP CERTIFICATE CertName
DROP MASTER KEY