Angel, Thanks for taking the time to share this with us.

I just put my 5ctv too 😀

- Identify Backup type with msdb..backupset.type 'D' - 'Full'; 'I' - 'Differential'; 'L' - 'Log'

- Specify last time backup was taken using [Backup Status]

CASE msdb..backupset.backup_start_date > DATEADD( hh, -1, getdate() ) )

THEN 'an hour' WHEN b.backup_start_date > DATEADD( dd, -1, getdate() )

THEN 'a day' WHEN b.backup_start_date > DATEADD( dd, -7, getdate() )

THEN 'a week' END ELSE '*****CHECK BACKUP!!!*****' END )

Final version:

SELECT

s.name

, b.backup_start_date

, ( CASE WHEN ( b.backup_start_date > DATEADD( dd, -7, getdate() ) )

THEN CASE b.type WHEN 'D' THEN 'Full'

WHEN 'I' THEN 'Differential'

WHEN 'L' THEN 'Log' END +' Backup within ' +

CASE WHEN ( b.backup_start_date > DATEADD( hh, -1, getdate() ) )

THEN 'an hour' WHEN b.backup_start_date > DATEADD( dd, -1, getdate() )

THEN 'a day' WHEN b.backup_start_date > DATEADD( dd, -7, getdate() )

THEN 'a week' END ELSE '*****CHECK BACKUP!!!*****' END ) [Backup Status]

FROM master..sysdatabasess

LEFT OUTER JOIN msdb..backupset b ON s.name = b.database_name

AND b.backup_start_date = ( SELECT MAX(backup_start_date) FROM msdb..backupset

WHERE database_name = b.database_name

AND type in ( 'D', 'I', 'L' ) ) -- FULL database backups only, not log backups

Thanks you