Find the date and time of the last backup for all databases

To find the date and time of the last backup for all the databases for each backup type in the SQL Server instance run this simple query:

SELECT s.Name AS Database_Name,
MAX(b.backup_finish_date) AS BackUpTime,
case when b.type = 'D' then 'Full'
when b.type = 'I' then 'Differential'
when b.type = 'L' then 'Log'
when b.type = 'F' then 'File or filegroup'
when b.type = 'G' then 'Differential file'
when b.type = 'P' then 'Partial'
when b.type = 'Q' then 'Differential partial'
when b.type is NULL then 'N/A'
else 'Other'
end as Backup_type
FROM sys.sysdatabases s
LEFT OUTER JOIN msdb.dbo.backupset b ON b.database_name = s.name
GROUP BY s.Name, b.type
ORDER BY Database_Name

last_backup_time