How to Get Backup Status in SQL Server using Stored Procedure

Any SQL Server DBA (of course, this applies to all platforms) will agree that database backups are the most essential things for data professionals. Monitoring those backups’ statuses is crucial. To make this task more straightforward, I have created a custom Stored Procedure. It will allow you to get the latest database backups’ (if any) statuses for all the necessary databases under your care.

Before we start, check the account executing this Stored Procedure. It must have the necessary rights to perform SELECT on the following tables in order to create the stored procedure:

• sys.databases (master)
• backupmediafamily (msdb)
• backupset (msdb)

How to Use the Stored Procedure

The Stored Procedure T-SQL Code is provided in this article. The procedure expects 2 parameters:

• @database is the name of the target database. If none is specified, all the databases will be assumed.
• @backupType is the type of backup you want to check. Depending on your situation, it can be:
  • F – Full
  • D – Differential
  • L – Transaction Log
  • A – All of the above

Here’s a matrix of possible parameter combinations that can be used, and the output you should expect. X is the name of the database you want to target.

Note: If the target database is in the Simple Recovery Model, the Transaction Log backups information will show up as NULL. It has never been under the Full Recovery Model, and the Transaction Log backup has never taken place for it.

How to Monitor Backup Progress in SQL Server

I am going to demonstrate some of the script combinations for you to get an idea of what to expect from this Stored Procedure:

EXEC DBA_DatabaseBackups @database = 'All', @backupType = 'A'

EXEC DBA_DatabaseBackups @database = 'All', @backupType = 'F'

EXEC DBA_DatabaseBackups @database = 'All', @backupType = 'D'

EXEC DBA_DatabaseBackups @database = 'All', @backupType = 'L'

The screenshots won’t cover the SP targeting a single database because the output is the same, the only difference is, it displays one database.

As you can see, the data for the “Differential” columns are NULL because I have never made a differential backup for any of them. However, to fully demonstrate how useful this solution can be, we need a differential backup. I’ll take one for the DBA database and execute the Stored Procedure to see what it returns:

EXEC DBA_DatabaseBackups @database = 'DBA', @backupType = 'D'

After taking the differential backup, you can see that our stored procedure returns the data for Differential columns too, precisely of the backup I have just made.

Stored Procedure: How to Get Status of Backup in SQL Server

At the very beginning of the script, you will see default values – the script assumes them if no value is passed for each parameter.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Alejandro Cobar
-- Create date: 	2021-05-10
-- Description:	SP to retrieve the latest backups information
-- =============================================
CREATE PROCEDURE DBA_DatabaseBackups
	@database VARCHAR(256) = 'all', 
	@backupType CHAR(1) = 'A'
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @sqlCommand VARCHAR(MAX);

	SET @sqlCommand = '
    WITH MostRecentBackups
	AS(
		SELECT 
			database_name AS [Database],
			MAX(bus.backup_finish_date) AS LastBackupTime,
			CASE bus.type
				WHEN ''D'' THEN ''Full''
				WHEN ''I'' THEN ''Differential''
				WHEN ''L'' THEN ''Transaction Log''
			END AS Type
		FROM msdb.dbo.backupset bus
		WHERE bus.type <> ''F''
		GROUP BY bus.database_name,bus.type
	),
	BackupsWithSize
	AS(
		SELECT 
			mrb.*, 
			(SELECT TOP 1 CONVERT(DECIMAL(10,4), b.compressed_backup_size/1024/1024/1024) AS backup_size FROM msdb.dbo.backupset b WHERE [Database] = b.database_name AND LastBackupTime = b.backup_finish_date) AS [Backup Size],
			(SELECT TOP 1 DATEDIFF(s, b.backup_start_date, b.backup_finish_date) FROM msdb.dbo.backupset b WHERE [Database] = b.database_name AND LastBackupTime = b.backup_finish_date) AS [Seconds],
			(SELECT TOP 1 b.media_set_id FROM msdb.dbo.backupset b WHERE [Database] = b.database_name AND LastBackupTime = b.backup_finish_date) AS media_set_id
		FROM MostRecentBackups mrb
	)

	SELECT 
d.name AS [Database],
      	d.state_desc AS State,
      	d.recovery_model_desc AS [Recovery Model],'

	  IF @backupType = 'F' OR @backupType = 'A'
	  SET @sqlCommand += '
      bf.LastBackupTime AS [Last Full],
      DATEDIFF(DAY,bf.LastBackupTime,GETDATE()) AS [Time Since Last Full (in Days)],
      bf.[Backup Size] AS [Full Backup Size],
      bf.Seconds AS [Full Backup Seconds to Complete],
      (SELECT TOP 1 bmf.physical_device_name FROM msdb.dbo.backupmediafamily bmf WHERE bmf.media_set_id = bf.media_set_id AND bmf.device_type = 2) AS [Full Backup Path]
      '

	  IF @backupType = 'A'
	  SET @sqlCommand += ','

	  IF @backupType = 'D' OR @backupType = 'A'
	  SET @sqlCommand += '
	  bd.LastBackupTime AS [Last Differential],
      DATEDIFF(DAY,bd.LastBackupTime,GETDATE()) AS [Time Since Last Differential (in Days)],
      bd.[Backup Size] AS [Differential Backup Size],
      bd.Seconds AS [Diff Backup Seconds to Complete],
      (SELECT TOP 1 bmf.physical_device_name FROM msdb.dbo.backupmediafamily bmf WHERE bmf.media_set_id = bd.media_set_id AND bmf.device_type = 2) AS [Diff Backup Path]
      '

	  IF @backupType = 'A'
	  SET @sqlCommand += ','

	  IF @backupType = 'L' OR @backupType = 'A'
	  SET @sqlCommand += '
	  bt.LastBackupTime AS [Last Transaction Log],
      DATEDIFF(MINUTE,bt.LastBackupTime,GETDATE()) AS [Time Since Last Transaction Log (in Minutes)],
      bt.[Backup Size] AS [Transaction Log Backup Size],
      bt.Seconds AS [TLog Backup Seconds to Complete],
      (SELECT TOP 1 bmf.physical_device_name FROM msdb.dbo.backupmediafamily bmf WHERE bmf.media_set_id = bt.media_set_id AND bmf.device_type = 2) AS [Transaction Log Backup Path]
	  '

	SET @sqlCommand += '
	FROM sys.databases d
	LEFT JOIN BackupsWithSize bf ON (d.name = bf.[Database] AND (bf.Type = ''Full'' OR bf.Type IS NULL))
	LEFT JOIN BackupsWithSize bd ON (d.name = bd.[Database] AND (bd.Type = ''Differential'' OR bd.Type IS NULL))
	LEFT JOIN BackupsWithSize bt ON (d.name = bt.[Database] AND (bt.Type = ''Transaction Log'' OR bt.Type IS NULL))
	WHERE d.name <> ''tempdb'' AND d.source_database_id IS NULL'

	IF LOWER(@database) <> 'all'
	SET @sqlCommand += ' AND d.name ='+CHAR(39)+@database+CHAR(39)

	EXEC (@sqlCommand)
END
GO

Conclusion

With this custom Stored Procedure, you can build a mechanism to alert you when a certain backup type for any given database hasn’t been made within a particular period.

You can deploy this stored procedure in every SQL Server instance and check the backups information for every single database (System and User databases).

Also, you might use the information returned by the stored procedure to build a backups map to identify the location for the latest backup file for each database. In my current job, I have used this to build a script to orchestrate the restoration tests of all backups under my support and confirm that they are 100% reliable. For me, it has been extremely useful.