SQL Backup Validation Automation

Problem Statement

A backup job finishing "successfully" is not the same as a backup being usable. This project automates restoring backups to a scratch instance/database on a schedule, running consistency checks, and generating a report of which backups actually validated versus which ones only looked fine in the job history.

Architecture

Technologies Used

• T-SQL for restore automation and DBCC CHECKDB validation.
• PowerShell for orchestration, cleanup of scratch databases, and report generation.
• SQL Agent for scheduling validation runs outside business hours.
• SSRS (.rdl) for consolidated cross-node backup reporting exported to Excel.

SSRS Cross-Node Backup Report

In an Always On Availability Group environment, not all databases are backed up from the same node. In the deployment described here, eligible AG backups were scheduled on a secondary to offload I/O, while standalone databases were backed up on their hosting instance. This was a deployment-specific job policy, not an Always On guarantee. A single-server report can show an incomplete picture.

To solve this, I built an SSRS report (Database_Backup_Report.rdl) with two separate shared data sources — one pointing to msdb on the primary node and one pointing to msdb on the secondary node — and two corresponding datasets, each running the same backup-summary query against its respective node.

Historical dataset split and its limits

This split describes the original report layout. For complete monitoring, collect relevant backup history from every replica, include AG databases on each node, and reconcile it with the expected database inventory. Failovers and job-policy changes can leave history on either node. The two-tab split alone is not an estate-wide coverage check.

DataSet1 — Primary node (non-AG databases)

Queries msdb on the primary replica and filters results to only databases that are not part of any AG availability group:

-- Filter: databases where group_database_id IS NULL (not enrolled in any AG)
FROM MainBigSet a
WHERE name IN (
    SELECT name FROM sys.databases WHERE group_database_id IS NULL
)

This captures system databases, standalone user databases, and any database not enrolled in an availability group — which this report collects from the designated primary instance.

DataSet2 — Secondary node (AG-synchronized databases)

Runs the identical query against msdb on the secondary replica with no group filter, capturing all databases visible from that node, including standalone/system databases. Each instance keeps its own backup history in msdb; AG history can also exist on a primary or a former replica. Do not infer that a missing row on one node means a backup never ran.

Both queries use @@SERVERNAME as the first column so every row is self-identifying with the node name that actually performed the backup.

Backup summary query pattern (both nodes)

WITH backupsetSummary AS (
    SELECT database_name, type AS bstype,
           MAX(backup_finish_date) AS MAXbackup_finish_date
    FROM   msdb.dbo.backupset
    GROUP BY database_name, type
),
MainBigSet AS (
    SELECT @@SERVERNAME AS servername,
           db.name, db.state_desc, db.recovery_model_desc,
           bs.type,
           CONVERT(decimal(10,2), bs.backup_size / 1024.0 / 1024) AS backup_sizeinMB,
           bs.backup_start_date, bs.backup_finish_date,
           m.physical_device_name,
           DATEDIFF(MINUTE, bs.backup_start_date, bs.backup_finish_date) AS DurationMins
    FROM   master.sys.databases db
    LEFT JOIN backupsetSummary  bss ON bss.database_name = db.name
    LEFT JOIN msdb.dbo.backupset bs  ON bs.database_name = db.name
                                    AND bss.bstype = bs.type
                                    AND bss.MAXbackup_finish_date = bs.backup_finish_date
    LEFT JOIN msdb.dbo.backupmediafamily m ON bs.media_set_id = m.media_set_id
)
SELECT
    servername, name, state_desc, recovery_model_desc,
    Last_Backup                = MAX(backup_finish_date),
    Last_Full_Backup_Start     = MAX(CASE WHEN type = 'D' THEN backup_start_date  END),
    Last_Full_Backup_End       = MAX(CASE WHEN type = 'D' THEN backup_finish_date END),
    Last_Full_Backup_Size_MB   = MAX(CASE WHEN type = 'D' THEN backup_sizeinMB   END),
    Full_Duration_Seconds      = MAX(CASE WHEN type = 'D'
                                     THEN DATEDIFF(SECOND, backup_start_date, backup_finish_date) END),
    Last_Log_Backup_Start      = MAX(CASE WHEN type = 'L' THEN backup_start_date  END),
    Last_Log_Backup_End        = MAX(CASE WHEN type = 'L' THEN backup_finish_date END),
    Last_Log_Backup_Size_MB    = MAX(CASE WHEN type = 'L' THEN backup_sizeinMB   END),
    Log_Duration_Seconds       = MAX(CASE WHEN type = 'L'
                                     THEN DATEDIFF(SECOND, backup_start_date, backup_finish_date) END),
    Days_Since_Last_Backup     = DATEDIFF(DAY, MAX(backup_finish_date), GETDATE())
FROM MainBigSet a
-- Original report: DataSet1 filtered group_database_id IS NULL; DataSet2 did not.
-- For complete collection, query every relevant node WITHOUT that exclusion.
-- Reconcile results by database identity and backup type across all sources.
GROUP BY servername, name, state_desc, recovery_model_desc
ORDER BY name

Excel output — two tabs, one file

The SSRS report renders both datasets in separate tablix regions. When exported to Excel (Database_Backup_Report.xlsx) each tablix lands on its own worksheet tab:

Tab	Source node	Databases shown
Primary Node Backups	Primary replica	Non-AG / standalone databases
Secondary Node Backups	Secondary replica	All visible databases, including AG databases

This gives a single file where a DBA can review the databases returned by both datasets — whether a database is AG-enrolled or not — in one workbook. It still needs cross-replica reconciliation and an inventory comparison before drawing conclusions about coverage.

Architecture with SSRS layer

Screenshots

All screenshots below are from a sanitized demo export of Database_Backup_Report.xlsx. Server names, database names, timestamps, sizes, and durations are synthetic demo values.

Primary Node tab (non-AG / standalone databases):

Secondary Node tab (AG-synchronized databases):

Lessons Learned

The main insight from building this was how often "backup completed successfully" and "backup is restorable" turned out to be different questions in practice - especially after storage or configuration changes. Automating the restore-and-check cycle turned backup validation from a manual, easy-to-skip task into something that runs whether or not anyone remembers to check.

On the reporting side, the AG split was the key challenge: backup history for AG databases can live in either replica's msdb over time, so a single-connection report can miss relevant history. Two SSRS data sources made the original report convenient, but a durable monitoring design must collect from all relevant nodes, handle role changes, and distinguish backup-history checks from actual restore validation.

Validation and limitations

The history query retains databases with no matching backup media so missing history can be investigated. History alone is not proof of recoverability: a successful isolated restore, consistency checks, and application-level validation are separate evidence. Collect the report from every relevant instance, reconcile it against the expected database inventory, and test the dataset split after failover. Purged msdb history must be distinguished from a missing backup.

Backup placement depends on replica role, backup preference, job logic, and SQL Server version. For SQL Server 2012–2022, secondary-replica support includes copy-only full and log backups, not differential backups. An AG database is not inherently backed up only on a secondary. See Microsoft's secondary backup guidance.

The published query is an illustrative reporting pattern; the revised join has not been executed in a SQL Server lab during this website update. No estate-wide coverage percentage or recovery-time improvement is claimed here.