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