SELECT d.name AS 'DATABASE_Name',
MAX(CASE WHEN bu.TYPE = 'D' THEN bu.LastBackupDate END) AS 'Last Full DB Backup',
MAX(CASE WHEN bu.TYPE = 'I' THEN bu.LastBackupDate END) AS
'Last Diferential DB Backup',
MAX(CASE WHEN bu.TYPE = 'L' THEN bu.LastBackupDate END) AS
'Last Transaction Log Backup',
CASE d.recovery_model WHEN 1 THEN 'Full' WHEN 2 THEN 'Bulk Logged' WHEN 3 THEN 'Simple' END RecoveryModel
FROM MASTER.sys.databases d
LEFT OUTER JOIN (SELECT database_name, TYPE, MAX(backup_start_date) AS LastBackupDate
FROM msdb.dbo.backupset
GROUP BY database_name, TYPE) AS bu ON d.name = bu.database_name
GROUP BY d.Name, d.recovery_model
