SQLite backups you can actually restore

SQLite is one file. That is its best feature and the reason its backups are so often broken, because one file invites the obvious move:

cp app.db backups/app-$(date +%F).db     # don't

This works right up until it doesn't, and it fails in two different ways.

Against a database being written to, you get a torn file. cp reads pages over some interval; a writer changes pages during it. The result can be a file that opens fine and returns wrong results, which is worse than one that refuses to open.

In WAL mode, you get a stale file even with no writer at all. With journal_mode=WAL — the default in many frameworks now, and the right choice for concurrency — committed transactions live in a separate app.db-wal file until a checkpoint folds them back. Copy only app.db and every commit since the last checkpoint is gone. The file is perfectly valid. It is just missing yesterday afternoon.

Check which mode you are in before anything else:

sqlite3 app.db "PRAGMA journal_mode;"     # 'wal' or 'delete'
ls -la app.db*                            # -wal and -shm present?

1. Use the online backup API

SQLite ships two correct answers. Both are one line, both are safe against concurrent writers, and both produce a single self-contained file.

.backup — the general answer:

mkdir -p ./db-backups
sqlite3 app.db ".backup './db-backups/app.db'"

This drives SQLite's online backup API, copying page by page while holding appropriate locks and restarting if a writer changes things underneath it. It includes everything in the WAL. The output is a complete, consistent database file.

VACUUM INTO — the same safety, plus compaction:

sqlite3 app.db "VACUUM INTO './db-backups/app.db'"

Available since SQLite 3.27. It writes a fresh, defragmented copy with free pages reclaimed, so the backup is usually smaller than the source and always internally tidy.

Which to pick:

VACUUM INTO refusing to overwrite is a real operational footgun in a cron job — the second night fails silently unless you delete the target first. If you use it, rm -f the destination as part of the script.

For a backup pipeline that deduplicates, prefer .backup. Compaction rewrites page layout, which makes more of the file look new to a chunker than a plain page-level copy does.

What about .dump?

sqlite3 app.db ".dump" > ./db-backups/app.sql

It produces portable SQL text. Useful for moving between SQLite versions or into another engine, and it diffs and compresses well. But restoring means replaying every INSERT, which on a large database is dramatically slower than copying a file, and the output loses nothing but takes far longer to become a working database. Keep it as a secondary artifact if you want portability; don't make it your primary.

Keep the output path stable. Overwrite ./db-backups/app.db every run rather than dating the filename. Dated filenames defeat deduplication completely — every night looks like a brand new file — and you don't need them, because the next step versions snapshots for you.

2. Verify before you trust it

Thirty seconds, and it catches the failures that otherwise surface during recovery:

sqlite3 ./db-backups/app.db "PRAGMA integrity_check;"    # expect: ok
sqlite3 ./db-backups/app.db "SELECT count(*) FROM users;"

integrity_check walks the whole file and validates structure. quick_check is the faster, shallower version if the database is large enough that the full check hurts.

A file that passes integrity_check and returns a plausible row count is a plausible backup. Plausible isn't proven — that comes in step 4.

3. Get it off the box

A backup beside the database dies with the disk. Push it somewhere with separate credentials.

Sign in at Backup Data, claim the free 5 GB workspace, mint an API key scoped backup:write, backup:read, snapshots:read, then:

npm install @lighthouse-web3/baas-js-sdk
export LH_API_KEY="lh_xxxxxxxxxxxxxxxxxxxxxxxx"
export LH_WORKSPACE_ID="your-workspace-uuid"

import { BackupClient } from "@lighthouse-web3/baas-js-sdk";

const client = new BackupClient({
  apiKey: process.env.LH_API_KEY,
  workspaceId: process.env.LH_WORKSPACE_ID,
});

const snapshot = await client.backup(["./db-backups"], {
  description: "nightly sqlite backup",
  tags: { type: "sqlite", app: "myapp", env: "prod" },
});

console.log(`snapshotId=${snapshot.snapshotId} totalSize=${snapshot.totalSize}`);

(A Go SDK offers the same surface.)

SQLite is where content-defined chunking earns its keep. A 2 GB database where a few thousand rows changed rewrites a small number of pages; the chunker sees the unchanged pages as unchanged and ships only the difference. That is the whole reason for the stable filename in step 1.

