Backup verification is the least glamorous item on that list, and also the most revealing. Ask most DBA teams what their least favorite recurring task is, and the answer is rarely something complicated. It’s confirming a backup actually exists. It’s proving a backup would actually restore. It’s producing a clean record of who accessed a database, and when, on demand. None of these tasks require deep architectural judgment. All three are exactly the kind of well-defined, repetitive, evidence-generating work that gets done manually under time pressure — which means it’s also the kind of work that quietly stops getting done at all once a team is stretched.
We’ve spent recent engagements closing that gap for clients running SQL Server estates, building three pieces of automation that don’t just perform the task, but generate proof that it happened. Here’s what each one does, and why the distinction between “doing the task” and “proving the task” turns out to matter more than it sounds.
Myth 1: “The backup job succeeded, so backup verification is unnecessary”
A scheduled backup job reporting a green checkmark tells you the job executed. It does not tell you a usable file landed where it was supposed to, how large it is, or whether a permissions change on the NAS silently turned last night’s run into an empty folder.
The fix for backup verification is a scanner, not a bigger dashboard. We built a Python tool that walks the backup repository — a mapped NAS share or a UNC path — and captures full file evidence for every backup it finds: name, path, size, and both created and modified timestamps. Two implementation details matter more than they look:
- It uses os.scandir() instead of the more common os.walk() + os.stat() pairing. On Windows, scandir’s DirEntry caches the stat result from the directory enumeration itself, avoiding a second network round-trip per file over SMB — often a 2-5x speedup on a large NAS tree, which matters when the repository holds years of backup history.
- Every folder is scanned inside its own try/except block. A single locked or offline subfolder — common on NAS shares with inconsistent permissions — gets logged as skipped or errored and the scan continues. One bad folder doesn’t cost you the evidence for the other two hundred.
The tool filters by database name and backup type (FULL, DIFF, Weekly, Monthly…) using wildcard patterns matched against any path component, so it works regardless of whether a given NAS layout nests database folders inside backup-type folders or the other way around. The output is two artifacts: a plain-text audit log for quick review, and a formatted Word document — complete with a summary table — that drops directly into a change ticket or an audit workpaper. Run it daily via Task Scheduler immediately after the backup job finishes, and “the backup exists” stops being something anyone has to take on faith.
Myth 2: “If the backup job succeeded, the backup works”
This is the assumption that actually costs organizations money, and the research on it is not subtle. Cockroach Labs’ State of Resilience 2025 found that 62% of organizations fail to run regular backup and restoration exercises, and 71% do no failover testing whatsoever. Unitrends’ State of Backup and Recovery Report 2025 is more specific about the consequence: more than 60% of organizations believe they could recover from a downtime event within hours, but in reality only 35% actually could — and a quarter of organizations test disaster recovery once a year or less. Unitrends also found 51% of organizations already spend ten-plus hours a week just managing backups, before anyone has tested whether a single one of them actually restores.
A backup nobody has restored is not evidence. It’s a hypothesis.
We addressed this with a monthly automated restore-validation pipeline covering the full chain, not just the restore command:
- Capture source row counts for the tables that matter, before touching anything.
- Search the backup repository recursively and validate candidate files with RESTORE HEADERONLY — reading the backup’s own metadata rather than trusting a filename — to confirm database name and backup type actually match what’s being validated.
- Select the most recent valid backup by its actual backup-finish timestamp, restore it to an isolated destination database (with dynamic file-move clauses built from the destination server’s own default data and log paths, so it never collides with a production file layout), and verify the restored database comes back ONLINE.
- Capture destination row counts and compare them against the source, table by table.
- Run a Created Date validation query — deliberately left as a parameter, so it maps to whatever integrity check a specific restore procedure document actually specifies, rather than a canned assumption.
- Roll all of it into CSV, JSON, and a self-contained HTML report with a clear pass/fail per stage, and return a distinct exit code for each failure mode — backup-path unreachable, no valid backup found, restore failure, validation failure — so the result can be wired into monitoring rather than just read by a human after the fact.
The output isn’t “the restore ran.” It’s a comparison table, an integrity check result, and a timestamped report that says, in writing, whether last month’s backup would actually have saved you.
Myth 3: “Audit evidence is a research project”
The third recurring request is the one that shows up with a deadline attached: a security review or a regulator asks for every recorded access to a specific database across a specific window. Handled manually, that means someone locating the right SQL Server Audit files across live and archive locations, filtering by date by hand, and assembling whatever comes back into something presentable — usually while everything else on their plate waits.
We replaced that with a small, deliberately boring piece of infrastructure: a plain-text configuration file (server, date range, archive path, staging path, output directory, batch size) that an analyst edits without touching a script, paired with a PowerShell workflow built on dbatools that does the retrieval end to end. It queries sys.server_file_audits to find where the currently active audit files actually live, rather than assuming a fixed path, and adds a configured historical archive location alongside them. It pre-filters candidate .sqlaudit files by timestamp before touching SQL Server at all, then pushes the real date-range filter down into sys.fn_get_audit_file itself — server-side — instead of pulling every record across the network and filtering client-side. Large extracts are processed in configurable batches and streamed straight to CSV as they complete, rather than held in memory until the end. When it finishes, it captures a timestamped screenshot as contemporaneous proof of execution, assembles a Word document (with an optional PDF export) containing the run metadata and that screenshot, and cleans up its own staging copy afterward.
What used to be an afternoon of manual file-hunting is now a config edit and one command — with a document at the end of it that’s already shaped for an auditor’s file, not a screenshot someone has to write a memo around.
What Backup Verification, Restore Testing, and Audit Retrieval Have in Common
None of these three tools — backup verification, restore testing, or audit retrieval — is architecturally novel, and that’s deliberate. The value isn’t in doing something clever with SQL Server — it’s in treating evidence generation as a first-class output of a routine task rather than an afterthought bolted on when someone asks for proof. Three habits show up in all three:
- Every one of them degrades gracefully. A locked folder, an offline share, an unreadable backup file — each is logged and skipped, never fatal to the rest of the run.
- Every one of them is parameterized rather than hand-edited. A config file or a set of flags means the same script serves the next audit request, the next database, the next backup type, instead of being rewritten each time one comes up.
- Every one of them is designed to run unattended, on a schedule, so the evidence accumulates whether or not anyone remembers to ask for it — which is exactly when an auditor, a security review, or an actual outage tends to ask.
That last point is the one worth sitting with. The organizations in the Unitrends and Cockroach Labs research aren’t failing to back up their data — they’re failing to prove, on a schedule, that the backup would actually work if they needed it. Routine backup verification and DBA hygiene, automated and made evidentiary by default, is a cheap way to close that gap before something forces the question.
Arsenal IT Consultants builds this kind of automation into SQL Server, ERP, and broader database estates for organizations that need to spend less time proving their infrastructure works and more time running it. If backup verification, restore testing, or audit evidence retrieval in your environment still depends on someone remembering to do it manually, get in touch — a short assessment is usually enough to scope what’s worth automating first.
If this pattern of compliance-driven automation sounds familiar, it’s worth reading how the same discipline applies to legacy systems and network segmentation in our related piece, Patch It or Wall It Off?