Last Backup Taken, or Not

--Last Full, Diff, and Log backup taken with recovery model
--Get the most recent Full backup taken 

SELECT T1.Name AS DatabaseName, 
 Isnull(Max(T2.recovery_model), 'No Backup Taken') AS recovery_model, 
 'Full' AS BackupType, 
 Isnull(CONVERT(VARCHAR(23), CONVERT(DATETIME, Max(T2.backup_finish_date), 131)), '') AS LastBackUpTaken 
FROM sys.sysdatabases T1 
 LEFT OUTER JOIN msdb.dbo.backupset T2 
 ON T2.database_name = T1.name 
WHERE type = 'D' 
GROUP BY T1.Name 

--Get the most recent Diff backup taken 
UNION ALL 
SELECT T1.Name AS DatabaseName, 
 Isnull(Max(T2.recovery_model), 'No Backup Taken') AS recovery_model, 
 'Differential' AS BackupType, 
 Isnull(CONVERT(VARCHAR(23), CONVERT(DATETIME, Max(T2.backup_finish_date), 131)), '') AS LastBackUpTaken 
FROM sys.sysdatabases T1 
 LEFT OUTER JOIN msdb.dbo.backupset T2 
 ON T2.database_name = T1.name 
WHERE type = 'I' 
GROUP BY T1.Name 

--Get the most recent Log backup taken 
UNION ALL 
SELECT T1.Name AS DatabaseName, 
 Isnull(Max(T2.recovery_model), 'No Backup Taken') AS recovery_model, 
 'Log' AS BackupType, 
 Isnull(CONVERT(VARCHAR(23), CONVERT(DATETIME, Max(T2.backup_finish_date), 131)), '') AS LastBackUpTaken 
FROM sys.sysdatabases T1 
 LEFT OUTER JOIN msdb.dbo.backupset T2 
 ON T2.database_name = T1.name 
WHERE type = 'L' 
GROUP BY T1.Name 

--Get the databases with no backup yet taken 
UNION ALL 
SELECT T1.Name AS DatabaseName, 
 Isnull(Max(T2.recovery_model), 'No Backup Taken') AS recovery_model, 
 'No Backup' AS BackupType, 
 Isnull(CONVERT(VARCHAR(23), CONVERT(DATETIME, Max(T2.backup_finish_date), 131)), '') AS LastBackUpTaken 
FROM sys.sysdatabases T1 
 LEFT OUTER JOIN msdb.dbo.backupset T2 
 ON T2.database_name = T1.name 
WHERE type IS NULL 
GROUP BY T1.Name 

--Sort the combined results
ORDER BY T1.name, 
 BackupType