Public/Test-SqlBackupEncryptionReadiness.ps1
|
# ============================================================================= # Script : Public/Test-SqlBackupEncryptionReadiness.ps1 # Author : Keith Ramsey # Created : 2026-09-08 # ============================================================================= # Change Log # ----------------------------------------------------------------------------- # 2026-09-08 Keith Ramsey Initial (B2, GR-004, DR-036): read-only audit tying # certificate-encrypted backups in msdb history to # their certificate's present/escrowed state on this # server -- "can I still restore my encrypted backups # here?" Reuses the read seam; never-throw. Lifecycle # (create/escrow/restore) is B1's, not duplicated. # ============================================================================= # Decision Contract (Docs/DECISIONS_PHASE7.md DR-036) # ----------------------------------------------------------------------------- # Must : read-only; LEFT JOIN so an encryptor cert absent from this server still # surfaces; per-certificate present + escrowed (pvt_key_last_backup_date) # + dependent databases/backup counts; one never-throw SqlCert.Result; # does NOT create/escrow/restore (B1 does, on any master cert). # ============================================================================= function Test-SqlBackupEncryptionReadiness { <# .SYNOPSIS Reports whether certificate-encrypted backups can still be restored on this server (B2). .DESCRIPTION Reads msdb backup history for every backup encrypted by a certificate and, for each distinct encrypting certificate, reports whether that certificate is present in master on THIS server and whether its private key has been backed up (escrowed), plus which databases and how many backups depend on it. This answers the question that only surfaces at the worst possible moment otherwise: can I actually restore my encrypted backups here? A certificate that encrypted backups but is absent from this server means those backups cannot be restored here; a present-but-un-escrowed certificate is a latent loss. Read-only and never-throw. This audits; it does not fix. To recover an absent certificate on a DR/replacement server use Restore-SqlTdeCertificate, and to escrow a present one use Backup-SqlTdeCertificate -- both operate on any master certificate, including a backup-encryption certificate. .PARAMETER SqlInstance The instance to audit. Default 'localhost'. .PARAMETER BackupPath Optional. A specific backup FILE to check restorability for on THIS server. Reads the backup header (RESTORE HEADERONLY) for the encrypting certificate's thumbprint and reports whether that certificate is present here -- i.e. "can I restore THIS backup on THIS server?" This is the DR-server question the msdb-history mode cannot answer: history is local to the server that took the backup, so a backup carried to a fresh server has no local history. With -BackupPath the check is against the file itself, so it works on a server that never took the backup. The SQL service must be able to read the file. .PARAMETER Credential SQL or Windows credential for the connection. .OUTPUTS SqlCert.Result Data key (history mode): Certificates = @(Thumbprint, CertName, PresentOnServer, PrivateKeyBackedUp, Databases, BackupCount). Data keys (-BackupPath mode): BackupPath, Encrypted, EncryptorThumbprint, EncryptorType, PresentOnServer, RestorableHere, CertName. .EXAMPLE 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. .EXAMPLE (Test-SqlBackupEncryptionReadiness -SqlInstance dr-sql).Data.Certificates | Where-Object { -not $_.PresentOnServer } On a DR server, lists the certificates whose encrypted backups could NOT be restored there yet -- the certificates to bring over with Restore-SqlTdeCertificate. .EXAMPLE Test-SqlBackupEncryptionReadiness -SqlInstance dr-sql -BackupPath '\\share\move\payroll.bak' On a fresh DR server, reads the backup file's header and reports whether the certificate that encrypted it is present on dr-sql -- i.e. whether that specific backup is restorable there. .NOTES Steps: 0. If -BackupPath was supplied, read the backup header (RESTORE HEADERONLY) for the encryptor thumbprint/type; report whether a certificate with that thumbprint is present here (restorable-here) -- the file-based DR check that does not depend on local backup history. Return. 1. Otherwise read msdb.dbo.backupset for certificate-encrypted backups, LEFT JOINed to master.sys.certificates (so an absent encryptor cert still surfaces), via the shared read seam. 2. No such backups -> Success with an empty certificate set. 3. Group by certificate thumbprint (varbinary -> hex): present-on-server, escrowed (pvt_key_last_backup_date), dependent databases, backup count. 4. Return a Success SqlCert.Result; the Detail flags absent certs (not restorable here) and un-escrowed certs. Any read error -> Failed. Never throws. #> [CmdletBinding()] [OutputType('SqlCert.Result')] param( [string] $SqlInstance = 'localhost', [string] $BackupPath, [PSCredential] $Credential ) $credSplat = @{} if ($Credential) { $credSplat.Credential = $Credential } $hexOf = { param($v) if ($v -is [byte[]]) { ([System.BitConverter]::ToString([byte[]]$v) -replace '-', '') } else { [string]$v } } $query = @' SELECT bs.encryptor_thumbprint AS Thumbprint, bs.database_name AS DatabaseName, c.name AS CertName, CASE WHEN c.thumbprint IS NULL THEN 0 ELSE 1 END AS PresentOnServer, c.pvt_key_last_backup_date AS PvtKeyLastBackup FROM msdb.dbo.backupset AS bs LEFT JOIN master.sys.certificates AS c ON c.thumbprint = bs.encryptor_thumbprint WHERE bs.encryptor_type LIKE 'CERTIFICATE%' AND bs.encryptor_thumbprint IS NOT NULL; -- 'CERTIFICATE' or SQL 2025's 'CERTIFICATE_OAEP_256' '@ try { # 0. File-based check: does THIS server hold the cert needed to restore THIS backup? # (msdb history is local to the server that took the backup, so a backup carried to a # fresh DR server has no local row -- read the file header instead.) if ($BackupPath) { $pathLit = $BackupPath -replace "'", "''" # SQL refuses to read the header of a certificate-encrypted backup when the encrypting # certificate is absent -- but the error names the required thumbprint. That IS the # not-restorable-here answer, so catch it and report rather than failing. try { $hdr = @(Invoke-SqlCertInventoryQuery -SqlInstance $SqlInstance -Database 'master' @credSplat ` -Query "RESTORE HEADERONLY FROM DISK = N'$pathLit';") } catch { if ("$($_.Exception.Message)" -match "server certificate with thumbprint '0x([0-9A-Fa-f]+)'") { $needTp = $Matches[1] return New-SqlCertResult -Stage Readiness -Status Success ` -Detail "Backup '$BackupPath' is encrypted by a certificate (thumbprint $needTp) that is NOT present on '$SqlInstance' -- NOT restorable here until you recover it with Restore-SqlTdeCertificate." ` -Data ([pscustomobject]@{ BackupPath = $BackupPath; Encrypted = $true; EncryptorThumbprint = $needTp; EncryptorType = 'CERTIFICATE'; PresentOnServer = $false; RestorableHere = $false; CertName = $null }) } throw } if ($hdr.Count -eq 0) { return New-SqlCertResult -Stage Readiness -Status Failed ` -Detail "Could not read a backup header from '$BackupPath' on '$SqlInstance'." ` -Data ([pscustomobject]@{ BackupPath = $BackupPath }) } $etp = & $hexOf $hdr[0].EncryptorThumbprint $etype = [string]$hdr[0].EncryptorType if ([string]::IsNullOrEmpty($etp)) { return New-SqlCertResult -Stage Readiness -Status Success ` -Detail "Backup '$BackupPath' is not certificate-encrypted -- no encryptor certificate is required to restore it." ` -Data ([pscustomobject]@{ BackupPath = $BackupPath; Encrypted = $false; RestorableHere = $true }) } $pres = @(Invoke-SqlCertInventoryQuery -SqlInstance $SqlInstance -Database 'master' @credSplat ` -Query "SELECT name AS CertName FROM master.sys.certificates WHERE thumbprint = 0x$etp;") $present = ($pres.Count -ge 1) $certName = if ($present) { [string]$pres[0].CertName } else { $null } $detail = if ($present) { "Backup '$BackupPath' is encrypted by certificate '$certName' (thumbprint $etp), which IS present on '$SqlInstance' -- restorable here." } else { "Backup '$BackupPath' is encrypted by a certificate (thumbprint $etp) that is NOT present on '$SqlInstance' -- NOT restorable here until you recover it with Restore-SqlTdeCertificate." } return New-SqlCertResult -Stage Readiness -Status Success -Detail $detail ` -Data ([pscustomobject]@{ BackupPath = $BackupPath; Encrypted = $true; EncryptorThumbprint = $etp; EncryptorType = $etype; PresentOnServer = $present; RestorableHere = $present; CertName = $certName }) } # 1. Read certificate-encrypted backups (absent encryptor certs surface via LEFT JOIN). $rows = @(Invoke-SqlCertInventoryQuery -SqlInstance $SqlInstance -Database 'master' -Query $query @credSplat) # 2. None. if ($rows.Count -eq 0) { return New-SqlCertResult -Stage Readiness -Status Success ` -Detail "No certificate-encrypted backup in msdb history on '$SqlInstance'." ` -Data ([pscustomobject]@{ Certificates = @() }) } # 3. Group by certificate thumbprint. $groups = [ordered]@{} foreach ($r in $rows) { $tp = & $hexOf $r.Thumbprint if (-not $groups.Contains($tp)) { $groups[$tp] = [pscustomobject]@{ Thumbprint = $tp CertName = [string]$r.CertName PresentOnServer = ([int]$r.PresentOnServer -eq 1) PrivateKeyBackedUp = ($r.PvtKeyLastBackup -is [datetime]) Databases = [System.Collections.Generic.List[string]]::new() BackupCount = 0 } } $g = $groups[$tp] $g.BackupCount++ $db = [string]$r.DatabaseName if ($db -and -not $g.Databases.Contains($db)) { $g.Databases.Add($db) } } $certs = @(foreach ($g in $groups.Values) { [pscustomobject]@{ Thumbprint = $g.Thumbprint CertName = $g.CertName PresentOnServer = $g.PresentOnServer PrivateKeyBackedUp = $g.PrivateKeyBackedUp Databases = @($g.Databases) BackupCount = $g.BackupCount } }) # 4. Assess + report. $absent = @($certs | Where-Object { -not $_.PresentOnServer }) $unescrowed = @($certs | Where-Object { $_.PresentOnServer -and -not $_.PrivateKeyBackedUp }) $detail = "$(@($certs).Count) certificate(s) protect certificate-encrypted backups on '$SqlInstance'." if ($absent.Count -gt 0) { $detail += " $($absent.Count) NOT present on this server -- those backups cannot be restored here (recover with Restore-SqlTdeCertificate)." } if ($unescrowed.Count -gt 0) { $detail += " $($unescrowed.Count) present but with NO private-key backup -- escrow with Backup-SqlTdeCertificate." } if ($absent.Count -eq 0 -and $unescrowed.Count -eq 0) { $detail += " All present and escrowed -- restorable here." } New-SqlCertResult -Stage Readiness -Status Success -Detail $detail ` -Data ([pscustomobject]@{ Certificates = $certs }) } catch { New-SqlCertResult -Stage Readiness -Status Failed ` -Detail "Test-SqlBackupEncryptionReadiness could not read backup history on '$SqlInstance': $($_.Exception.Message)" ` -Data ([pscustomobject]@{ Certificates = @() }) } } |