LLI Wiki — My Digital Knowledge Base

Check the latest full and log backups

msdb queries to show the last full and log backup per database, and the recent backup history.

The following queries read the backup history stored in the msdb database. The first returns, for each user database, the date of the last full backup and the date of the last log backup. The second lists the backups started in the last 11 days, with their type, size and files.

Last full and log backup per database

SELECT @@SERVERNAME AS [Instance],
    a.name AS [Database Name],
    a.recovery_model_desc AS [Recovery Model],
    (
        SELECT MAX(b.backup_finish_date)
        FROM msdb..backupset b
        WHERE b.type = 'D'
            AND a.name = b.database_name
    ) AS [Full Backup],
    (
        SELECT MAX(b.backup_finish_date)
        FROM msdb..backupset b
        WHERE b.type = 'L'
            AND a.name = b.database_name
    ) AS [Log Backup]
FROM master.sys.databases a
WHERE a.name NOT IN ('master', 'msdb', 'model', 'tempdb')
ORDER BY 1, 2;

Backups of the last 11 days

SELECT CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
    msdb.dbo.backupset.database_name,
    msdb.dbo.backupset.backup_start_date,
    msdb.dbo.backupset.backup_finish_date,
    msdb.dbo.backupset.expiration_date,
    CASE msdb..backupset.type
        WHEN 'D' THEN 'Database'
        WHEN 'L' THEN 'Log'
    END AS backup_type,
    msdb.dbo.backupset.backup_size AS backup_size,
    msdb.dbo.backupmediafamily.logical_device_name,
    msdb.dbo.backupmediafamily.physical_device_name,
    msdb.dbo.backupset.name AS backupset_name,
    msdb.dbo.backupset.description,
    CAST(first_lsn AS VARCHAR) AS first_lsn,
    CAST(last_lsn AS VARCHAR) AS last_lsn,
    CAST(checkpoint_lsn AS VARCHAR) AS checkpoint_lsn
FROM msdb.dbo.backupmediafamily
    INNER JOIN msdb.dbo.backupset
        ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE CONVERT(DATETIME, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 11
-- AND msdb..backupset.type = 'D'
ORDER BY backup_finish_date DESC;
Mastodon LLIMastodon SocialFosstodon