Finding Last Backup Time for All Database – Optimized

SELECT   d.name AS 'DATABASE_Name'
  , MAX(CASE WHEN bu.TYPE = 'D' THEN bu.LastBackupDate END) AS 'Full DB Backup Status'
  , MAX(CASE WHEN bu.TYPE = 'I' THEN bu.LastBackupDate END) AS 'Differential DB Backup Status'
  , MAX(CASE WHEN bu.TYPE = 'L' THEN bu.LastBackupDate END) AS 'Transaction DB Backup Status'
  , 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

Trovato qui.