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
whereBKS.media_set_id = BMF.media_set_id
order by BKS.backup_start_date desc, BKS.database_name