;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

;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

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