T-SQL script - Backup History Analysis

SELECT s.database_name, 
m.physical_device_name, 
cast(s.backup_size/1000000 as varchar(14))+' '+'MB' as bkSize, 
CAST (DATEDIFF(second,s.backup_start_date , 
s.backup_finish_date)AS VARCHAR(4))+' '+'Seconds' TimeTaken,
s.backup_start_date, 
CASE s.[type] 
WHEN 'D' THEN 'Full'
WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Transaction Log'
END as BackupType, 
s.server_name, 
s.recovery_model
FROM msdb.dbo.backupset s
INNER JOIN msdb.dbo.backupmediafamily m
ON s.media_set_id = m.media_set_id
WHERE s.database_name = 'BackupReport'
ORDER BY database_name, backup_start_date, backup_finish_date

Reference : Pinal Dave (http://blog.SQLAuthority.com)

See Also:

• T-SQL Scripts