Backup History using TSQL

Using SQL to Retrieve Backup History Details

By querying system objects in SQL, you can retrieve detailed information about your backup history. This includes tables like Backupmediafamily and Backupset, which provide insight into backup sets and media families.

Understanding Key Tables:

• Backupmediafamily: Stores details for each media family. If a media family exists in a mirrored media set, there will be a separate row for each mirror. This data is stored in the msdb database.
• Backupset: Contains one row for each backup set. A backup set represents a successful backup operation, and statements like RESTORE, RESTORE FILELISTONLY, and RESTORE HEADERONLY act on a single backup set within the media set.

Find Backup History from the Last Day

If you're looking to retrieve backup history from the past 24 hours, use the following SQL query:

SELECT 
    CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, 
    bs.server_name, 
    BS.database_name, 
    BS.backup_start_date, 
    BS.backup_finish_date, 
    DATEDIFF(SECOND, bs.backup_start_date, bs.backup_finish_date) AS 'Duration (sec)', 
    CASE BS.type  
        WHEN 'D' THEN 'Database'  
        WHEN 'L' THEN 'Log'  
        WHEN 'I' THEN 'Differential database'
        WHEN 'F' THEN 'File/Filegroup'   
        WHEN 'G' THEN 'Differential File'  
        WHEN 'P' THEN 'Partial'  
        WHEN 'Q' THEN 'Differential partial'   
    END AS backup_type, 
    ROUND(((bs.backup_size/1024)/1024),2) AS 'Backup Size (MB)',
    ROUND(((bs.compressed_backup_size/1024)/1024),2) AS 'Compressed Backup Size (MB)',
    BS.backup_size, 
    BMF.physical_device_name, 
    BS.name AS backupset_name, 
    bs.is_copy_only, 
    bs.compressed_backup_size, 
    bs.recovery_model, 
    bs.is_password_protected, 
    bs.last_lsn, 
    bs.compatibility_level, 
    bs.database_version
FROM msdb.dbo.backupmediafamily BMF
INNER JOIN msdb.dbo.backupset BS ON BMF.media_set_id = BS.media_set_id 
WHERE CONVERT(datetime, BS.backup_start_date, 102) >= GETDATE()-1 
ORDER BY BS.backup_finish_date DESC

Alternative Query with Different Columns

Here's a similar query that returns the backup history with alternative column names:

SELECT 
    CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, 
    bs.server_name, 
    BS.database_name, 
    BS.backup_start_date, 
    BS.backup_finish_date, 
    DATEDIFF(MINUTE, bs.backup_start_date, bs.backup_finish_date) AS 'Duration (min)', 
    CASE BS.type  
        WHEN 'D' THEN 'Database'  
        WHEN 'L' THEN 'Log'  
        WHEN 'I' THEN 'Differential database'
        WHEN 'F' THEN 'File/Filegroup'   
        WHEN 'G' THEN 'Differential File'  
        WHEN 'P' THEN 'Partial'  
        WHEN 'Q' THEN 'Differential partial'   
    END AS backup_type, 
    ROUND(((bs.backup_size/1024)/1024),2) AS 'Backup Size (MB)', 
    ROUND(((bs.compressed_backup_size/1024)/1024),2) AS 'Compressed Backup Size (MB)', 
    BS.backup_size, 
    BMF.physical_device_name, 
    BS.name AS backupset_name, 
    bs.is_copy_only, 
    bs.compressed_backup_size, 
    bs.recovery_model, 
    bs.is_password_protected, 
    bs.last_lsn, 
    bs.compatibility_level, 
    bs.database_version
FROM msdb.dbo.backupmediafamily BMF
INNER JOIN msdb.dbo.backupset BS ON BMF.media_set_id = BS.media_set_id 
WHERE CONVERT(datetime, BS.backup_start_date, 102) >= GETDATE()-1 
ORDER BY BS.backup_finish_date DESC

Backup History for Databases Without Backups

This query helps identify databases that haven't had any backups:

-- Database backup not taken
SELECT 
    S.NAME AS database_name,  
    'Nobackups' AS [Backup Age (Hours)]  
FROM master.dbo.sysdatabases S 
LEFT JOIN msdb.dbo.backupset B
    ON S.name = B.database_name 
WHERE B.database_name IS NULL 
    AND S.name <> 'tempdb'
ORDER BY B.database_name

Find Most Recent Full Backup for Each Database

To get a list of the most recent full backups for all databases, use this query:

-- To get a list of the most recent full backup of all the databases:
SELECT 
    CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, 
    msdb.dbo.backupset.database_name,  
    MAX(msdb.dbo.backupset.backup_finish_date) AS last_db_backup_date 
FROM msdb.dbo.backupmediafamily  
INNER JOIN msdb.dbo.backupset 
    ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id  
WHERE msdb.dbo.backupset.type = 'D' 
GROUP BY msdb.dbo.backupset.database_name

Track Backup Progress Percentage

If you're monitoring a backup, this query shows the percentage completion:

-- Backup percentage complete
SELECT 
    query = a.text, 
    start_time, 
    percent_complete,
    eta = dateadd(second, estimated_completion_time/1000, getdate())
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) a
WHERE r.command LIKE 'BACKUP%'

References:

For further details, refer to the official documentation:

• Backupset Table Documentation
• Backupmediafamily Table Documentation