T-SQL Script to Get Last Backup Dates for All Active DB’s

Version: 2005+
*/
with tmp as
(
select row_number() over (partition by BS.database_name order by BS.backup_set_id desc) as rowid, BS.database_name, BS.backup_finish_date
from msdb.dbo.backupset BS
inner join msdb.dbo.backupmediafamily BMF
on BS.media_set_id = BMF.media_set_id
where BS.type = 'D'
and BS.database_name not in ('master', 'msdb', 'tempdb', 'model')
)
select database_name, backup_finish_date as last_full_backup_date from tmp
where rowid = 1 and database_name in (select name from sys.databases where state_desc = 'online' )
order by backup_finish_date desc