mssql backup views
select
bs.type,
case bs.type
when 'D' then 'Database'
when 'I' then 'Differential database'
when 'L' then 'Log'
when 'F' then 'File or filegroup'
when 'G' then 'Differential file'
when 'P' then 'Partial'
when 'Q' then 'Differential partial'
else '?'
end as type_desc ,
bs.database_name,
bs.backup_start_date,
bs.backup_finish_date,
bs.compatibility_level,
bs.backup_size,
case
when bs.backup_size >= 1024 and bs.backup_size < 1024*1024
then cast(CEILING( bs.backup_size / 1024 ) as varchar) + 'k'
when bs.backup_size >= 1024*1024 and bs.backup_size < 1024*1024*1024
then cast(CEILING( bs.backup_size / (1024*1024) ) as varchar) + 'm'
when bs.backup_size >= 1024*1024*1024
then cast(CEILING( bs.backup_size / (1024*1024*1024) ) as varchar) + 'g'
else cast(CEILING(bs.backup_size) as varchar)
end as backup_size_desc,
bs.media_set_id,
bs.position,
(select top 1 f.physical_device_name from msdb.dbo.backupmediafamily f where f.media_set_id = bs.media_set_id)
as physical_device_name,
bs.recovery_model,
bs.has_incomplete_metadata
from msdb.dbo.backupset bs
order by bs.backup_start_date desc
-----------------