Backup Validation

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.

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

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.

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

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.