Hi,

this is not so short anymore, but should do the trick...

;WITH a AS (SELECT 'Full' AS type UNION ALL SELECT 'Log' UNION ALL SELECT 'Differential'), b AS

( SELECT Name, DATABASEPROPERTYEX(Name, 'recovery') AS rm

FROM sys.sysdatabases

), c AS

( SELECT * FROM a , b WHERE rm = 'FULL' OR (rm = 'SIMPLE' AND type IN ( 'FULL', 'Differential') )

), d AS

( SELECT database_name,

Isnull(Max(recovery_model),'No Backup Taken') AS backup_recovery_model,

CASE type

WHEN 'D' THEN 'Full'

WHEN 'I' THEN 'Differential'

WHEN 'L' THEN 'Log'

ELSE COALESCE(type,'No Backup')

END AS BackupType,

CONVERT(VARCHAR(17), CONVERT(DATETIME, Max(backup_finish_date), 113)) AS LastBackUpTaken

FROM msdb.dbo.backupset

GROUP BY database_name, type

)

SELECT c.Name AS DatabaseName,

c.rm AS database_recovery_model,

c.type AS BackupType,

Isnull(d.LastBackUpTaken, 'Never') AS LastBackUpTaken

FROM c LEFT OUTER JOIN d

ON d.database_name = c.name AND c.type = d.BackupType

WHERE NOT ( c.type = 'Differential' AND d.LastBackUpTaken IS NULL) AND c.name <> 'tempdb'

GROUP BY c.Name, d.BackupType, d.LastBackUpTaken, c.rm, c.type

ORDER BY 1, 3

All corrections are welcome... 🙂