SELECT T1.Name AS DatabaseName,
 DATABASEPROPERTYEX(T1.Name, 'recovery') AS database_recovery_model, 
 Isnull(Max(T2.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, 
 Isnull(CONVERT(VARCHAR(17), CONVERT(DATETIME, Max(T2.backup_finish_date), 113)), 'Never') AS LastBackUpTaken 
FROM sys.sysdatabases T1 
 LEFT OUTER JOIN msdb.dbo.backupset T2 
 ON T2.database_name = T1.name 
GROUP BY T1.Name, type
ORDER BY T1.Name, type