List DB Backup Summary

--
-- Summary of all successful backups to date in descending date and ascending DB name order
--
select 	BKS.name as BackupSetName, 
	case BKS.type when 'D' then 'Full' when 'I' then 'Differential' when 'L' then 'LogFile' else 'File or Filegroup' end as TypeOf,
	BMF.physical_device_name as Location, 
	case when BKS.backup_size > 0 then cast(BKS.backup_size / 1024 as integer) else 0 end as SizeMb,
	BKS.first_lsn StartLSN,
	BKS.last_lsn EndLSN,
	BKS.checkpoint_lsn CheckpointLSN,
	BKS.database_name as DBName,
	BKS.server_name as Server,
	BKS.backup_start_date as StartDate,
	BKS.backup_finish_date as EndDate,
	cast(
	case when 
		datediff(hh, BKS.backup_start_date, BKS.backup_finish_date) = 0 
		then
			case when datediff(mi, BKS.backup_start_date, BKS.backup_finish_date) = 0 
			then
				cast(datediff(ss, BKS.backup_start_date, BKS.backup_finish_date) as varchar) + ' sec'
			else
				cast(datediff(mi, BKS.backup_start_date, BKS.backup_finish_date) as varchar) + ' min'
			end
		else
			cast(datediff(hh, BKS.backup_start_date, BKS.backup_finish_date) as varchar) + ' hrs'
		end
	as varchar) as TimeTaken,
	cast(BKS.software_major_version as varchar) + '.' + cast(BKS.software_minor_version as varchar)+ '.' + cast(BKS.software_build_version as varchar) DBVersion,
	case compatibility_level when 60 then 'SQL Server version 6.0' when 65 then 'SQL Server 6.5' when 70 then 'SQL Server 7.0' when 80 then  'SQL Server 8.0' else 'Unknown' end as CompatibilityLevel,
	BKS.collation_name as Collation
from 	msdb.dbo.backupset BKS, msdb.dbo.backupmediafamily BMF
where	BKS.media_set_id = BMF.media_set_id
order by BKS.backup_start_date desc, BKS.database_name