When was a database backed up?

10 Dec 2010 6-minute read Al Eardley
On-PremData Platform
SQL ServerT-SQL

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


Posts in this Series


Comment on this post: