---
title: 'Backup Validation'
source: 'https://academia.sh/en/courses/database-administration/backup-validation'
course: 'Relational Database Administration'
language: en
updated: '2026-08-23T07:00:39+00:00'
license: 'CC BY-SA 4.0'
---

# Backup Validation

Knowing a backup's validity before the moment of restore: integrity checking with a file hash, catching silent corruption with a content hash, the scope of a structural check, and setting up a regular recovery drill.

The previous two lessons built taking a backup and restoring from one. Both rested on
the same assumption: the backup file is readable and its content is correct. That
assumption is taken as true until it is tested — right up to the moment of failure.

The most expensive defect in backing up is not the backup that was never taken; it is
**the backup believed to have been taken**. A file half-written because the disk filled
up, a byte corrupted during copying, a backup job silently failing for weeks — all of
these produce the same outcome: an unusable file in hand at the moment of recovery.

This lesson builds ways to know a backup's validity **before the moment of restore**.
There are three separate checks, and each catches something different.

## Three Checks, Three Scopes

**File hash.** The moment the backup is taken, the file's hash is computed and stored
alongside it. A comparison made later shows that not even a single byte of the file has
changed. It catches corruption during transfer and storage; it does not show that the
backup's **content** is correct.

**Structural integrity check.** The engine's own check tests whether the database
file's page structure, tree links, and indexes are consistent. It checks structure, not
values.

**Content hash.** A hash computed from the database's row values is recorded at the
moment the backup is taken, and recomputed and compared after it is restored. This is
the only check that catches corruption in a value.

## Two Forms of Corruption in a Logical Backup

The block below takes a backup, corrupts it in two separate ways, and runs all three
files through the same drill. The first corruption is a single byte changing — a loan
record's book number turns into a different number. The second is truncating the file's
last tenth, which is a half-written backup.

The block operates only on temporary files it produces itself and never modifies a
backup in place; the corrupted versions are written to separate files. A real drill
works the same way: the backup file is never touched, and the work happens on a copy of
it.

```bash
rm -f library.db backup.sql corrupted.sql truncated.sql drill.db

sqlite3 library.db <<'SQL'
CREATE TABLE member(id INTEGER PRIMARY KEY, name TEXT NOT NULL, branch_id INT NOT NULL);
CREATE TABLE loan(id INTEGER PRIMARY KEY, book_id INT NOT NULL, member_id INT NOT NULL,
                   branch_id INT NOT NULL, pickup TEXT NOT NULL, returned TEXT);
WITH RECURSIVE s(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM s WHERE n<5000)
INSERT INTO member(id,name,branch_id) SELECT n,'Member-'||n,(n%3)+1 FROM s;
WITH RECURSIVE s(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM s WHERE n<40000)
INSERT INTO loan(id,book_id,member_id,branch_id,pickup,returned)
SELECT n,(n%500)+1,(n%5000)+1,(n%3)+1,'2025-06-01',NULL FROM s;
SQL

sqlite3 library.db .dump > backup.sql
shasum -a 256 backup.sql | awk '{print "backup hash (at the time it was taken): " substr($1,1,16)}'

# Two corruption forms are produced: a single-byte change and file truncation.
node -e '
const fs = require("node:fs");
const b = fs.readFileSync("backup.sql");
const at = b.indexOf("VALUES(20000,");
const pos = at + "VALUES(20000,".length;       // first digit of the book_id field
const c = Buffer.from(b);
c[pos] = b[pos] === 0x39 ? 0x38 : b[pos] + 1;  // change the digit
fs.writeFileSync("corrupted.sql", c);
fs.writeFileSync("truncated.sql", b.subarray(0, Math.floor(b.length * 0.9)));
console.log("source   : " + b.subarray(at, at + 37).toString());
console.log("corrupted: " + c.subarray(at, at + 37).toString());
'

source_hash=$(sqlite3 library.db '.sha3sum --schema --sha3-256')
echo
echo "backup        | file hash | restore | loan rows | content hash"
echo "--------------|-----------|---------|-----------|-------------"
for file in backup.sql corrupted.sql truncated.sql; do
  rm -f drill.db
  file_hash=$(shasum -a 256 "$file" | awk '{print substr($1,1,8)}')
  if sqlite3 drill.db < "$file" 2>/dev/null; then restore="ok"; else restore="ERROR"; fi
  rows=$(sqlite3 drill.db 'SELECT COUNT(*) FROM loan;' 2>/dev/null || echo "none")
  content=$(sqlite3 drill.db '.sha3sum --schema --sha3-256' 2>/dev/null | tail -1)
  if [ "$content" = "$source_hash" ]; then equal="EQUAL"; else equal="DIFFERENT"; fi
  printf "%-13s | %-9s | %-7s | %9s | %s\n" "$file" "$file_hash" "$restore" "$rows" "$equal"
done
rm -f drill.db
```

```text
backup hash (at the time it was taken): b799626b828b7e06
source   : VALUES(20000,1,1,3,'2025-06-01',NULL)
corrupted: VALUES(20000,2,1,3,'2025-06-01',NULL)

backup        | file hash | restore | loan rows | content hash
--------------|-----------|---------|-----------|-------------
backup.sql    | b799626b  | ok      |     40000 | EQUAL
corrupted.sql | 68a2ee39  | ok      |     40000 | DIFFERENT
truncated.sql | a63b4da9  | ERROR   |      none | DIFFERENT
```

The middle row is the reason for this lesson. The corrupted backup **restored without
error**. The row count came out the same as the source. Tables are in place, indexes are
built, queries run. Only the content hash differs — and the difference amounts to a
single number in one of forty thousand rows.

This form of corruption does not announce itself. When one of forty thousand loan
records has the wrong book number, no query returns an error; only the person searching
for that book fails to find it. A drill that accepts a backup as "successfully restored"
lets this defect through.

The bottom row teaches something different. The truncated backup did not restore, and
the table was not even created. The reason is that the logical backup is written as a
single transaction, started at the beginning of the file and committed at the end. When
the file is left half-written, the commit never arrives, and everything is rolled back.
This is better than a partial restore — a half-populated database is more dangerous than
an empty one, because it looks usable.

The file hash column differs for all three files. Compared against the hash recorded at
the moment the backup was taken, both corruptions would have been caught **without ever
attempting a restore**. This is the cheapest check, and the one line of information that
should be stored alongside every backup.

## Structural Checking in a Physical Backup

In a physical backup, the situation is different: the file is not text, it is a
structure made of pages. The engine can test this structure itself. The block below
corrupts two copies of the same database at two separate locations — one at a page
header, the other at a data value.

```bash
rm -f physical.db

sqlite3 physical.db <<'SQL'
CREATE TABLE loan(id INTEGER PRIMARY KEY, book_id INT NOT NULL, member_id INT NOT NULL,
                   branch_id INT NOT NULL, pickup TEXT NOT NULL);
WITH RECURSIVE s(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM s WHERE n<40000)
INSERT INTO loan(id,book_id,member_id,branch_id,pickup)
SELECT n,(n%500)+1,(n%5000)+1,(n%3)+1,'2025-06-01' FROM s;
CREATE INDEX loan_member ON loan(member_id);
SQL

source_hash=$(sqlite3 physical.db '.sha3sum --schema --sha3-256')

# Two locations are each corrupted by a single byte: a page header and a data value.
for offset in 40960 200000; do
  cp physical.db corrupted-$offset.db
  node -e "
const fs = require('node:fs');
const fd = fs.openSync('corrupted-$offset.db', 'r+');
const b = Buffer.alloc(1);
fs.readSync(fd, b, 0, 1, $offset);
fs.writeSync(fd, Buffer.from([b[0] ^ 0xff]), 0, 1, $offset);
fs.closeSync(fd);
"
done

echo "file                | integrity_check | content hash"
echo "--------------------|-----------------|-------------"
for d in physical.db corrupted-40960.db corrupted-200000.db; do
  ic=$(sqlite3 "$d" 'PRAGMA integrity_check;' 2>&1 | head -1)
  hash=$(sqlite3 "$d" '.sha3sum --schema --sha3-256' 2>/dev/null | tail -1)
  if [ "$hash" = "$source_hash" ]; then equal="EQUAL"; else equal="DIFFERENT"; fi
  printf "%-19s | %-15s | %s\n" "$d" "${ic:0:15}" "$equal"
done
rm -f corrupted-40960.db corrupted-200000.db
```

