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:
- 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