Let’s work through some code to do an encrypted backup. This feature is available to you if you are using SQL Server 2014 onwards but I decided to use SQL Server 2017.

To encrypt during backup, you must specify an encryption algorithm, and an “encryptor” to secure the encryption key. I have decided to use the following options:

• Encryption Algorithm: AES 256
• Encryptor: A certificate

First step is to create a certificate.

USE master;
GO
CREATE CERTIFICATE DBBackupEncryptCert
   WITH SUBJECT = 'DBBackupEncryptCert ';
GO 

Next step, Issue the backup command and state the certificate (above) and the algorithm choice. As you can see I opt for AES_256, the algorithms are implemented using the Windows Crypto API.

This is the main code.

BACKUP DATABASE [AdventureWorks2020]
TO DISK = 'C:\SQLSERVER\backups\AdventureWorks2020Full2.bak'
WITH
  COMPRESSION,
  COPY_ONLY,
  ENCRYPTION
   (
   ALGORITHM = AES_256,
   SERVER CERTIFICATE = DBBackupEncryptCert
   ),
  STATS = 5
GO

Do not forget about the certificate! Warning: The certificate used for encrypting the database encryption key has not been backed up. Imagine if you need to recover the backup and you can’t?  You will get the dreaded thumbprint error.

Msg 33111, Level 16, State 3, Line 25 Cannot find server certificate with thumbprint ‘0x78FAB5A2A5D593FD3C4E163C90B745F70AB51233’. Msg 3013, Level 16, State 1, Line 25

RESTORE DATABASE is terminating abnormally.

So make sure you respect this certificate (and the key) and back it up and re-create them on the target server for a successful restore.

 -- SOURCE SERVER
USE master
GO
BACKUP CERTIFICATE DBBackupEncryptCert
TO FILE = 'C:\SQLSERVER\backups\DBBackupEncryptCert.cer'
WITH PRIVATE KEY
(FILE = 'C:\SQLSERVER\backups\DBBackupEncryptKey.pvk',
ENCRYPTION BY PASSWORD = 'Hslfm30dBLOB1278EAT2!')
 -- TARGET SERVER
CREATE CERTIFICATE DBBackupEncryptCert
FROM FILE = ' C:\SQLSERVER\backups\DBBackupEncryptCert.cer'
WITH PRIVATE KEY (FILE = ' C:\SQLSERVER\backups\DBBackupEncryptKey.pvk',
DECRYPTION BY PASSWORD = 'Hslfm30dBLOB1278EAT2!');
GO
USE [master]
RESTORE DATABASE [AdventureWorks2020]
FROM  DISK = ' C:\SQLSERVER\backups\AdventureWorks2020Full2.bak 'WITH  FILE = 1, STATS = 5

RESTORE DATABASE successfully processed 24266 pages in 1.038 seconds (182.634 MB/sec).

What effect does this have on backup compression?

I ran the following 3 backup commands, first one with no extra options, second command with compression then finally compression with encryption – the question is does encryption on the backup affect compression rates?

-- NOTHING - NAKED
BACKUP DATABASE  [NEWdb]
TO DISK = 'C:\SQLSERVER\backups\NewDBFull1.bak'
WITH  
 COPY_ONLY,
 STATS = 5
GO  
-- COMPRESSION ONLY
BACKUP DATABASE  [NEWdb]
TO DISK = 'C:\SQLSERVER\backups\NewDBFull2.bak'
WITH  
  COPY_ONLY,
  COMPRESSION,
  STATS = 5
GO  
-- WITH COMPRESSION AND ENCRYTPION
BACKUP DATABASE  [NEWdb]
TO DISK = 'C:\SQLSERVER\backups\NewDBFull3.bak'
WITH
  COMPRESSION,
  COPY_ONLY,
  ENCRYPTION
   (
   ALGORITHM = AES_256,
   SERVER CERTIFICATE = DBBackupEncryptCert
   ),
  STATS = 5
GO

A picture (below) speaks a thousand words.

Filed under: Admin, Backup & Recovery, SQL SERVER, SQL Server 2017 Tagged: Admin, backups, compression, Encryption, SQL Server 2014, SQL Server 2017, TSQL