SQL Server backup report

backup reportIn this article you can find a small script which you can use as a small cheat to have access to a on-the go backup report of your databases.

If you can connect to your database, you can run this query and find out when was the last time your databases were backed up

and when were the last times a FULL, Differential or Transaction Log backup was taken.

But what do you do when you do not have remote access to your databases, but still like to have a quick review of your last successful backups? Well, we’ll cover this too…

Backup report

We have created a small script which you can take and run at any time to have access to a small, cheat-like report of your most recent backups for all of your databases on a installation of SQL Server.

In order to run this script, you will need access to an interface where you will query your database, like SSMS or any other third-party tool or even through the command line (sqlcmd) and of course a connection, either local or remote, to your SQL Server instance.

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 query in SSMS or in a command line environment will return a result in a format similar to the one below.

backup report

But what happens when you do not have access to your servers, for different reasons like not having access to your VPN, but you still want to review the status of your last backups?

For any situation where you want to review your backup history or the backup schedule you can use SqlBak. Logging into SqlBak you have direct access to your dashboard, where you get a quick look over the status of your database servers.

backup report

Selecting one of your servers you will have access to a backup report which gives you a more in-depth look over the backup history of your databases. You can see what type of backups were made for the databases on that server, what was the size of the backup, if it was successful or failed.

backup report

Even through this backup report you have access to downloading the backup file or doing a quick restore of the database in case of a disaster situation.

About Radu Gheorghiu

Passionate SQL Developer on the journey of trying to become an expert in all things Data (storage, manipulation, gathering etc.) Information is power, and information can only be obtained from data. Thus, in order to harness the power of information, you must be a master of Data.

1 thought on “SQL Server backup report

  1. Script is very useful.
    One question. Why are you using inner join? Without inner join also I was able to get same results. Is inner join required for any other purpose?

Leave a Reply

Your email address will not be published. Required fields are marked *