T-SQL SQL Server Backup Report: Last Full, Differential, and Log Backup Details

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;