Recovery Verification

The previous experiment broke a part of the system that kept running; when the experiment stopped, everything returned to how it was, because nothing had been lost. That assumption collapses when what breaks is data: when a copy is lost, what brings the system back is not stopping the experiment but restoring the backup.

A backup is taken every day, and every day it is taken is counted a success. This is a claim. Recovery verification is testing that claim by actually restoring the backup. The question here is not how long recovery takes — the arithmetic of recovery objectives and the failover model were built in the Resilience and Reliability course and are not repeated here. The question here is: which set of checks says a backup is restorable, and what can that set not see.

A Backup Is a Dump, Its Scope Is a Decision

The library database consists of three tables and one index; the index and the third table were added by two later migrations. A backup is a SQL dump, and the dump’s scope is determined by which objects make it in. Three formats will be compared: one that reads the schema from sqlite_master, one whose hand-written table list has no knowledge of the latest migration, and one that takes the tables but skips the indexes.

NF20 — the source data is 6 books, 10 loans, and 3 fines; the scale factor grows it tenfold. NF21 — the backup is restored within the same process. Both are assumptions; the second is what makes the harness a model, because in a real recovery the file moves to another machine, and the move itself is a separate source of failure.

// library.mjs — the catalog and loan schema, the data, and the three backup formats. A backup
// is a SQL dump; its scope is determined by which objects make it into the dump.
import { DatabaseSync } from 'node:sqlite';

export const SCHEMA = [
  'create table book(no text primary key, title text not null)',
  'create table loan(id integer primary key, book text not null, member text not null, day integer not null)',
  'create unique index loan_unique on loan(book, member)',                // migration 2
  'create table fine(member text primary key, amount integer not null)',   // migration 3
];

export function setup(path, scale = 1) {
  const db = new DatabaseSync(path);
  for (const s of SCHEMA) db.exec(s);
  const book = Array.from({ length: 6 * scale }, (_, i) => `K-${901 + i}`);
  const k = db.prepare('insert into book values (?, ?)');
  for (const no of [...book].reverse()) k.run(no, `book ${no}`);     // insertion order reversed
  const o = db.prepare('insert into loan(book, member, day) values (?, ?, ?)');
  for (let i = 0; i < 10 * scale; i += 1)
    o.run(book[i % book.length], `member-${Math.floor(i / 6) % 7}`, 14 + (i % 14));
  const c = db.prepare('insert into fine values (?, ?)');
  for (let i = 0; i < 3 * scale; i += 1) c.run(`member-${i}`, 5 * (i + 1));
  return db;
}

// 'full' reads the schema from sqlite_master; 'missing-table' uses a hand-written table list;
// 'missing-index' takes the tables but skips the indexes.
export function takeBackup(db, format) {
  const objects = db.prepare(
    'select type, name, sql from sqlite_master where sql is not null order by rowid').all();
  const tables = format === 'missing-table'
    ? ['book', 'loan']                      // hand-written list: does not know about migration 3
    : objects.filter((n) => n.type === 'table').map((n) => n.name);
  const lines = [];
  for (const n of objects) {
    if (n.type === 'index' && format === 'missing-index') continue;
    if (n.type === 'table' && tables.includes(n.name) === false) continue;
    lines.push(`${n.sql};`);
  }
  for (const t of tables) {
    const columns = db.prepare(`select * from pragma_table_info('${t}')`).all().map((s) => s.name);
    const orderBy = t === 'book' ? ' order by no' : '';    // dump order is not insertion order
    for (const r of db.prepare(`select * from ${t}${orderBy}`).all()) {
      const d = columns.map((s) => (typeof r[s] === 'number' ? r[s] : `'${r[s]}'`));
      lines.push(`insert into ${t}(${columns.join(',')}) values (${d.join(',')});`);
    }
  }
  return `${lines.join('\n')}\n`;
}

Three Backups, Five Checks

