Part of the DBA-Tools Project.
TDE (Transparent Data Encryption) and backup encryption are two different things that get confused constantly. TDE encrypts the data and log files at rest. It does not automatically encrypt your backups in a way msdb tracks as an encrypted backup set, and it definitely doesn’t help you if a .bak file gets copied off a fileshare that has no encryption of its own.
A database can have TDE on and still produce backup files that are just as readable as an unencrypted one if you use the wrong angle, or your compliance scope includes “backups in transit and at rest” and nobody actually checked whether the backup step itself uses encryption.
This script cross-references TDE state per database against what backupset actually recorded for the most recent full backup, and flags the mismatch: TDE without encrypted backups, encrypted backups without TDE, or neither.
Why Backup Encryption Status Matters
TDE and native backup encryption (BACKUP ... WITH ENCRYPTION) are separate SQL Server features that happen to both use the word encryption:
- TDE encrypts data and log files on disk. It’s transparent to applications and doesn’t require any change to backup or restore commands.
- Native backup encryption is applied at backup time, using a certificate or asymmetric key, and is recorded in
msdb.dbo.backupsetviakey_algorithmandencryptor_thumbprint. - A TDE-enabled database’s backup file is not automatically flagged as an encrypted backup set in
msdb, because that’s a different mechanism entirely. - Enabling TDE on any user database also encrypts tempdb, since tempdb can contain data from any database on the instance. That’s a side effect worth knowing about before you turn TDE on somewhere you didn’t expect a change.
For anyone answering a compliance question like “are backups encrypted,” the honest answer requires checking both mechanisms, not assuming one implies the other.
When to Run This Script
- Routine security and compliance reviews
- Before an audit that asks about encryption at rest and encryption of backup media
- After enabling TDE on any database, to confirm backups reflect the change you expect
- When backup files are stored or transmitted somewhere outside your normal, already-secured environment
The Script
Run the following script against your SQL Server instance.
/*
Script Name : Get-BackupEncryptionStatus
Category : backups
Purpose : Shows TDE status and backup encryption coverage per database. Identifies
databases where TDE is on but backups are not encrypted, or where neither
TDE nor backup encryption is used.
Author : Peter Whyte (https://sqldba.blog/dba-scripts-get-backup-encryption-status/)
Requires : VIEW DATABASE STATE; VIEW SERVER STATE for sys.dm_database_encryption_keys
*/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;
/*
DESIGN: Joins sys.databases → sys.dm_database_encryption_keys (TDE state) →
msdb.dbo.backupset (last 30 days). The key_algorithm and encryptor_thumbprint columns
in backupset are populated when backup encryption was used (SQL 2014+).
A database can have TDE without encrypted backups, or encrypted backups without TDE.
Neither is automatically wrong — the report surfaces the combination so you can assess.
*/
SELECT
d.name AS database_name,
d.recovery_model_desc,
CASE d.is_encrypted WHEN 1 THEN 'Yes' ELSE 'No' END
AS tde_enabled,
dek.encryption_state_desc AS tde_state,
dek.encryptor_type AS tde_encryptor_type,
-- Most recent full backup (last 30 days)
MAX(CASE bs.type WHEN 'D' THEN bs.backup_finish_date END)
AS last_full_backup,
-- Was the most recent full backup encrypted?
MAX(CASE bs.type WHEN 'D' THEN bs.key_algorithm END)
AS backup_encryption_algorithm,
MAX(CASE bs.type WHEN 'D'
THEN SUBSTRING(CONVERT(varchar(max), bs.encryptor_thumbprint, 2), 1, 16)
ELSE NULL END) AS backup_encryptor_thumbprint_prefix,
-- Assessment
CASE
WHEN d.is_encrypted = 1
AND MAX(CASE bs.type WHEN 'D' THEN bs.key_algorithm END) IS NOT NULL
THEN 'OK — TDE + encrypted backups'
WHEN d.is_encrypted = 1
AND MAX(CASE bs.type WHEN 'D' THEN bs.key_algorithm END) IS NULL
THEN 'WARN — TDE enabled but backups not encrypted'
WHEN d.is_encrypted = 0
AND MAX(CASE bs.type WHEN 'D' THEN bs.key_algorithm END) IS NOT NULL
THEN 'INFO — backup encrypted (no TDE on data files)'
WHEN MAX(CASE bs.type WHEN 'D' THEN bs.backup_finish_date END) IS NULL
THEN 'WARN — no full backup in last 30 days'
ELSE 'INFO — no encryption on TDE or backup'
END AS encryption_status
FROM sys.databases d
LEFT JOIN sys.dm_database_encryption_keys dek
ON dek.database_id = d.database_id
LEFT JOIN msdb.dbo.backupset bs
ON bs.database_name = d.name
AND bs.backup_finish_date >= DATEADD(DAY, -30, GETDATE())
WHERE d.database_id > 4
AND d.state_desc = 'ONLINE'
GROUP BY
d.name,
d.recovery_model_desc,
d.is_encrypted,
dek.encryption_state_desc,
dek.encryptor_type
ORDER BY
CASE
WHEN d.is_encrypted = 1
AND MAX(CASE bs.type WHEN 'D' THEN bs.key_algorithm END) IS NULL THEN 1
ELSE 2
END,
d.name;
The script joins each database’s TDE state against its most recent full backup in msdb, and flags any mismatch between the two.
How To Run From The Repo
Clone DBA Tools, initialize and run the script:
# Clone dba-tools repo:
git clone https://github.com/peterwhyte-lgtm/dba-tools
# Initialize environment:
cd dba-tools
.\Initialize-Environment.ps1
# Check TDE and backup encryption status across all user databases:
.\run.ps1 Get-BackupEncryptionStatus
# To run against a remote sql server:
.\run.ps1 Get-BackupEncryptionStatus -ServerInstance SQLSERVER01
This script lives in the repo at:
sql/backups/Get-BackupEncryptionStatus.sqlpowershell/wrappers/backups/Get-BackupEncryptionStatus.ps1
Example Output
| Database | Recovery Model | TDE Enabled | Backup Encryption | Status |
|---|---|---|---|---|
| GrowthLab | FULL | Yes (ENCRYPTED) | None | WARN: TDE enabled but backups not encrypted |
| DemoDatabase | FULL | No | None | INFO: no encryption on TDE or backup |
| RandomLab | FULL | No | aes_256 | INFO: backup encrypted (no TDE on data files) |
| WatchtowerMetrics | SIMPLE | Yes (ENCRYPTED) | aes_256 | OK: TDE + encrypted backups |
| 54 other databases | mostly FULL | No | None | WARN: no full backup in last 30 days |
Real output, captured against a local lab instance. Four databases here were deliberately set up to walk through every combination the script can find: TDE without backup encryption, backup encryption without TDE, both together, and neither. The remaining 54 databases fall back to the “no recent backup” warning, since this lab’s regular backup schedule is over a month stale, which is itself a genuine finding this script surfaces honestly rather than masking.
Understanding the Results
The encryption_status column is the summary. Everything else in the row supports it.
OK: TDE + encrypted backups. Both mechanisms are in place. This is the state to aim for if your compliance scope covers encryption at rest and encryption of backup media.
WARN: TDE enabled but backups not encrypted. The data files are encrypted, but the backup file coming off this database is not, unless something else outside SQL Server (disk-level encryption, an encrypted backup target) is covering it. If a .bak file leaves the server, it’s readable.
INFO: backup encrypted (no TDE on data files). The backup itself is encrypted, but the live data and log files are not. This is a legitimate configuration if TDE isn’t required for this database, but worth confirming it was a deliberate choice.
INFO: no encryption on TDE or backup. Neither mechanism is in use. Not automatically wrong for every database, but it’s the row that needs a real answer if this database is in scope for any encryption-at-rest requirement.
WARN: no full backup in last 30 days. The script can’t assess backup encryption because there’s nothing recent enough to check. Resolve the backup coverage gap first; encryption status is moot until a backup actually exists to evaluate.
Common Causes
- TDE turned on for compliance without anyone updating the backup process to match
- Backup encryption configured once, then the certificate or key quietly expired or was rotated without updating the backup job
- A database migrated or restored from a source where TDE was enabled, but the target’s backup jobs were never adjusted
- Compliance requirements changed after the original backup strategy was designed, and encryption was never revisited
How to Fix Missing Backup Encryption
If TDE is enabled but backups aren’t encrypted, add backup encryption to the job. It requires a certificate or asymmetric key protected by the database master key:
-- One-time setup: master key + certificate (skip if you already have one)
USE master;
IF NOT EXISTS (SELECT 1 FROM sys.symmetric_keys WHERE name = '##MS_DatabaseMasterKey##')
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<StrongPassword>';
CREATE CERTIFICATE BackupEncryptionCert
WITH SUBJECT = 'Backup encryption certificate';
-- Back this certificate up immediately and store it somewhere separate from the
-- database backups it protects. Without it, an encrypted backup is unrestorable.
BACKUP CERTIFICATE BackupEncryptionCert
TO FILE = 'D:\SQL-Backups\BackupEncryptionCert.cer'
WITH PRIVATE KEY (
FILE = 'D:\SQL-Backups\BackupEncryptionCert.pvk',
ENCRYPTION BY PASSWORD = '<AnotherStrongPassword>'
);
-- Then encrypt backups going forward:
BACKUP DATABASE [YourDatabase]
TO DISK = N'D:\SQL-Backups\YourDatabase_FULL.bak'
WITH COMPRESSION, CHECKSUM,
ENCRYPTION (ALGORITHM = AES_256, SERVER CERTIFICATE = BackupEncryptionCert);
If a database needs TDE and doesn’t have it, enabling it is a separate step (its own certificate, CREATE DATABASE ENCRYPTION KEY, then ALTER DATABASE ... SET ENCRYPTION ON), and worth its own careful change rather than bolting it on alongside a backup encryption fix.
The trap to avoid: losing the certificate or its private key. Without it, an encrypted backup cannot be restored, on this server or any other. Back up the certificate and private key the moment you create them, and store that backup somewhere separate from the database backups it protects.
Best Practices
- Treat TDE and backup encryption as two separate checkboxes, not one
- Back up every certificate and its private key immediately after creation, before you rely on it for anything
- Store certificate backups separately from the database backups they protect
- Re-verify encryption status after any migration or restore, since certificates don’t always travel with the database automatically
- Include this check in routine security reviews, not just at audit time
Related Scripts
You may also find these scripts useful:
- Backup Chain Integrity
- Backup Coverage
- Backup Restore Duration Estimate
- Backup Size Trend
- Database Backup History
- Last Database Backup Times
- Backup and Restore Progress
Frequently Asked Questions
Does TDE encrypt my SQL Server backups?
Not in a way msdb tracks as an encrypted backup set. TDE encrypts the data and log files, and a backup taken from a TDE-enabled database inherits that encryption, but native backup encryption is a separate feature with its own certificate and its own record in backupset.
Does enabling TDE affect tempdb?
Yes. Once TDE is enabled on any user database, tempdb is automatically encrypted too, since it can contain data from every database on the instance. This happens without any extra configuration and often surprises people who only intended to encrypt one database.
Do I need both TDE and backup encryption?
It depends on your compliance requirements. Some frameworks only require encryption at rest for live data (TDE covers that). Others explicitly require backup media to be encrypted too, which TDE alone doesn’t provide. Check what’s actually being asked for before assuming one covers the other.
What happens if I lose the certificate used for backup encryption?
The backup becomes permanently unrestorable. There’s no recovery path without the certificate and its private key. Back both up immediately after creation and store that backup separately from the data it protects.
Summary
TDE and backup encryption answer different questions, and treating them as interchangeable is how a database ends up with encrypted data files and a completely readable backup file sitting on a share somewhere. This script checks both at once and tells you exactly which combination each database actually has.
Run it as part of routine security reviews, and treat any WARN as a real finding, not a formality. A database in the wrong state here usually got there because encryption was configured for one requirement and nobody circled back to check whether it also satisfied the next one.
Leave a Reply