... and one more version.

This one works on SQL Server versions 2000-2012...

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 ( SELECT b.Name, DATABASEPROPERTYEX(b.Name, 'recovery') as rm, a.*

FROM (SELECT type='D' UNION ALL SELECT 'L' UNION ALL SELECT 'I') a, master.dbo.sysdatabases b

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

) c LEFT OUTER JOIN ( 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

) 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 1, c.type