A Dedicated Stored Procedure to Get the Latest Database Backups Status

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.

CodingSight - a Dedicated Stored Procedure to Get the Latest Database Backups Status

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.

@database@backupTypeOutput
AllADisplays the most recent Full, Differential, and Transaction Log backups of all databases within the instance.
AllFDisplays the most recent Full backups of all databases within the instance.
AllDDisplays the most recent Differential backups of all databases within the instance.
AllLDisplays the most recent Transaction Log backups of all databases within the instance.
XADisplays the most recent Full, Differential, and Transaction Log backups of the X database within the instance.
XFDisplays the most recent Full backup of the X database within the instance.
XDDisplays the most recent Differential backup of the X database within the instance.
XLDisplays the most recent Transaction Log backup of the X database within the instance.

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.

Execution Tests

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'
Stored Procedure Execution Test
Stored Procedure Execution Test
Stored Procedure Execution Test
EXEC DBA_DatabaseBackups @database = 'All', @backupType = 'F'
Stored Procedure Execution Test
Stored Procedure Execution Test
EXEC DBA_DatabaseBackups @database = 'All', @backupType = 'D'
Stored Procedure Execution Test
EXEC DBA_DatabaseBackups @database = 'All', @backupType = 'L'
Stored Procedure Execution Test
Stored Procedure Execution Test

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'
Stored Procedure Execution
Stored Procedure Execution

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.

The Complete Stored Procedure Code

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.

Alejandro Cobar
Latest posts by Alejandro Cobar (see all)

Alejandro Cobar

Alejandro Cobar is a multi-platform DBA (with a stronger focus in SQL Server), with a passion to automate stuff as much as possible to make things work for the greater good. He began his professional journey as a developer (a bit more than 10 years ago) and has been going back and forth with DBA roles as well, being the latter the one he has mostly developed throughout the years. He also has several Microsoft SQL Server Certifications that have helped him go even further on his skills as a SQL Server Database Administrator.

Leave a Reply

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