How to Monitor the Availability of Your SQL Server Database Backup Files

Every DBA uses backup file as the basis to recover a corrupted SQL Server database. SQL Server’s maintenance plan helps you to create Backup plans. You can then schedule those maintenance plans as jobs though SQL Server agent. However you will not receive any alert if these backup files get corrupted or moved or deleted from the backup path. This article will help you to identify if all databases in your SQL environment has a backup file that can be used during a disaster recovery.

Start with a function

Execute the script from a new query window. We are creating a function to check if the backup file exists at the backup path. The function will return 1 if the backup file is available and returns 0 if the backup file is not available. We can then create a temp table to hold the backup date, age of backup file, full path of backup file and a column to hold 1 or 0.

Set alert

On this temp table, you have to focus on the “cexists” column. If the backup file is missing for a database, fix it immediately by taking a FULL backup. Following might be reasons for the column “cexists” to show ‘0’ for a database

• Backup file was deleted from the backup path
• Backup file was renamed and the script was not able to identify it
• The maintenance plan was set to backup only selected user databases instead of all user databases.

The next column to be focussed is the age of backup file. A full backup file that is one year old will never help us. The best method is to compare this column with your company’s backup policy. In most of companies, FULL back up happens for every week. So any FULL backup file that is older than 7 days is a non-compliant.

Based on these 2 columns you can send an alert email to the DBA team. You can directly attach the result as a file or you can create a HTML alert email.

Best practice

You can use restore verifyonly to check if a backup file can be used for a database restoration activity. However, if you try the restore verifyonly on your corrupted backup files you might find that the result is not 100% certain. As a best practice, you can set a bimonthly audit and do an actual restore with backup files picked at random. During this audit, please ensure that you are doing the restoration on a test server and certainly not on a production server.

Script: 

CREATE FUNCTION dbo.chkfilefn (@bckpath VARCHAR(2000))
RETURNS BIT
AS
BEGIN
    DECLARE @bit INT

    EXEC master.dbo.xp_fileexist @bckpath
        ,@bit OUTPUT

    RETURN cast(@bit AS BIT)
END;
GO

CREATE TABLE #temp1 (
    cdbname VARCHAR(200)
    ,cdatetime DATETIME
    ,cold INT
    ,cloc VARCHAR(1000)
    ,cexists INT
    )
GO

WITH CTETBL1
AS (
    SELECT BCKTBL3.database_name
        ,BCKTBL3.backup_size
        ,BCKTBL3.backup_start_date
        ,BCKTBL1.physical_device_name
        ,Position = ROW_NUMBER() OVER (
            PARTITION BY BCKTBL3.database_name ORDER BY BCKTBL3.backup_start_date DESC
            )
    FROM msdb.dbo.backupmediafamily BCKTBL1
    JOIN msdb.dbo.backupmediaset BCKTBL2 ON BCKTBL1.media_set_id = BCKTBL2.media_set_id
    JOIN msdb.dbo.backupset BCKTBL3 ON BCKTBL2.media_set_id = BCKTBL3.media_set_id
    WHERE BCKTBL3.[type] = 'D'
        AND BCKTBL3.is_copy_only = 0
    )
INSERT INTO #temp1 (
    cdbname
    ,cdatetime
    ,cloc
    )
SELECT BCKTBL4.NAME AS [Database]
    ,backup_start_date AS [Last Full DB Backup Date]
    ,physical_device_name AS [Backup File Location]
FROM sys.databases AS BCKTBL4
LEFT JOIN CTETBL1 AS BCKTBL5 ON BCKTBL4.NAME = BCKTBL5.database_name
    AND Position = 1
ORDER BY [Database];

UPDATE #temp1
SET cold = datediff(d, cdatetime, getdate())

UPDATE #temp1
SET cexists = master.dbo.chkfilefn(cloc)

SELECT *
FROM #temp1

DROP TABLE #temp1
GO

Author Introduction:

Neil Varley is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including repair Outlook pst data problem and excel recovery software products. For more information visit www.datanumen.com