Query to find Last Full backup of all databases - SQL Server

Query to find Last Full backup of all databases - SQL Server

Run below script to find out last full backup of all the databases.

SELECT  name ,
            recovery_model_desc ,
            state_desc ,
            d AS 'Last Full Backup'
    FROM    ( SELECT    db.name ,
                        db.state_desc ,
                        db.recovery_model_desc ,
                        type ,
                        backup_finish_date
              FROM      master.sys.databases db
                        LEFT OUTER JOIN msdb.dbo.backupset a ON a.database_name = db.name
            ) AS Sourcetable
        PIVOT
            ( MAX(backup_finish_date) FOR type IN ( D) ) AS MostRecentBackup