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.
Pingback: Backup Compression And Encryption – Curated SQL
Can you provide the timings, how long each too, for the final three backups in your sample too. Thanks
Can you provide the timings, how long each backup took, for the three backups in your final example too. (Sorry about the typo above)
actually, you first need to create a database master key in the master database if it doesn’t exist already (which it likely won’t)
Also, when you create the certificate, set the authorization
and provide a start and end date.
Backup the certificate first before you do anything else.
If moving to a new server, don’t forget to create a DMK there first before restoring the certificate.
For more, see my article at this link
Is compression available along with encryption on Standard edition?
yes it is for the newer versions , never used to be cant remember when MS made that decision though