SQL Server backup report

backup reportSometimes it helps to have simple access to detailed information about your latest backups.
This is why a backup report always comes in handy when you need to know when was the last time your database or a specific database was backed up and what was the last type of backup that was taken.
In order to help you with this we’ve composed a small script which you can easily run to find out important information.

Backup report

Below you can find a simple script which 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 about 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 where the last times FULL, Differential or Transaction Log backups were made.

backup report

 

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.

[Total: 2    Average: 5/5]