The last full, differential and log backup of every database, hours since the last full, and the last 7 days of backup history. Part of my SQL Server health check: run Health Check 00 for a one-page summary, then the numbered scripts for the detail behind each area. Read-only, changes nothing. Tested on SQL Server 2017 and 2025.

-- 03_Backups.sql - last full, differential and log backup of every database, and the last 7 days' history. Read-only.
SET NOCOUNT ON;
SELECT d.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,
       DATEDIFF(HOUR, MAX(CASE WHEN b.type = 'D' THEN b.backup_finish_date END), GETDATE()) AS hours_since_full
FROM sys.databases AS d LEFT JOIN msdb.dbo.backupset AS b ON b.database_name = d.name
WHERE d.database_id <> 2
GROUP BY d.name, d.recovery_model_desc
ORDER BY hours_since_full DESC, d.name;

SELECT b.database_name, CASE b.type WHEN 'D' THEN 'FULL' WHEN 'I' THEN 'DIFF' WHEN 'L' THEN 'LOG' ELSE b.type END AS type,
       b.backup_start_date, b.backup_finish_date, DATEDIFF(SECOND, b.backup_start_date, b.backup_finish_date) AS seconds,
       CONVERT(decimal(12,1), b.compressed_backup_size / 1048576.0) AS size_mb, b.is_copy_only, b.has_backup_checksums,
       mf.physical_device_name
FROM msdb.dbo.backupset AS b JOIN msdb.dbo.backupmediafamily AS mf ON mf.media_set_id = b.media_set_id
WHERE b.backup_start_date > DATEADD(DAY, -7, GETDATE())
ORDER BY b.backup_start_date DESC;