If the database holds user data — and a SQLite database usually is the application's data — encrypt client-side:

import { generateKeyfile } from "@lighthouse-web3/baas-js-sdk";

// once:
generateKeyfile("/secure/lh.keyfile", process.env.LH_KEYFILE_PASSPHRASE);

// then on every backup:
await client.backup(["./db-backups"], {
  description: "nightly-encrypted",
  encryption: {
    keyfilePath: "/secure/lh.keyfile",
    passphrase: process.env.LH_KEYFILE_PASSPHRASE,
  },
});

AES-GCM on your machine; the server stores only ciphertext. You hold the keys — lose the keyfile and passphrase and the data is gone, with no server-side reset.

4. Prove it restores

Restoring SQLite is genuinely easy, which is exactly why people skip proving it and then discover the file they have been shipping for a year was empty.

import { mkdtempSync } from "node:fs";
import { tmpdir } from "node:os";
import { join } from "node:path";

const target = mkdtempSync(join(tmpdir(), "sqlite-restore-drill-"));
await client.restore(snapshot.snapshotId, target, {
  onProgress: (e) => console.log(`[${e.phase}] ${e.current}/${e.total}`),
});

Every restore verifies chunk integrity against expected checksums before reassembly.

sqlite3 "$TARGET/db-backups/app.db" "PRAGMA integrity_check;"
sqlite3 "$TARGET/db-backups/app.db" "SELECT count(*) FROM users;"
sqlite3 "$TARGET/db-backups/app.db" "SELECT * FROM users ORDER BY id DESC LIMIT 5;"
sqlite3 "$TARGET/db-backups/app.db" ".schema" | head -30

That last one matters more than it looks. A schema migration that ran after your backup script was written, against a table the script never touches, still shows up here — and finding out that your restore is a schema version behind is much better on a Tuesday than during an outage.

Then point the actual application at it. Copy the restored file into a scratch directory, start the app with its database path overridden, and load a page. A file that opens in sqlite3 is not the same as an application that runs.

Restoring for real is a file copy, with one rule:

# Stop the application first. Never restore over a database with an open writer.
systemctl stop myapp
cp "$TARGET/db-backups/app.db" /var/lib/myapp/app.db
rm -f /var/lib/myapp/app.db-wal /var/lib/myapp/app.db-shm
systemctl start myapp

Deleting the stale -wal and -shm alongside is not optional. Leaving an old WAL next to a restored main database is how you get a file that opens and then behaves incoherently.

The drill checklist:

• ✅ Restore into a scratch directory, never over the live file
• ✅ Run PRAGMA integrity_check and expect exactly ok
• ✅ Compare row counts against production, and read actual rows
• ✅ Check .schema matches the migration your app expects
• ✅ Start the real application against the restored file
• ✅ Delete stale -wal and -shm when restoring in place
• ✅ Record wall-clock time — that is your real RTO

5. Retention and scheduling

const before = new Date(Date.now() - 30 * 24 * 60 * 60 * 1000).toISOString();

// sourceId identifies the machine and path a backup came from; every snapshot
// carries it. Retention is currently applied across the whole workspace, so
// keepLatest counts every snapshot in it, not just this source's.
const policy = { sourceId: snapshot.sourceId, keepLatest: 14, before };

const preview = await client.pruneSnapshots({ ...policy, dryRun: true });
console.log("would delete:", preview.snapshotIds);

await client.pruneSnapshots({ ...policy, dryRun: false });

# /etc/cron.d/sqlite-backup (hourly — SQLite backups are cheap)
0 * * * * app /opt/backup/sqlite-backup.sh >> /var/log/sqlite-backup.log 2>&1

Hourly is reasonable here in a way it rarely is elsewhere: .backup on a modest database takes well under a second, and deduplication means twenty-four snapshots a day cost close to what one costs. Your RPO drops from a day to an hour for almost nothing.

The pipeline, complete

The difference between this and cp app.db backup.db is not effort. Both are one line. One of them is correct in WAL mode and under concurrent writes, and the other is a coin flip you only resolve when you need the file.

Start with the free tier: backupdata.io has 5 GB free, no card, and the 10-minute quickstart gets you to a first verified snapshot today.