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.