Following script lists last full, diff and transaction log backup dates and some useful details of all databases in an instance.
d.name as db_name,
when 'D' then 'Full Backup'
when 'I' then 'Differential Backup'
when 'L' then 'Transaction Log Backup'
end as 'backup_type',
cast(bs.compressed_backup_size/1048576 as decimal(15,2)) as 'backup_size_in_MB',
row_number() over(partition by d.database_id,backup_types.Backup_Type order by bs.backup_start_date desc) as rwn
from sys.databases as d
select 'D' as Backup_Type
) as backup_types
left join msdb.dbo.backupset as bs on (d.name=bs.database_name and backup_types.Backup_Type=bs.type)
left join msdb.dbo.backupmediafamily as bmf on bs.media_set_id=bmf.media_set_id
where isnull(bs.is_copy_only,0)=0 --- if you want to filter copy_only backups
) as tbl
and tbl.database_id>4 --- eliminate system databases
order by tbl.database_id asc
If you want to filter system databases and copy_only backups, mind the comments in the script.
You can notice the below result set some columns contain NULL. Does that mean there are no backups have been performed? May be. Please ensure that backup and restore history cleanup script has not been run recently in msdb database. Maybe later I will explain why do we need cleaning backup/restore history tables and how to clean up?