SQL Server Backup History Report

• This script provides a detailed history of database backups in SQL Server. It is important because it allows database administrators (DBAs) to:
• Verify that backups are being performed regularly.
• Check backup duration and size to monitor performance.
• Compare normal vs. compressed backup sizes to analyze storage efficiency.
• Identify the type of backup (Full, Differential, Log, etc.) for proper restore planning.
• Ensure restore chain integrity using Log Sequence Numbers (LSNs).
• Quickly troubleshoot missing or failed backups.

SELECT
    bs.database_name AS [Database Name],
    bs.backup_start_date AS [Backup Start],
    bs.backup_finish_date AS [Backup Finished],
    DATEDIFF(MINUTE, bs.backup_start_date, bs.backup_finish_date) AS [Duration (Minutes)],
    bmf.physical_device_name AS [Backup File],
    CAST(bs.first_lsn AS VARCHAR(50)) AS [First LSN],
    CAST(bs.last_lsn AS VARCHAR(50)) AS [Last LSN],
    CASE 
        WHEN bs.[type] = 'D' THEN 'Full Backup' 
        WHEN bs.[type] = 'I' THEN 'Differential Backup' 
        WHEN bs.[type] = 'L' THEN 'Log Backup' 
        WHEN bs.[type] = 'F' THEN 'File/Filegroup Backup' 
        WHEN bs.[type] = 'G' THEN 'Differential File Backup'
        WHEN bs.[type] = 'P' THEN 'Partial Backup'  
        WHEN bs.[type] = 'Q' THEN 'Differential Partial Backup' 
    END AS [Backup Type],
    CAST(CAST(bs.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' MB' AS [Backup Size (MB)],
    CAST(CAST(bs.compressed_backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' MB' AS [Compressed Backup Size (MB)],
    bs.recovery_model AS [Recovery Model]
FROM 
    msdb..backupmediafamily bmf
INNER JOIN 
    msdb..backupset bs 
    ON bmf.media_set_id = bs.media_set_id
-- Uncomment to filter by specific database
-- WHERE bs.database_name = 'YourDatabaseName'
ORDER BY 
    bs.backup_start_date DESC;

Column Description
Database Name Name of the database being backed up.
Backup Start / Backup Finished Start and end timestamps of the backup process.
Duration (Minutes) Total time taken to complete the backup (in minutes).
Backup File Physical file path of the backup file stored on disk.
First LSN / Last LSN Log Sequence Numbers used to track backup chains (crucial for restores).
Backup Type Type of backup (Full, Differential, Log, Filegroup, etc.).
Backup Size (MB) Total size of the backup before compression.
Compressed Backup Size (MB) Actual size of the backup file if compression was used.
Recovery Model Recovery model of the database at backup time (Full, Bulk-Logged, or Simple).