The checks are ordered by strength. D1 only looks at whether the file exists. D2 compares the table names in the restored copy. D3 compares each table’s row count against the source. D4 tests the write path: is a duplicate loan record rejected. D5 reads the first record without an explicit order and compares it against the source.

// verify.mjs — three backups are actually restored and run through five checks; then the
// verification set is grown to count false passes against false fails.
import { writeFileSync, statSync, rmSync } from 'node:fs';
import { DatabaseSync } from 'node:sqlite';
import { setup, takeBackup } from './library.mjs';

const source = setup(':memory:');
const tables = (db) => db.prepare(
  "select name from sqlite_master where type='table' order by name").all().map((r) => r.name);
const count = (db, t) => db.prepare(`select count(*) c from ${t}`).get().c;
const FIRST = 'select title from book limit 1';
const EXPECTED = { tables: tables(source), first: source.prepare(FIRST).get().title };
for (const t of EXPECTED.tables) EXPECTED[t] = count(source, t);

const CHECK = {
  D1: (_, file) => statSync(file).size > 0,
  D2: (db) => tables(db).join() === EXPECTED.tables.join(),
  D3: (db) => EXPECTED.tables.every((t) => tables(db).includes(t) && count(db, t) === EXPECTED[t]),
  D4: (db) => {                                   // write path: a duplicate loan must be rejected
    const r = db.prepare('select book, member from loan limit 1').get();
    try { db.prepare('insert into loan(book, member, day) values (?, ?, 1)').run(r.book, r.member); }
    catch { return true; }
    return false;
  },
  D5: (db) => db.prepare(FIRST).get().title === EXPECTED.first,
};
const TRUTH = { full: 'healthy', 'missing-table': 'defective', 'missing-index': 'defective' };
const NAMES = Object.keys(CHECK);

const result = {}, measure = {};
for (const format of Object.keys(TRUTH)) {
  const file = `backup-${format}.sql`;
  const dump = takeBackup(source, format);
  writeFileSync(file, dump);
  const db = new DatabaseSync(':memory:');
  db.exec(dump);
  result[format] = Object.fromEntries(NAMES.map((a) => {
    try { return [a, CHECK[a](db, file)]; } catch { return [a, false]; }
  }));
  measure[format] = { lines: dump.split('\n').length - 1, bytes: statSync(file).size };
  rmSync(file);
}

const p = (x, n) => String(x).padStart(n);
console.log(`source: ${EXPECTED.tables.length} tables, ` +
  `${EXPECTED.tables.map((t) => `${t}=${EXPECTED[t]}`).join(', ')} rows`);
console.log(`\n${'backup format'.padEnd(15)}${'actual'.padEnd(11)}` +
  `${NAMES.map((a) => a.padStart(8)).join('')}${'dump lines'.padStart(14)}${'bytes'.padStart(7)}`);
for (const b of Object.keys(TRUTH))
  console.log(`${b.padEnd(15)}${TRUTH[b].padEnd(11)}` +
    `${NAMES.map((a) => (result[b][a] ? 'passed' : 'failed').padStart(8)).join('')}` +
    `${p(measure[b].lines, 14)}${p(measure[b].bytes, 7)}`);
console.log(`D1 file exists  D2 table names  D3 row count  D4 write path  D5 first record (unordered)`);

console.log(`\n${'verification set'.padEnd(20)}${'passing backups'.padStart(34)}` +
  `${'false pass'.padStart(14)}${'false fail'.padStart(14)}`);
for (let n = 1; n <= NAMES.length; n += 1) {
  const set = NAMES.slice(0, n);
  const passing = Object.keys(TRUTH).filter((b) => set.every((a) => result[b][a]));
  const falsePass = passing.filter((b) => TRUTH[b] === 'defective').length;
  const falseFail = Object.keys(TRUTH).filter((b) => TRUTH[b] === 'healthy' && passing.includes(b) === false).length;
  console.log(`${set.join('+').padEnd(20)}${(passing.join(',') || '-').padStart(34)}` +
    `${p(falsePass, 14)}${p(falseFail, 14)}`);
}

