How to Use TSQL to Get Database Backup History

Having a record of the database backup history is particularly useful.  I use the following script a lot, but I find it’s main use is when I am logshipping and SOMEBODY breaks the recovery chain by taking an extra log backup.  This can give you an idea of who did it and where the log backup went.

SELECT TOP 50 bs.database_name, m.physical_device_name,
bs.user_name, bs.backup_start_date,
CASE bs.[type]
WHEN ‘D’ THEN ‘Full’
WHEN ‘I’ THEN ‘Differential’
WHEN ‘L’ THEN ‘Transaction Log’
AS BackupType
FROM msdb.dbo.backupset bs
INNER JOIN msdb.dbo.backupmediafamily m ON bs.media_set_id = m.media_set_id
WHERE bs.database_name = ‘AdventureWorks’
ORDER BY backup_start_date DESC, backup_finish_date

If you are just after the last backup date of each database take a look at this one:

select as [DatabaseName],
MAX(T2.backup_finish_date) as [Last Backup Date]
from sys.databases T1 LEFT OUTER JOIN msdb.dbo.backupset T2
where not in (‘model’,’tempdb’)
and t2.type in (‘D’, ‘I’, ‘G’,’F’) — Data, incremental, file/filegroup or file/filegroup incremental
group by
order by

Leave a Comment

Your email address will not be published.