TSQL To List Recent Backups

Doing some recent investigation to find out why backups where not showing up where they were expected to be I put together this script to help track down recent backups. Normally I would use Database Health Monitor and the cool backup reports, but I didn’t have that installed on the server needing help.

SELECT b.database_name, 
       b.backup_start_date, 
       b.backup_finish_date, 
       CASE b.type 
       WHEN 'D' THEN 'Database' 
       WHEN 'I' THEN 'Differential' 
       WHEN 'L' THEN 'Log' 
       END AS backup_type, 
       f.physical_device_name
  FROM msdb.dbo.backupmediafamily f
 INNER JOIN msdb.dbo.backupset b ON f.media_set_id = b.media_set_id 
 WHERE b.backup_start_date > GETDATE() - 30
 ORDER BY b.backup_finish_date  desc;

The output looks something like this, although to get this output I commented out the WHERE clause to pull in some data from a test server.

This script has been tested on every non-Azure SQL version between SQL Server 2000, and SQL Server 2019, including 2005, 2008, 2008R2, 2012, 2014, 2016 and 2017.

This saved me some time and helped me track down an issue. I hope you find this useful as well.

-Steve Stedman

More from Stedman Solutions:

Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!