```text
file                | integrity_check | content hash
--------------------|-----------------|-------------
physical.db         | ok              | EQUAL
corrupted-40960.db  | Error: stepping | DIFFERENT
corrupted-200000.db | ok              | DIFFERENT
```

When the corrupted byte is in a page header, the structural check catches it: the engine
cannot read the page and reports the file as corrupted. When the corrupted byte is
inside a data value, the structural check **says `ok`** — the structure is consistent,
only the value is wrong.

The byte positions here depend on this engine's page size and where the tables land in
the file; different positions may give a different result on your machine. The result
itself is not engine-specific: no tool that checks page structure can know whether a
number inside a page is the correct number.

This is the limit of a structural check, and it shows why a content hash is needed in
addition.

## The Recovery Drill

The two blocks above are parts of a drill. The whole of it consists of the following
steps, and it runs at regular intervals, when nobody is in a hurry.

**The backup file's hash is compared against the hash from when it was taken.** A
mismatch means there is a problem in the storage or transfer layer; no restore is
attempted.

**The backup is restored into a non-production environment.** The target is always a
new, empty database. The drill is never performed on top of a running database.

**A structural integrity check is run.** For physical backups, this verifies page and
index consistency.

**The content hash is compared against the hash recorded when the backup was taken.**
This step replaces comparing row counts — the counts can match while values are
corrupted.

**Duration is measured.** How long the restore takes is the one number that says
whether the recovery time objective is achievable. This number grows as the data grows;
until it is measured, the plan is an assumption.

**The application connects to the recovered copy.** The backup having been restored does
not mean the application will run: a missing index, an unsaved view, or a configuration
table that never made it into the backup surfaces only at this step.

The drill's output is not a report, it is a decision: the backup is valid or it is not.
The in-between state of "probably fine" is the same thing as the backup being
unvalidated.

## The Drill's Frequency and Scope

Frequency is set together with the recovery point objective. Every day a backup goes
unvalidated is a day of risk that it is unreliable. If backups are daily, the drill
should be frequent too; if a full drill is expensive, a light integrity check runs on
every backup, and a full restore drill runs less often.

Scope covers every object that is backed up. If the loan table is backed up but the
branch definitions sit in a separate configuration file, the drill does not bring the
library up and running. A backup's scope is everything the application needs to run;
the drill is how that gets found out.

Finally, the drill itself leaves a record: which backup, on what date, how long the
restore took, and whether the hashes matched. This record answers the question "when did
we last try this" at the next failure.

## Summary

- A backup's validity is known not at the moment it is taken, but at the moment it is
  validated; an unvalidated backup offers the same guarantee as no backup at all.
- A file hash catches corruption without attempting a restore, and is stored alongside
  every backup.
- A logical backup with a single changed byte can restore without error and the row
  count can match; only a content hash catches this corruption.
- A structural integrity check tests page and index consistency, not the correctness of
  values.
- A truncated logical backup produces an empty database, not a partial one, because it
  is written as a single transaction.
- A recovery drill includes hash comparison, structural checking, duration measurement,
  and connecting the application; its output is a valid/invalid decision.

## Next Step

Backup and recovery guarantee that data is not lost; they do not guarantee the system
runs without interruption. A restore that takes forty minutes means the library cannot
lend books for those forty minutes. Shortening the interruption means keeping a copy of
the data already standing ready on another machine. The next lesson takes up physical
replication: continuously streaming the primary server's log records to a standby
server, the delay in that stream, and its effect on read consistency.