SQL Server database backup history

 

How to View SQL Server Backup History

Monitoring database backups is essential for maintaining data integrity and recovery readiness. In this post, we’ll look at how to retrieve backup history in SQL Server using T-SQL queries. These scripts help you analyze when your databases were last backed up, the type of backups performed, and the devices used.

---

Viewing Recent Backup History

The following script displays detailed information about all database backups from the past 30 days for a specified database:

-- Retrieve backup history for the past 30 days
SELECT 
    CONVERT(CHAR(100), SERVERPROPERTY('ServerName')) AS ServerName,
    bs.database_name,
    bs.backup_start_date,
    bs.backup_finish_date,
    bs.expiration_date,
    CASE bs.type 
        WHEN 'D' THEN 'Database' 
        WHEN 'L' THEN 'Transaction Log' 
    END AS BackupType,
    bs.backup_size,
    bf.logical_device_name,
    bf.physical_device_name,
    bs.name AS BackupSetName,
    bs.description
FROM 
    msdb.dbo.backupmediafamily AS bf
    INNER JOIN msdb.dbo.backupset AS bs ON bf.media_set_id = bs.media_set_id
WHERE 
    bs.backup_start_date >= DATEADD(DAY, -30, GETDATE()) 
    AND bs.database_name = 'myDatabase'
ORDER BY 
    bs.database_name, 
    bs.backup_finish_date;

This query pulls data from the msdb system database and provides insight into the backup type, size, device used, and more.

---

Finding the Most Recent Backup per Database

To identify the latest full backup for each database on the server, use the query below. This script returns the most recent full backup details for all databases.

-- Get the latest full backup information for each database
SELECT 
    A.Server,
    A.last_db_backup_date,
    B.backup_start_date,
    B.expiration_date,
    B.backup_size,
    B.logical_device_name,
    B.physical_device_name,
    B.backupset_name,
    B.description
FROM (
    SELECT 
        CONVERT(CHAR(100), SERVERPROPERTY('ServerName')) AS Server,
        bs.database_name,
        MAX(bs.backup_finish_date) AS last_db_backup_date
    FROM 
        msdb.dbo.backupmediafamily AS bf
        INNER JOIN msdb.dbo.backupset AS bs ON bf.media_set_id = bs.media_set_id
    WHERE 
        bs.type = 'D'
    GROUP BY 
        bs.database_name
) AS A
LEFT JOIN (
    SELECT 
        CONVERT(CHAR(100), SERVERPROPERTY('ServerName')) AS Server,
        bs.database_name,
        bs.backup_start_date,
        bs.backup_finish_date,
        bs.expiration_date,
        bs.backup_size,
        bf.logical_device_name,
        bf.physical_device_name,
        bs.name AS backupset_name,
        bs.description
    FROM 
        msdb.dbo.backupmediafamily AS bf
        INNER JOIN msdb.dbo.backupset AS bs ON bf.media_set_id = bs.media_set_id
    WHERE 
        bs.type = 'D'
) AS B 
ON 
    A.Server = B.Server 
    AND A.database_name = B.database_name 
    AND A.last_db_backup_date = B.backup_finish_date
ORDER BY 
    A.database_name;

---

These queries are helpful for DBAs who want to quickly validate the backup status of their databases or audit recent backup operations.

For more details on the backup-related dynamic management views, refer to the Microsoft Documentation on backupset.