Hi,

i had neglected bulk_logged databases... Here a better script

;WITH a AS

( SELECT type='D' UNION ALL SELECT 'L' UNION ALL SELECT 'I'

), c AS

( SELECT b.Name, DATABASEPROPERTYEX(b.Name, 'recovery') as rm, a.type

FROM a, sys.sysdatabases b

WHERE DATABASEPROPERTYEX(b.Name, 'recovery') IN ( 'FULL', 'BULK_LOGGED') OR a.type IN ( 'D', 'I')

), d AS

( SELECT database_name,

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

type,

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,

CASE c.type

WHEN 'D' THEN 'Full'

WHEN 'I' THEN 'Differential'

WHEN 'L' THEN 'Log'

ELSE COALESCE(d.type,'No Backup')

END AS BackupType,

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

FROM c LEFT OUTER JOIN d

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

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

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

ORDER BY c.Name, c.type