Backup & Recovery from First Principles, Part 10: Backup Encryption and TDE – The Gotchas That Cost You the Restore
Every part of this series so far assumed one thing: if you have the backup file, you can restore it. Encryption changes that deal. An encrypted backup (or any backup of a TDE database) is restorable only by someone holding the right certificate – which is the point – and that someone includes future-you, on a different server, at 3 AM, after the original server is a smoking crater. This final part demonstrates the failure mode and the key-management habits that prevent it.
This is part 10 of the backup and recovery series (part 1). Demo ran on SQL Server 2025 (RTM, 17.0.1125.2), Developer Edition.
Two Different Features, One Failure Mode
- Backup encryption (
BACKUP ... WITH ENCRYPTION) encrypts the backup file. The database itself is plaintext on disk.[1] - TDE (Transparent Data Encryption) encrypts the database’s data and log files at rest; backups of a TDE database come out encrypted as a side effect.[2]
Both hang off the same key hierarchy: Service Master Key -> master’s Database Master Key -> a certificate -> (for TDE) the Database Encryption Key. And both share one failure mode: the restore needs the certificate, and the certificate does not live in the backup file.
The Demo: Encrypt, Lose the Cert, Fail the Restore
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
USE [master]; CREATE MASTER KEY ENCRYPTION BY PASSWORD = N'Demo-0nly-Passw0rd!'; CREATE CERTIFICATE [bkrec_backup_cert] WITH SUBJECT = N'bkrec demo backup certificate'; /* the encrypted backup */ BACKUP DATABASE [bkrec_demo] TO DISK = N'C:\temp\bkrec\enc_full.bak' WITH INIT , COMPRESSION , ENCRYPTION (ALGORITHM = AES_256, SERVER CERTIFICATE = [bkrec_backup_cert]); |
SQL Server starts lecturing immediately, and every word is true:
|
1 2 3 4 5 6 |
Warning: The certificate used for encrypting the database encryption key has not been backed up. You should immediately back up the certificate and the private key associated with the certificate. If the certificate ever becomes unavailable or if you must restore or attach the database on another server, you must have backups of both the certificate and the private key or you will not be able to open the database. BACKUP DATABASE successfully processed 394 pages in 0.017 seconds (180.836 MB/sec). |
Now simulate the 3 AM scenario: the original server is gone, and we restore on a replacement instance that has never seen bkrec_backup_cert. (In the demo: drop the certificate.)
|
1 2 3 4 5 6 7 8 9 |
DROP CERTIFICATE [bkrec_backup_cert]; RESTORE HEADERONLY FROM DISK = N'C:\temp\bkrec\enc_full.bak'; RESTORE DATABASE [bkrec_demo_enc] FROM DISK = N'C:\temp\bkrec\enc_full.bak' WITH MOVE N'bkrec_demo' TO N'C:\temp\bkrec\bkrec_enc.mdf' , MOVE N'bkrec_demo_log' TO N'C:\temp\bkrec\bkrec_enc.ldf'; |
|
1 2 3 4 5 6 7 8 |
Msg 33111, Level 16, State 3 Cannot find server certificate with thumbprint '0xB7BA7C20FA792D9FFDE21635EB7C6A5BFDD311FA'. Msg 3013, Level 16, State 1 RESTORE HEADERONLY is terminating abnormally. Msg 33111, Level 16, State 3 Cannot find server certificate with thumbprint '0xB7BA7C20FA792D9FFDE21635EB7C6A5BFDD311FA'. Msg 3013, Level 16, State 1 RESTORE DATABASE is terminating abnormally. |
Note that even RESTORE HEADERONLY fails – without the certificate you cannot so much as read the backup’s header, let alone its contents. The backup file is intact, verified, and utterly useless. No certificate, no restore, no exceptions, no support escalation that ends differently. The related “certificate private key not present” error is the same lesson wearing different clothes: even having the certificate is not enough if its private key did not come along.
The Recovery: Restore the Certificate First
The fix exists only if you did the key backup at creation time – the step that warning was demanding:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
/* THE STEP THAT MATTERS - do this the moment the certificate is created */ BACKUP CERTIFICATE [bkrec_backup_cert] TO FILE = N'C:\temp\bkrec\bkrec_cert.cer' WITH PRIVATE KEY ( FILE = N'C:\temp\bkrec\bkrec_cert.pvk' , ENCRYPTION BY PASSWORD = N'Demo-0nly-Pvk-Passw0rd!' ); /* on the replacement server */ CREATE CERTIFICATE [bkrec_backup_cert] FROM FILE = N'C:\temp\bkrec\bkrec_cert.cer' WITH PRIVATE KEY ( FILE = N'C:\temp\bkrec\bkrec_cert.pvk' , DECRYPTION BY PASSWORD = N'Demo-0nly-Pvk-Passw0rd!' ); RESTORE DATABASE [bkrec_demo_enc] FROM DISK = N'C:\temp\bkrec\enc_full.bak' WITH MOVE N'bkrec_demo' TO N'C:\temp\bkrec\bkrec_enc.mdf' , MOVE N'bkrec_demo_log' TO N'C:\temp\bkrec\bkrec_enc.ldf'; |
|
1 |
RESTORE DATABASE successfully processed 394 pages in 0.015 seconds (204.947 MB/sec). |
(In production, store the .cer/.pvk files and password somewhere far more protected than the demo path used here, and use a real password.)
Everything in this series – chains, sequences, verification – now has a third artifact class beside data backups and log backups: key material. It needs its own backup, its own (offline, access-controlled) storage, and its own restore rehearsal. Where you store it matters as much as whether: filesystem security for TDE keys and certificate backups covers locking those .cer/.pvk files down.
TDE-Specific Gotchas
- Every backup of a TDE database is an encrypted backup. Nobody has to type WITH ENCRYPTION; the dependency on the certificate is acquired the moment TDE is enabled, and it applies retroactively to your restore plans for every environment that receives prod backups (dev refresh servers need the cert too – or better, restore-then-rotate).
- Compression and TDE interact. TDE-encrypted pages do not compress (encrypted data has no patterns), so pre-2016 a compressed backup of a TDE database was pointless. From SQL Server 2016, specifying
MAXTRANSFERSIZE > 65536on the BACKUP command enables an optimized path that decrypts, compresses, and re-encrypts, restoring the compression ratios from part 9. - The tail-log backup needs the cert too. The disaster runbook from part 3 starts with a tail-log backup – of a TDE database, that file is encrypted like every other. A DR runbook for a TDE database must restore the certificate before step one of the restore sequence.
- Certificate expiry warnings are cosmetic for TDE (SQL Server ignores expiry for TDE certificates), but rotation still matters for compliance, and every rotation adds a certificate that historical backups depend on. Retire a certificate only when every backup encrypted under it has aged out of retention.
Series Wrap-Up
Ten parts, one thesis: a backup strategy is really a restore strategy, designed backward from the outages you intend to survive. Recovery models set what is possible; chains make points-in-time reachable; copy-only keeps ad-hoc needs from sabotaging the plan; restore sequences, piecemeal, and page restore are the escalating toolkit; verification keeps everyone honest; tuning makes the window fit; and key management makes sure encryption protects you from attackers instead of from yourself. For where backups fit in the larger availability picture, see the HA/DR options field guide and the three-layer backup strategy.
Thanks for reading along. One more thing before you go: the certificate demo above assumed the top of the key hierarchy was healthy. Bonus part 11 covers what happens when it is not – backing up the Service Master Key and Database Master Keys, with a war story. What did the series miss? Comments below, or find me on Bluesky or LinkedIn.
References
- Backup Encryption – Microsoft Learn. Encrypted backup requirements, algorithms, and certificate dependency. ↩
- Transparent Data Encryption (TDE) – Microsoft Learn. TDE key hierarchy, certificate backup requirements, and restore dependencies. ↩