Get backup history details

backup-history.sql

SET NOCOUNT ON;

SELECT distinct t1.name AS 'DatabaseName'
	,(datediff( ss,  t3.backup_start_date, t3.backup_finish_date)) AS 'DurationInSeconds'
	,t3.user_name AS 'UserResponsible'
	,t3.name AS backup_name
	,t3.description
	,t3.backup_start_date
	,t3.backup_finish_date
	,CASE
		WHEN t3.type = 'D' THEN 'Database'
		WHEN t3.type = 'L' THEN 'Log'
		WHEN t3.type = 'F' THEN 'FileOrFilegroup'
		WHEN t3.type = 'G' THEN 'DifferentialFile'
		WHEN t3.type = 'I' THEN 'DiferentialDatabase'
		WHEN t3.type = 'P' THEN 'Partial'
		WHEN t3.type = 'Q' THEN 'DifferentialPartial'
		ELSE t3.type
		END AS 'BackupType'
	,floor(((t3.backup_size/1024)/1024)) AS 'BackupSizeMB' 
	,t6.physical_device_name
	,CASE
		WHEN t6.device_type = 2 THEN 'Disk'
		WHEN t6.device_type = 102 THEN 'Disk'
		WHEN t6.device_type = 5 THEN 'Tape'
		WHEN t6.device_type = 105 THEN 'Tape'
		END AS 'DeviceType'
	,t3.recovery_model  
FROM sys.databases t1 
	INNER JOIN msdb.dbo.backupset t3 
		ON (t3.database_name = t1.name )
	LEFT OUTER JOIN msdb.dbo.backupmediaset t5 
		ON ( t3.media_set_id = t5.media_set_id )
	LEFT OUTER JOIN msdb.dbo.backupmediafamily t6 
		ON ( t6.media_set_id = t5.media_set_id )
--WHERE t1.name = ''
--WHERE	t3.type = 'D'
--WHERE t1.name = '' AND t3.type = ''
ORDER BY backup_start_date DESC