Monitoring your SQL Server backups is crucial for disaster recovery planning.
Below is a powerful SQL query that retrieves the last full, differential, and log backup details for each database.
SELECT
db.name AS DatabaseName,
-- Last Full Backup Details
MAX(CASE WHEN bs.type = 'D' THEN bs.backup_finish_date END) AS LastFullBackupTime,
MAX(CASE WHEN bs.type = 'D' THEN bmf.physical_device_name END) AS FullBackupLocation,
-- Last Differential Backup Details
MAX(CASE WHEN bs.type = 'I' THEN bs.backup_finish_date END) AS LastDifferentialBackupTime,
MAX(CASE WHEN bs.type = 'I' THEN bmf.physical_device_name END) AS DifferentialBackupLocation,
-- Last Log Backup Details
MAX(CASE WHEN bs.type = 'L' THEN bs.backup_finish_date END) AS LastLogBackupTime,
MAX(CASE WHEN bs.type = 'L' THEN bmf.physical_device_name END) AS LogBackupLocation,
-- Backup Status
CASE
WHEN MAX(CASE WHEN bs.type = 'D' THEN bs.backup_finish_date END) IS NULL THEN 'No Full Backup Found'
ELSE 'Full Backup Available'
END AS FullBackupStatus,
CASE
WHEN MAX(CASE WHEN bs.type = 'I' THEN bs.backup_finish_date END) IS NULL THEN 'No Differential Backup Found'
ELSE 'Differential Backup Available'
END AS DifferentialBackupStatus,
CASE
WHEN MAX(CASE WHEN bs.type = 'L' THEN bs.backup_finish_date END) IS NULL THEN 'No Log Backup Found'
ELSE 'Log Backup Available'
END AS LogBackupStatus
FROM msdb.dbo.backupset bs
JOIN msdb.dbo.backupmediafamily bmf ON bs.media_set_id = bmf.media_set_id
JOIN sys.databases db ON bs.database_name = db.name
GROUP BY db.name
ORDER BY db.name;