---
title: "How I verify backups: a weekly restore, not a green checkmark"
description: "Every Sunday the home server restores a file from each backup copy and checks every database dump, but four months in it has never restored a whole service."
url: "https://michael.zazon.us/notes/how-i-verify-backups-a-weekly-restore-not-a-green-checkmark"
published: "2026-10-02T12:00:00-04:00"
kind: "build-log"
subject: "operations"
tags: ["backups","data"]
---
I migrated everything over to the new home server, migrated the nightly backups, and migrated the failure alerts (I have [Backrest](https://github.com/garethgeorge/backrest) page me via its own error hook if a run fails).

But I didn't migrate the weekly restore test. That was still running on the old server that I was retiring. So every night I got a nice green checkmark on the new server, but I had no idea if I could restore any file from it. The question had gone from "did it run?" to "would it come back?"

I decided against the most thorough option: restoring the entire full snapshot every week and comparing it to the live disk (too slow, too much space for a weekly). Instead, I restore one sample file and all my database dumps. I can restore individual files cheaply with `restic restore --include` ([restic restore docs](https://restic.readthedocs.io/en/stable/050_restore.html)), and I want to test restoring the databases because you can have a backup that *seems* fine but is actually useless.

I needed to fix the databases before this test would be meaningful. The problem is that I was backing up the live DB files, so restoring a copy of a file that was in the middle of being written to might not be consistent, rendering any checks worthless. So I set up dumps to run a few minutes before the nightly backup (SQLite's [online backup](https://www.sqlite.org/backup.html), `pg_dump` for Postgres). A job on Sunday morning restores the dumps and checks them. It's silent on success, and only pages me if something goes wrong.

> [!warning] Don't back up live DBs
> If you restore from a copy of a live database file that got caught in the middle of a write, the restored copy might not be consistent. So it's good to do dumps a few minutes before running the nightly backup (SQLite has an online backup mode, and you can use `pg_dump` for Postgres).

[Diagram: Nightly dump and backup flow into Sunday restore checks, with what was never restored](https://michael.zazon.us/notes/how-i-verify-backups-a-weekly-restore-not-a-green-checkmark)

The first time I ran the full suite, 28 of 28 tests passed, but only after fixing many things on launch day. The biggest fix was alerts: my notification relay was returning success when I tested sending but silently dropping every message ([One alert relay for everything apprise](https://michael.zazon.us/notes/one-alert-relay-for-everything-apprise)). Other launch-day fixes were timers that never fired and a failure alert that was "heavily truncated on fail".

## The checker needs checking

A few weeks later, I got my first false fail. It turned out that Plex's library DBs use a custom tokenizer, so stock `sqlite3` can't run [`PRAGMA integrity_check`](https://www.sqlite.org/pragma.html#pragma_integrity_check) on them, it errors with `unknown tokenizer: collating`. The run showed 3 failures, but nothing was wrong. I didn't want to exclude Plex (which would silence the noise but stop checking Plex), so I made the checker distinguish between a tooling limit and actual corruption. The fallback is that if it can't run the integrity check, it reads the schema, which still tells me the restored file is a valid DB and not truncated.

```bash
res=$(sqlite3 "${HOST_TMP}/${f}" "PRAGMA integrity_check" 2>&1)
if [[ "$res" == "ok" ]]; then
    report PASS "sqlite integrity: ${f}"
elif [[ "$res" == *"unknown tokenizer"* || "$res" == *"no such module"* || "$res" == *"in prepare"* ]]; then
    # That's a tooling limit, not corruption. Fall back to a tokenizer-free
    # structural probe: a readable schema B-tree proves the restored file is a
    # valid, non-truncated sqlite DB.
    objs=$(sqlite3 "${HOST_TMP}/${f}" "SELECT count(*) FROM sqlite_master" 2>&1)
    if [[ "$objs" =~ ^[0-9]+$ ]]; then
        report PASS "sqlite integrity: ${f} (integrity_check N/A: custom tokenizer; schema readable, ${objs} objects)"
    else
        report FAIL "sqlite integrity: ${f}: schema unreadable: ${objs}"
    fi
fi
```

While chasing that, I found a false pass, which is a worse thing to worry about. It turns out that if you run `sqlite3` on a nonexistent path, it'll create an empty database and report `ok`. So a mangled path would pass, but not because it was testing anything. I learned that the checker itself needs to be checked, and now I'm suspicious of an easy `ok`.

> [!warning] sqlite3 false pass
> `sqlite3` will create an empty database if the path doesn't exist and report `ok`. A mangled path will pass `PRAGMA integrity_check` without actually checking anything.

## One file from every copy

At first I only had the local repository, but over the summer I added the ability to restore individual files from every other copy: the local USB disk, the SSD tier, the offsite bucket, and the cold bucket (see [My backup strategy](https://michael.zazon.us/notes/my-backup-strategy) for the layout and the offsite). Each file is md5 compared to the live file, so I know I'm getting the file's content, not just something. (the comment on the cold bucket leg says "a copy nobody restores from isn't a copy.")

```bash
# Runs once per offsite repo: main (covered set) and cold
# - a copy nobody restores from is not a copy.
age_h=$(( ($(date +%s) - epoch) / 3600 ))
[[ "$age_h" -le "$MAX_AGE_H" ]] \
    && report PASS "${label} freshness: ${snap} is ${age_h}h old (${count} snapshots)" \
    || report FAIL "${label} freshness: ${snap} is ${age_h}h old (> ${MAX_AGE_H}h)"
for sample in "$@"; do
    rrestic restore "$snap" --target "$restore_dir" --include "$sample" >/dev/null 2>&1 || true
    ...
        # Live copy on hand: prove content, not just presence.
        h_live=$(md5sum "$sample" | cut -d' ' -f1)
        h_rest=$(dx md5sum "${restore_dir}${sample}" 2>/dev/null | cut -d' ' -f1)
        [[ -n "$h_live" && "$h_live" == "$h_rest" ]] \
            && report PASS "${label} restore: $(basename "$sample") (${lines} bytes, md5 matches)" \
            || report FAIL "${label} restore: $(basename "$sample") md5 MISMATCH"
done
```

I added two more legs. Every week, I grab a photo from the offsite bucket and md5 compare it to the copy on the server. The photos are the most irreplaceable, and the first one matched byte for byte ([Photos iCloud is a sync service not a backup](https://michael.zazon.us/notes/photos-icloud-is-a-sync-service-not-a-backup)). The test only pages on failure, so a verify that silently stopped would be invisible. So I publish a last-success metric and Grafana alerts if it gets older than eight days.

## Silent gaps

The health check in late September revealed two silent gaps. Both were cases where everything kept passing. The first was a Plex dump that had stopped refreshing. The old copy kept passing the integrity check for about two months. The database was stale, but still valid. I changed it so that any dump much older than its snapshot fails. The orphaned old dump failed the new check (it was 1488 hours old) and I deleted it.

```bash
# Dump age vs the snapshot: a dump the nightly job stopped refreshing (source DB
# deleted, or its dump failing and being rm'd while an old copy survives) still
# passes integrity forever. `restic restore` and `docker cp` both keep mtime.
d_mtime=$(stat -c %Y "${HOST_TMP}/${f}")
d_age_h=$(( (SNAP_EPOCH - d_mtime) / 3600 ))
if [[ "$d_age_h" -gt "$MAX_AGE_H" ]]; then
    report FAIL "db dump: ${f} is ${d_age_h}h older than snapshot ${SNAP_ID} (source gone or dump failing?)"
    continue
fi
```

The second gap was the dump script itself. It was exiting success every night even though two of the dumps were failing. Nothing upstream noticed. Now it errors on any failed dump and publishes its own metrics.

## Never a full restore

The same health check gives me the honest bottom line. Backups happen on time, verifications pass, but I've never restored and started a service from a backup. Git repositories, documents, my photo library's database, my notes vault.. never restored beyond a single file. Postgres dumps are listed but never restored. The latest run shows 74 passed, 0 failed, 1 warning (an unpushed commit).

[Image: End of a weekly verify run: two restores with matching md5s, then 0 failed, 74 passed, 1 warn](https://michael.zazon.us/notes/how-i-verify-backups-a-weekly-restore-not-a-green-checkmark)

This proves I can get my files back, but not yet that I can get a service back. I plan to add a deeper monthly run that restores a database from backups and queries it, going beyond a single file.