SELECT bs.server_name
	,bs.database_name
	,bs.recovery_model
	,convert(NUMERIC(10, 2), backup_size / 1024 / 1024) [backup, Mb]
	,bs.backup_start_date
	,bs.backup_finish_date
	,CASE 
		WHEN datediff(ss, bs.backup_start_date, bs.backup_finish_date) >= 86400
			THEN Convert(VARCHAR(5), datediff(ss, bs.backup_start_date, bs.backup_finish_date) / 86400) + ' days '
		ELSE ''
		END + Convert(VARCHAR(8), DateAdd(Second, datediff(ss, bs.backup_start_date, bs.backup_finish_date), 0), 108) AS [Duration]
	,bs.name
	,bs.user_name
	,CASE bs.type
		WHEN 'D'
			THEN 'Database full'
		WHEN 'I'
			THEN 'Database Differential'
		WHEN 'L'
			THEN 'Log'
		WHEN 'F'
			THEN 'File or Filegroup'
		WHEN 'G'
			THEN 'File Differential'
		WHEN 'P'
			THEN 'Partial'
		WHEN 'Q'
			THEN 'Partial Differential'
		ELSE 'Unknown'
		END 'backup type' -- from BOL
	,bmf.physical_device_name
FROM msdb..backupset bs
JOIN msdb..backupmediafamily bmf ON bs.media_set_id = bmf.media_set_id
ORDER BY backup_finish_date DESC