Test-SqlTdeConfiguration

This is the check that answers “which databases are encrypted, and is every certificate that protects them safely backed up?” — without changing anything. It reads the server’s own records and reports what it finds.

The part that matters most is the last one: a certificate that protects encrypted databases but has never had its private key backed up is the single failure that leaves those databases unrecoverable if the server is lost. This command calls that out by name, so you can back up the exposed certificate before it becomes a problem.

Reads three things from the instance’s catalog views: whether a database master key exists in master, which databases are TDE-encrypted and by which certificate, and each TDE certificate’s private-key backup date (pvt_key_last_backup_date). It normalises the certificate thumbprints, resolves each encrypted database’s key to its certificate, and marks each certificate as escrowed or not from that backup date. It flags any TDE certificate whose private key has never been backed up. Read-only — no ShouldProcess, changes nothing. Provider access is the shared read seam (dbatools or SqlServer module), so it is subject to the same optional-dependency probe. Never throws; a read error returns a Failed result with the reason. -SqlInstance defaults to localhost. Data keys: MasterKeyPresent, EncryptedDatabases (Database / EncryptionState / EncryptorThumbprint / EncryptorType / EncryptorCertName), TdeCertificates (Name / Thumbprint / Subject / ExpiryDate / PrivateKeyLastBackup / PrivateKeyBackedUp), UnescrowedCertificateCount.

Recipe 01 — Report the TDE state of an instance

Test-SqlTdeConfiguration -SqlInstance sql01

Reports whether sql01 has a database master key, which databases are TDE-encrypted, and whether every TDE certificate’s private key is backed up. Safe to run anywhere — it changes nothing.

Recipe 02 — List the certificates whose private key was never escrowed

(Test-SqlTdeConfiguration -SqlInstance sql01).Data.TdeCertificates |
    Where-Object { -not $_.PrivateKeyBackedUp }

These are the certificates to back up immediately with Backup-SqlTdeCertificate.

Watchpoint — this reports intent, and flags the unrecoverable case. It reads the server’s records rather than testing a restore, so a certificate marked as backed up reflects a recorded backup date. UnescrowedCertificateCount and the flagged certificate names point at the certificates that would make an encrypted database unrecoverable if the server were lost.

This command looks but never touches. It reads three facts from SQL Server: whether the master key that all of this depends on exists, which databases are encrypted, and — for each certificate doing the encrypting — whether its private key has ever been backed up.

That last fact is the one worth acting on. An encrypted database can only be recovered with the certificate that protects it, and that certificate can only be recovered if its private key was backed up. If this command reports a certificate with no private-key backup, that database is one server failure away from being unreadable. Running this is the safe first step before you change anything.

• A non-mutating way to inventory TDE posture across an estate — safe for a compliance sweep, since it changes nothing.
• Flags every TDE certificate whose private key has no recorded backup, surfacing the single unrecoverable TDE failure before it happens.
• Returns structured per-certificate and per-database rows, including the private-key backup date, as evidence for an audit record.
• Never throws — a read error is reported as a Failed result with the reason, not an unhandled error.