Script To Retrieve SQL Server Database Backup History


To retrieve backup history from sql server instance we have to query 3 tables 


  1. msdb.dbo.backupset: This Provides information concerning the most-granular details of the backup process
  2. msdb.dbo.backupmediafamily: This Provides metadata for the physical backup files as they relate to backup sets
  3. msdb.dbo.backupfile: This system view provides the most-granular information for the physical backup files

Below is query to find backup history:
/*************************************/
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, 
msdb.dbo.backupmediafamily.logical_device_name, 
msdb.dbo.backupmediafamily.physical_device_name, 
msdb.dbo.backupset.name AS backupset_name, 
msdb.dbo.backupset.description 
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() - 30
ORDER BY 
msdb.dbo.backupset.database_name, 
msdb.dbo.backupset.backup_finish_date desc

/*************************************/







1 comment:

  1. Hi Sourabh,

    Can you please help me to get the below queries for an automation stuff.

    -> Query to find the estimated backup completion time for failed backups (assuming one or more DB backups fails as part of backup job run.
    -> If the ETA for failed DB backups <2 Hrs, query to run backups for only failed backups.

    Thank you in advance.
    Raveendra.

    ReplyDelete