Sometimes 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 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.