source: 3 tables, book=6, fine=3, loan=10 rows

backup format  actual           D1      D2      D3      D4      D5    dump lines  bytes
full           healthy      passed  passed  passed  passed  failed            23   1518
missing-table  defective    passed  failed  failed  passed  failed            19   1282
missing-index  defective    passed  passed  passed  failed  failed            22   1463
D1 file exists  D2 table names  D3 row count  D4 write path  D5 first record (unordered)

verification set                       passing backups    false pass    false fail
D1                    full,missing-table,missing-index             2             0
D1+D2                               full,missing-index             1             0
D1+D2+D3                            full,missing-index             1             0
D1+D2+D3+D4                                       full             0             0
D1+D2+D3+D4+D5                                       -             0             1

What the first row says is the lesson’s starting point: all three backups exist, and none of them is empty. D1 does not distinguish any flaw; the backup job staying green says nothing more than this check. A backup’s existence is not proof of its restorability.

D2 and D3 catch the first flaw. The hand-written table list never picked up the fine table added by the last migration; the restored copy has two tables and no fine records. The name for this class of flaw is backup scope drift: the schema grows with migrations, the backup’s scope stays fixed in a hand-written list, and the gap between them is visible only by restoring.

The second flaw is far quieter. In the missing-index format, every table and every row is in place: D2 passes, D3 passes. The only thing lost is the uniqueness index on the loan table. The restored copy is flawless when read, and breaks only when written to — the same book can be lent to the same member a second time. D4 is the only check that catches this. A verification that tests the read path cannot see a lost constraint.

D5 shows the error in the opposite direction. The first record, read without an explicit order, differs in the restored copy because the dump’s rows were written in a different order — but the restore is correct. The flaw is in the check: the result of an unordered query cannot be a verification criterion. Once this check is added to the set, even the healthy backup fails, and false fail rises to 1.

The chosen threshold is read from the table: the verification set is D1+D2+D3+D4, and the passing criterion is that the whole set passes. The source of the threshold is not a requirement but this scan itself — as the set grows to D4, false pass drops from 2 to 0 while false fail stays at 0; adding one more check brings no gain, only loss. The decision belongs to the backup job: when the set breaks, the backup is counted failed, and that day’s backup is not accepted as usable.

The Cost of the Verification Set

The recovery plan writes a single “restore” step and assigns it a fixed duration. The measurement separates two quantities: restore work and verification work do not grow the same way.

// cost.mjs — the cost of restore and of the verification set, at two data sizes. Query
// counts are measured with a counter; duration depends on the environment so it is not printed, work is.
import { DatabaseSync } from 'node:sqlite';
import { setup, takeBackup } from './library.mjs';

const counted = (db) => { const s = { n: 0 }; return { s, prepare: (q) => { s.n += 1; return db.prepare(q); } }; };
const tables = (db) => db.prepare(
  "select name from sqlite_master where type='table' order by name").all().map((r) => r.name);
const count = (db, t) => db.prepare(`select count(*) c from ${t}`).get().c;
const STEP = {
  'D2 schema': (d) => tables(d),
  'D3 count': (d) => tables(d).map((t) => count(d, t)),
  'D4 write': (d) => {
    const r = d.prepare('select book, member from loan limit 1').get();
    try { d.prepare('insert into loan(book, member, day) values (?, ?, 1)').run(r.book, r.member); }
    catch { /* constraint enforced: expected */ }
  },
};

const p = (x, n) => String(x).padStart(n);
console.log(`${'scale'.padEnd(8)}${'dump records'.padStart(13)}${'restored rows'.padStart(21)}` +
  `${'D2'.padStart(5)}${'D3'.padStart(5)}${'D4'.padStart(5)}${'set queries'.padStart(14)}`);
