A common requirement is to be able to report on the backup status of each database in an instance. There are of course many tools out there that do just this, but what if your organisation has not purchased one of these tools, or the tools are not installed, for instance in a development environment?
Here I present a stored procedure that will produce a table summarising the status of each database in an instance based on the type of backup that you are interested in. It takes an input parameter called @aintSPMode which accepts the following values:
1 - If details of full backups are required
2 - If details of transactional backups are required
4 – If details of differential backups are required
These values can be added together to return multiple tables if required.
/**************************************************
Name: dba_GetBackupHistory
Written by: Al Eardley
Date: December 2010
Purpose:
This script creates a stored procedure that returns the dates of the last backups of every database in an instance
Parameters:
@aintSPMode changes the results that are returned:
1 - details of full backup
2 - details of transaction backup
4 - details of differential backup
Add the values together to get multiple output tables
Execution:
EXEC dbo.dba_GetBackupHistory @aintSPMode = 1
**************************************************/
/**************************************************
** Initialise Script
**************************************************/
PRINT 'dbo.dba_GetBackupHistory'
SET ANSI_NULLS ON
SET NOCOUNT ON
/**************************************************
** Delete SP
**************************************************/
IF OBJECT_ID('dbo.dba_GetBackupHistory') IS NOT NULL
BEGIN
PRINT CHAR(9) + 'dropping'
DROP PROCEDURE dbo.dba_GetBackupHistory
END
GO
/**************************************************
** Create SP
**************************************************/
CREATE PROCEDURE dbo.dba_GetBackupHistory (
@aintSPMode int = 0
, @aintErrorID int = 0 OUTPUT
) AS
SET NOCOUNT ON
SET QUOTED_IDENTIFIER ON
/**************************************************
Declare default variables
**************************************************/
DECLARE
@intErrorID int = 0
, @strErrorDesc varchar(256) = 'No Error'
, @intReturnValue int = 0
, @intRowcount int = 0
, @strSPName varchar(128) = 'dbo.dba_GetBackupHistory'
/**************************************************
** Validate input parameters
**************************************************/
IF @aintSPMode IS NULL
BEGIN
SET @aintSPMode = 0
END
IF @aintSPMode NOT BETWEEN 1 AND 7
BEGIN
SET @intReturnValue = -1
SET @strErrorDesc = '@aintSPMode input should be one of the following values:
1 - Returns details of full backups
2 - Returns details of transaction backups
4 - Returns details of differential backups
Any combination of the above values for multiple results'
GOTO ErrorHandler
END
/**************************************************
Declare local variables
**************************************************/
DECLARE
@sqlSelect nvarchar(2048)
DECLARE @tblDatabases TABLE (
DatabaseID smallint IDENTITY(1,1) NOT NULL PRIMARY KEY
, DatabaseName sysname NOT NULL
, RecoveryModel tinyint NOT NULL
, MostRecentFullBackupDate datetime
, MostRecentDiffBackupDate datetime
, MostRecentTranBackupDate datetime
, ProcessingIsComplete bit DEFAULT 0
)
/**************************************************
Populate @tblDatabases
**************************************************/
BEGIN TRY
INSERT INTO @tblDatabases (
DatabaseName
, RecoveryModel
) SELECT
SD.name
, SD.recovery_model
FROM sys.databases SD WITH(NOLOCK)
ORDER BY
SD.name
END TRY
BEGIN CATCH
SET @strErrorDesc = 'Fatal Error: Populate @tblDatabases: ' + CAST(ERROR_LINE() AS nvarchar) + ' - ' + CAST(ERROR_NUMBER() AS nvarchar) + ' - ' + ERROR_MESSAGE()
SET @intReturnValue = -1
GOTO ErrorHandler
END CATCH
/**************************************************
Update the MostRecentFullBackupDate
**************************************************/
IF @aintSPMode & 1 = 1
BEGIN
BEGIN TRY
;WITH cLastFullBackup (
DatabaseName
, MostRecentFullBackupDate
) AS (
SELECT
BS.database_name
, MAX(BS.backup_finish_date)
FROM msdb.dbo.backupset BS WITH(NOLOCK)
WHERE
BS.type = 'D'
AND
CONVERT(datetime, BS.backup_start_date, 102) >= (GETDATE() - 7)
GROUP BY
BS.database_name
)
UPDATE @tblDatabases
SET
MostRecentFullBackupDate = cLFB.MostRecentFullBackupDate
FROM @tblDatabases tD
JOIN cLastFullBackup cLFB ON (cLFB.DatabaseName = tD.DatabaseName)
END TRY
BEGIN CATCH
SET @strErrorDesc = 'Fatal Error: Update the MostRecentFullBackupDate: ' + CAST(ERROR_LINE() AS nvarchar) + ' - ' + CAST(ERROR_NUMBER() AS nvarchar) + ' - ' + ERROR_MESSAGE()
SET @intReturnValue = -1
GOTO ErrorHandler
END CATCH
END
/**************************************************
Update the MostRecentTranBackupDate
**************************************************/
IF @aintSPMode & 2 = 2
BEGIN
BEGIN TRY
;WITH cLastFullBackup (
DatabaseName
, MostRecentTranBackupDate
) AS (
SELECT
BS.database_name
, MAX(BS.backup_finish_date)
FROM msdb.dbo.backupset BS WITH(NOLOCK)
WHERE
BS.type = 'L'
AND
CONVERT(datetime, BS.backup_start_date, 102) >= (GETDATE() - 7)
GROUP BY
BS.database_name
)
UPDATE @tblDatabases
SET
MostRecentTranBackupDate = cLFB.MostRecentTranBackupDate
FROM @tblDatabases tD
JOIN cLastFullBackup cLFB ON (cLFB.DatabaseName = tD.DatabaseName)
END TRY
BEGIN CATCH
SET @strErrorDesc = 'Fatal Error: Update the MostRecentFullBackupDate: ' + CAST(ERROR_LINE() AS nvarchar) + ' - ' + CAST(ERROR_NUMBER() AS nvarchar) + ' - ' + ERROR_MESSAGE()
SET @intReturnValue = -1
GOTO ErrorHandler
END CATCH
END
/**************************************************
Update the MostRecentDiffBackupDate
**************************************************/
IF @aintSPMode & 4 = 4
BEGIN
BEGIN TRY
;WITH cLastFullBackup (
DatabaseName
, MostRecentDiffBackupDate
) AS (
SELECT
BS.database_name
, MAX(BS.backup_finish_date)
FROM msdb.dbo.backupset BS WITH(NOLOCK)
WHERE
BS.type = 'I'
AND
CONVERT(datetime, BS.backup_start_date, 102) >= (GETDATE() - 7)
GROUP BY
BS.database_name
)
UPDATE @tblDatabases
SET
MostRecentDiffBackupDate = cLFB.MostRecentDiffBackupDate
FROM @tblDatabases tD
JOIN cLastFullBackup cLFB ON (cLFB.DatabaseName = tD.DatabaseName)
END TRY
BEGIN CATCH
SET @strErrorDesc = 'Fatal Error: Update the MostRecentDiffBackupDate: ' + CAST(ERROR_LINE() AS nvarchar) + ' - ' + CAST(ERROR_NUMBER() AS nvarchar) + ' - ' + ERROR_MESSAGE()
SET @intReturnValue = -1
GOTO ErrorHandler
END CATCH
END
/**************************************************
Select details of Databases
**************************************************/
BEGIN TRY
IF @aintSPMode & 1 = 1
BEGIN
SELECT
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server
, tD.DatabaseName
, tD.MostRecentFullBackupDate
, CASE
WHEN DATEDIFF(HOUR, tD.MostRecentFullBackupDate, GETDATE()) > 24 THEN '> 24 hrs'
WHEN DATEDIFF(WEEK, tD.MostRecentFullBackupDate, GETDATE()) > 1 THEN '> 1 week'
WHEN DATEDIFF(MONTH, tD.MostRecentFullBackupDate, GETDATE()) > 1 THEN '> 1 month'
WHEN tD.MostRecentFullBackupDate IS NULL THEN 'NEVER'
ELSE 'Safe'
END AS MostRecentFullBackupAge
FROM @tblDatabases tD
END
IF @aintSPMode & 2 = 2
BEGIN
SELECT
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server
, tD.DatabaseName
, tD.MostRecentTranBackupDate
, CASE
WHEN DATEDIFF(HOUR, tD.MostRecentTranBackupDate, GETDATE()) > 24 THEN '> 24 hrs'
WHEN DATEDIFF(WEEK, tD.MostRecentTranBackupDate, GETDATE()) > 1 THEN '> 1 week'
WHEN DATEDIFF(MONTH, tD.MostRecentTranBackupDate, GETDATE()) > 1 THEN '> 1 month'
WHEN tD.MostRecentTranBackupDate IS NULL THEN 'NEVER'
ELSE 'Safe'
END AS MostRecentTranBackupAge
FROM @tblDatabases tD
WHERE
tD.RecoveryModel = 1
END
IF @aintSPMode & 4 = 4
BEGIN
SELECT
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server
, tD.DatabaseName
, tD.MostRecentDiffBackupDate
, CASE
WHEN DATEDIFF(HOUR, tD.MostRecentDiffBackupDate, GETDATE()) > 24 THEN '> 24 hrs'
WHEN DATEDIFF(WEEK, tD.MostRecentDiffBackupDate, GETDATE()) > 1 THEN '> 1 week'
WHEN DATEDIFF(MONTH, tD.MostRecentDiffBackupDate, GETDATE()) > 1 THEN '> 1 month'
WHEN tD.MostRecentDiffBackupDate IS NULL THEN 'NEVER'
ELSE 'Safe'
END AS MostRecentTranDiffAge
FROM @tblDatabases tD
WHERE
tD.RecoveryModel = 1
END
END TRY
BEGIN CATCH
SET @strErrorDesc = 'Fatal Error: Select details of Databases: ' + CAST(ERROR_LINE() AS nvarchar) + ' - ' + CAST(ERROR_NUMBER() AS nvarchar) + ' - ' + ERROR_MESSAGE()
SET @intReturnValue = -1
GOTO ErrorHandler
END CATCH
/**************************************************/
ErrorHandler:
/**************************************************/
PRINT @strErrorDesc
/**************************************************
Return
**************************************************/
RETURN @intReturnValue
GO
The table that is used as the source of the information is msdb.dbo.backupset which, is defined in “Books Online” as:
Contains a row for each backup set. A backup set contains the backup from a single, successful backup operation. RESTORE, RESTORE FILELISTONLY, RESTORE HEADERONLY, and RESTORE VERIFYONLY statements operate on a single backup set within the media set on the specified backup device or devices.
There are many ways that this procedure can be enhanced and in particular, used as the basis of reporting or alerting and I shall cover these uses in later posts.
Table of Contents
Comment on this post: