Below you can find a simple script that you can run in any SSMS environment in order to have access to a simple backup report, yet one which gives you a clear view of your backup schedule’s activity
SELECT Bk1.server_name AS [Server_Name] , Bk1.database_name AS [Database_Name] , MAX(Bk1.backup_finish_date)AS [Last_Backup] , MAX(CASE WHEN Bk1.type ='D' THEN Bk1.backup_finish_date ELSE NULL END) AS [Last_Full_Backup] , MAX(CASE WHEN Bk1.type ='I' THEN Bk1.backup_finish_date ELSE NULL END) AS [Last_Diff_Backup] , MAX(CASE WHEN Bk1.type ='L' THEN Bk1.backup_finish_date ELSE NULL END) AS [Last_Log_Backup] , Bk2.NAME AS [Backup_Set] , DATEDIFF(d,(max(Bk1.backup_finish_Date)),GETDATE()) AS [Days_Since_Last_Backup] FROM msdb.dbo.backupset AS Bk1 INNER JOIN msdb.dbo.backupset AS Bk2 ON Bk1.backup_set_id = Bk2.backup_set_id GROUP BY Bk1.database_name , Bk1.server_name , Bk2.name ORDER BY database_name
Running this script in SSMS will yield a result for the backup report similar to the one in the image below, where you will be able to see the name of the database that was backed up, from what database server it belongs to when was the last time it was backed up and when were the last times FULL, Differential or Transaction Log backups were made.
We consider that this is the most useful information that a DBA might need at any given moment regarding his backups and his data.
If you have suggestions or alternatives for this query, please post a comment below and we will take into consideration your thoughts and possibly alter the query accordingly.