Pete Schott,
I agree with you. I got a number of emails to include Backup type in this script.
Below is the revised script to take care of this issue.
Select a.name,backup_type, Backup_Date from master.dbo.sysdatabases a
left join
( select database_name, Backup_Type = Case type when 'D' then 'Database'
When 'I' then 'Database Differential'
When 'L' then 'Log'
When 'F' then 'File or Filegroup'
Else
'Error'
End,
max(backup_finish_date) backup_date
from msdb.dbo.backupset where backup_finish_date <= getdate()
group by database_name,Type
)
B
on a.name=b.database_name
1234