With SQL server and 3rd party backup software, it can sometimes be difficult to tell when the last SQL backup has been taken and if the backup software has actually performed the backup or not.

There are 3 main backup types in SQL Server:

• Full
• Incremental
• Transaction Log

The following SQL query will show vital statistics, including when the last backup took place for each backup type. Handy for debugging and checking backup strategies..!

This post is not going to go into the details of how each one works, or recommendations for strategies etc. There are lots of details on guides on the internet, such as this one – Microsoft Technet – Understanding SQL Backups.

 SELECT  name ,
            recovery_model_desc ,
            state_desc ,
            d AS 'Last Full Backup' ,
            i AS 'Last Differential Backup' ,
            l AS 'Last log 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, I, L ) ) AS MostRecentBackup

For example: