Data Collection

IF OBJECT_ID('dbo.BackupMonitoringHistory') IS NULL
BEGIN
    CREATE TABLE dbo.BackupMonitoringHistory
    (
        BackupID BIGINT IDENTITY(1,1) PRIMARY KEY,
        DatabaseName SYSNAME,
        BackupStartDate DATETIME,
        BackupFinishDate DATETIME,
        BackupType VARCHAR(20),
        DurationSeconds INT,
        BackupSizeMB DECIMAL(18,2),
        CompressedBackupSizeMB DECIMAL(18,2),
        ChecksumStatus BIT,
        LastLSN NUMERIC(25,0),
        FirstLSN NUMERIC(25,0),
        CollectionDate DATETIME DEFAULT GETDATE()
    );
END
GO

INSERT INTO dbo.BackupMonitoringHistory
(
    DatabaseName,
    BackupStartDate,
    BackupFinishDate,
    BackupType,
    DurationSeconds,
    BackupSizeMB,
    CompressedBackupSizeMB,
    ChecksumStatus,
    LastLSN,
    FirstLSN
)
SELECT
    bs.database_name,
    bs.backup_start_date,
    bs.backup_finish_date,

    CASE bs.type
        WHEN 'D' THEN 'FULL'
        WHEN 'I' THEN 'DIFF'
        WHEN 'L' THEN 'LOG'
        ELSE 'OTHER'
    END AS BackupType,

    -- Prevent zero duration for fast backups
    CASE
        WHEN DATEDIFF(SECOND, bs.backup_start_date, bs.backup_finish_date) = 0
        THEN 1
        ELSE DATEDIFF(SECOND, bs.backup_start_date, bs.backup_finish_date)
    END AS DurationSeconds,

    CAST(bs.backup_size / 1048576.0 AS DECIMAL(18,2))
        AS BackupSizeMB,

    CAST(bs.compressed_backup_size / 1048576.0 AS DECIMAL(18,2))
        AS CompressedBackupSizeMB,

    CASE
        WHEN bs.has_backup_checksums = 1
        THEN 1
        ELSE 0
    END AS ChecksumStatus,

    bs.last_lsn,
    bs.first_lsn

FROM msdb.dbo.backupset bs
WHERE
    bs.backup_finish_date IS NOT NULL
    AND bs.backup_start_date >= DATEADD(HOUR, -1, GETDATE())
ORDER BY bs.backup_start_date;
GO