Verify last successful backups in one query

Never trust a backup job’s green checkmark alone. Confirm data in msdb.

SELECT
    d.name AS database_name,
    d.recovery_model_desc,
    MAX(CASE WHEN b.type = 'D' THEN b.backup_finish_date END) AS last_full,
    MAX(CASE WHEN b.type = 'I' THEN b.backup_finish_date END) AS last_diff,
    MAX(CASE WHEN b.type = 'L' THEN b.backup_finish_date END) AS last_log
FROM sys.databases AS d
LEFT JOIN msdb.dbo.backupset AS b
    ON b.database_name = d.name
    AND b.is_copy_only = 0
WHERE d.name <> 'tempdb'
GROUP BY d.name, d.recovery_model_desc
ORDER BY d.name;

Tip: For Full recovery databases, last_log going stale is as serious as a missing full backup.

Wire this into a daily check or monitoring tool, and alert when last full/log exceeds your RPO thresholds.