Find the latest Managed SQL Server Backup STATUS

USE msdb 

GO 

SELECT 

sbf.[database_name] AS [Database Name] 

--,sbf.backup_path AS [Backup Destination and File] 

--,[Backup Type] = 

--CASE sbf.backup_type 

--WHEN 1 THEN 'FULL' 

--WHEN 2 THEN 'LOG' 

--END 

--,[Backup Status] = 

--CASE sbf.[status] 

--WHEN 'A' THEN 'Available' 

--WHEN 'B' THEN 'Copy In Progress' 

--WHEN 'C' THEN 'Corrupted' 

--WHEN 'D' THEN 'Deleted' 

--WHEN 'F' THEN 'Copy Failed' 

--WHEN 'U' THEN 'Unknown' 

--END 

--,sbf.first_lsn AS [First LSN] 

--,sbf.last_lsn AS [Last LSN] 

--,sbf.backup_start_date AS [Backup Start Time] 

--,sbf.backup_finish_date AS [Backup Completion Time] 

--,sbf.expiration_date AS [Backup Expiry Date/Time] 

--,sdb.[Name] AS DatabaseName

,COALESCE(CONVERT(VARCHAR(12), MAX(sbf.backup_finish_date), 101),'-') AS LastBackUpTime

FROM

sys.sysdatabases sdb

LEFT OUTER JOIN smart_backup_files sbf  ON sbf.database_name = sdb.name

where backup_type in (1,2) 
-- 1 and 2 is FULL and Log backup--and cast(backup_finish_date as varchar(11))= cast(getdate() as varchar(11))

GROUP BY sbf.database_name

order by LastBackUpTime desc