SQL Server Backup Verification

Why this problem matters

A successful backup job alone does not prove that its file will be readable and usable when needed. Missing chains, inaccessible destinations, or unverified files increase recovery risk.

How to diagnose it

Review the latest full, differential, and log backup times by database from msdb history. Store results from checks such as RESTORE VERIFYONLY in separate automation records and distinguish them from actual restore tests.

The query reads msdb backup history and does not verify that a file exists or is readable. History may have been purged, and database renames can affect interpretation.

SELECT d.name,
       MAX(CASE WHEN bs.type = 'D' THEN bs.backup_finish_date END) AS last_full_backup,
       MAX(CASE WHEN bs.type = 'I' THEN bs.backup_finish_date END) AS last_diff_backup,
       MAX(CASE WHEN bs.type = 'L' THEN bs.backup_finish_date END) AS last_log_backup
FROM sys.databases AS d
LEFT JOIN msdb.dbo.backupset AS bs
  ON bs.database_name = d.name
 AND bs.is_copy_only = 0
GROUP BY d.name
ORDER BY d.name;

A safe solution approach

The backup policy must align with recovery-point and recovery-time objectives. Automate file validation, protect encryption keys, and schedule regular restore tests.

Why continuous monitoring matters

One missed log backup can affect the recovery chain and intended data-loss window. moon can monitor backup age and job outcomes and notify the responsible team through Slack, PagerDuty, or webhooks.