Test-SqlBackupEncryptionReadiness

This is the check that answers “if I had to restore my encrypted backups on this server right now, could I?” — without changing anything. It looks at the backups this server has taken, finds the certificate each one was encrypted with, and reports whether that certificate is still here and safely backed up.

Two situations it surfaces are the ones that only otherwise show up at the worst moment: a certificate that encrypted backups but is no longer on this server, which means those backups cannot be restored here; and a certificate that is here but whose private key was never backed up, which is a loss waiting to happen. It can also check a single backup file you point it at, which is how you confirm a specific backup carried to a fresh recovery server can actually be restored there.

In its default mode it reads msdb backup history for backups encrypted by a certificate and, for each distinct certificate, reports whether that certificate is present in master on this server and whether its private key has been backed up, plus the dependent databases and backup count. It uses a LEFT JOIN to master.sys.certificates, so a certificate that encrypted backups but is absent from this server still surfaces. With -BackupPath, it instead reads one backup file’s header (RESTORE HEADERONLY) for the encrypting certificate’s thumbprint and reports whether that certificate is present here — the file-based check that works on a server that never took the backup and so has no local history. It audits only; it does not create, escrow, or restore. Read-only, never throws; a read error returns Failed. -SqlInstance defaults to localhost. Data keys (history mode): Certificates (Thumbprint / CertName / PresentOnServer / PrivateKeyBackedUp / Databases / BackupCount). Data keys (-BackupPath mode): BackupPath, Encrypted, EncryptorThumbprint, EncryptorType, PresentOnServer, RestorableHere, CertName.

Recipe 01 — Audit every certificate behind this server’s encrypted backups

Test-SqlBackupEncryptionReadiness -SqlInstance sql01

Lists every certificate that has encrypted a backup on sql01 and whether each is present and escrowed — i.e. whether those encrypted backups are restorable here.

Recipe 02 — On a DR server, list the certificates not yet present

(Test-SqlBackupEncryptionReadiness -SqlInstance dr-sql).Data.Certificates |
    Where-Object { -not $_.PresentOnServer }

These are the certificates to bring over with Restore-SqlTdeCertificate before their encrypted backups can be restored on dr-sql.

Recipe 03 — Check whether one backup file is restorable here

Test-SqlBackupEncryptionReadiness -SqlInstance dr-sql -BackupPath '\\share\move\payroll.bak'

Reads the file’s header and reports whether the certificate that encrypted it is present on dr-sql — the DR-server question that backup history cannot answer for a file carried in from elsewhere.

Watchpoint — backup history is local to the server that took the backup. A backup carried to a fresh server has no row in that server’s msdb history, so the default mode cannot see it. Use -BackupPath to check the file itself. When a certificate-encrypted file’s certificate is absent, SQL Server names the required thumbprint in its error; the command reports that as “not restorable here” rather than failing.

An encrypted backup can only be restored on a server that holds the certificate it was encrypted with. This command answers, ahead of time, whether that is true here — so you find out before a real restore, not during one.

It has two ways of looking. By default it reads this server’s own history of backups it has taken and checks each encrypting certificate. But that history only lives on the server that made the backups; a backup file moved to a new recovery server has no history there. For that case you point the command at the file with -BackupPath, and it reads the file’s own header to find the certificate it needs and checks whether that certificate is present. It only reports; recovering an absent certificate is done separately with Restore-SqlTdeCertificate.

• Answers whether certificate-encrypted backups are restorable on a given server before a real restore has to, turning a latent recovery gap into a report row.
• The LEFT JOIN surfaces certificates that encrypted backups but are absent from this server — the case that would otherwise make those backups unrestorable here — and flags present-but-un-escrowed certificates as a latent loss.
• The -BackupPath mode checks a specific file’s header, so a backup carried to a fresh recovery server can be assessed without any local history.
• Read-only and never-throw — it audits and does not create, escrow, or restore; a read error is reported as a Failed result.