SQL Server backup report

In this article, you can find a small script that you can use as a small cheat to have access to an 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 that 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 an installation of SQL Server.

In order to run this script, you will need access to an interface where you will query your databases, 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.

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.

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.

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.