Get Database Backup History in SQL

This post will review how to get information on your SQL database backup history by querying the msdb system database, which stores information about backups . Here are some useful queries to get details about restore operations.

View Backup History for All Databases

You can use the following query to see the backup history of all databases:

 SELECT    
   CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,    
   BKS.database_name,    
   BKS.backup_start_date,    
   BKS.backup_finish_date,    
   BKS.expiration_date,    
   CASE BKS.type    
   WHEN 'D' THEN 'Database'    
   WHEN 'L' THEN 'Log'    
   END AS backup_type,    
   BKS.backup_size,    
   DKMF.logical_device_name,    
   DKMF.physical_device_name,    
   BKS.[name] AS backupset_name,    
   BKS.[description]    
  FROM    
   msdb.dbo.backupmediafamily AS DKMF   
   INNER JOIN msdb.dbo.backupset BKS ON DKMF.media_set_id =BKS.media_set_id    
  ORDER BY    
   BKS.database_name,    
   BKS.backup_finish_date    

View Backup History of a Specific Database of Database Server

To filter the results for a specific database, modify the query like this:

 SELECT    
   CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,    
   BKS.database_name,    
   BKS.backup_start_date,    
   BKS.backup_finish_date,    
   BKS.expiration_date,    
   CASE BKS.type    
   WHEN 'D' THEN 'Database'    
   WHEN 'L' THEN 'Log'    
   END AS backup_type,    
   BKS.backup_size,    
   DKMF.logical_device_name,    
   DKMF.physical_device_name,    
   BKS.[name] AS backupset_name,    
   BKS.[description]    
  FROM    
   msdb.dbo.backupmediafamily AS DKMF   
   INNER JOIN msdb.dbo.backupset BKS ON DKMF.media_set_id =BKS.media_set_id   
 WHERE database_name='Butterfly'    
  ORDER BY    
   BKS.database_name,    
   BKS.backup_finish_date    

View Details of the Last Backup Operation

To get details about the most recent backup operation for a specific database, use:

 SELECT TOP 1  
   CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,    
   BKS.database_name,    
   BKS.backup_start_date,    
   BKS.backup_finish_date,    
   BKS.expiration_date,    
   CASE BKS.type    
   WHEN 'D' THEN 'Database'    
   WHEN 'L' THEN 'Log'    
   END AS backup_type,    
   BKS.backup_size,    
   DKMF.logical_device_name,    
   DKMF.physical_device_name,    
   BKS.[name] AS backupset_name,    
   BKS.[description]    
  FROM    
   msdb.dbo.backupmediafamily AS DKMF   
   INNER JOIN msdb.dbo.backupset BKS ON DKMF.media_set_id =BKS.media_set_id    
  ORDER BY    
   BKS.backup_finish_date    

Additional Information

To get more detailed information, you can also join with other tables in the msdb database, such as backupmediafamily or backupfile, depending on what information you are interested in.

Thanks