Where is my backup? by Kenneth Fisher

http://www.sqlservercentral.com/blogs/sqlstudies/2012/12/12/where-is-my-backup/

by Kenneth Fisher









SELECT bs.machine_name,bs.database_name,bs.type, bs.name, bs.is_damaged,bs.user_name,
bf.physical_device_name, bs.backup_start_date, datediff(second,bs.backup_start_date,bs.backup_finish_date) as SecondsSpent,
bf.device_type
FROM msdb.dbo.backupset bs
JOIN msdb.dbo.backupmediafamily bf
ON bs.media_set_id = bf.media_set_id
WHERE bs.type = 'D' --and DATEDIFF(day,bs.backup_start_date, GETDATE()) <=1
ORDER BY bs.database_name, bs.backup_finish_date