const measured = {};
for (const scale of [1, 10]) {
  const dump = takeBackup(setup(':memory:', scale), 'full');
  const restored = new DatabaseSync(':memory:');
  restored.exec(dump);
  const records = dump.split('\n').filter((x) => x.startsWith('insert')).length;
  const loaded = tables(restored).reduce((a, t) => a + count(restored, t), 0);
  const counts = [];
  for (const f of Object.values(STEP)) { const w = counted(restored); f(w); counts.push(w.s.n); }
  const total = counts.reduce((a, x) => a + x, 0);
  measured[scale] = { records, total };
  console.log(`${String(scale).padEnd(8)}${p(records, 13)}${p(loaded, 21)}` +
    `${counts.map((x) => p(x, 5)).join('')}${p(total, 14)}`);
}
console.log(`\nwhen data grows 10x, restore work grows ` +
  `${(measured[10].records / measured[1].records).toFixed(2)}x, the verification set grows ` +
  `${(measured[10].total / measured[1].total).toFixed(2)}x`);

scale    dump records        restored rows   D2   D3   D4   set queries
1                  19                   19    1    4    2             7
10                190                  190    1    4    2             7

when data grows 10x, restore work grows 10.00x, the verification set grows 1.00x

The numbers are independent of the run: work is measured instead of duration. Restore grew from 19 records to 190, a 10.00x increase; the verification set stayed at 7 queries, 1.00x. The cost of recovery grows with the data; the cost of verification does not. The practical consequence is this: skipping verification does not meaningfully shorten recovery time, but it turns a recovery’s success back into a claim.

The same table also names the plan’s missing step. The plan’s verification step was the row count — D3. The drill showed that two more steps were needed: counting schema objects and testing the write path. The plan wrote four steps; the measured count came to six, and the two added steps together cost three queries. The plan’s most expensive gap is not a duration estimate but a verification step that was never written.

The Class Verification Cannot See

This set cannot see four things. The first is content corruption: values shifting while row counts hold steady is invisible to D3. The second is consistency at the moment the backup was taken: a dump taken mid-transaction can pass every check. The third is the target environment; here the backup was restored within the same process, whereas in a real recovery the file moves, and the move itself is a separate source of failure. The fourth is the backup’s age: every one of these checks also passes on yesterday’s backup, and none of them counts the data lost in between.

Summary

• A backup’s existence is not proof of its restorability: all three backups passed D1, and two of them were defective.
• The hand-written table list did not pick up the latest migration’s table; the name for this flaw class is backup scope drift, and it is visible only by restoring.
• The backup missing the index passed every read check (D2, D3) and failed only D4, which tests the write path; a lost constraint cannot be seen by reading.
• As the verification set grew to D4, false pass dropped from 2 to 0; once D5, which reads an unordered query, was added, even the healthy backup failed and false fail became 1.
• The chosen threshold is the whole D1+D2+D3+D4 set passing; its source is this scan, and when the set breaks, that day’s backup is not counted usable.
• When data grew 10x, restore work grew 10.00x while the verification set stayed at 1.00x; the plan’s four steps grew to six, and the two added steps cost three queries.

Next Step

The three experiments in this topic were all built the same way: a part of the system was chosen and deliberately broken. The catalog slowed down, a batch was tagged, a table dropped out of the backup. Every time, everything else kept working, and what was measured was whether the pattern or check facing the broken part did its job. None of these three experiments pushed the system to its own limit.

Where that limit sits was never asked. The library loan system gathers a batch in four requests, cuts off a catalog call at 100 ms, and completes a recovery in 190 statements — but at what load these numbers stop meaning anything is unknown. As load rises, the system reaches a point where it stops slowing down and starts dropping instead; that point has to be found first, then written down as a number. The next lesson raises load step by step to find that point, and asks by what metric, with what threshold, the breaking point gets declared.