Backup, VACUUM & Maintenance

Prev Next

RecipeBox's entire database is one file on disk — recipebox.db — which makes backups conceptually simple: copy the file. But "just copy the file" hides a real trap when the database is live and something might be writing to it at the same moment you copy. This lesson covers the naive copy, why it's risky, the robust way SQLite actually recommends backing up a live database, and two maintenance commands every SQLite app should know: VACUUM and PRAGMA integrity_check.

The Naive Approach: Just Copy the File

Because a SQLite database is a single file, copying it with your operating system's normal file copy tools genuinely does work as a backup — but only under specific conditions.

Condition	Is a plain file copy safe?
No connection has the database open at all	Yes — nothing can be mid-write
The app is open but genuinely idle (no writes in flight)	Usually fine, but there's no guarantee something doesn't start writing mid-copy
A write transaction is in progress, rollback journal mode	No — you can copy a half-written, inconsistent file
WAL mode, with writes actively happening	No, unless you also copy the `-wal` and `-shm` files and get the timing exactly right

Note: In WAL Mode & Concurrency we saw that recent writes can live in a separate -wal file before being checkpointed back into the main database file. A plain copy of just recipebox.db while WAL is active can silently miss those writes.

The Robust Way: The Online Backup API / .backup Command

SQLite ships a purpose-built Online Backup API that safely copies a live database page by page, coordinating correctly with any connection that's currently reading or writing it — no exclusive lock on the whole file required. Every serious language binding exposes it, and the sqlite3 command-line shell exposes the same mechanism through the .backup dot-command.

-- From the sqlite3 CLI, back up the live RecipeBox database to a new file
sqlite3 recipebox.db ".backup backup.db"

-- Restoring is just as direct: it's a real, complete database file
sqlite3 backup.db ".tables"

This is the command to reach for whenever you need to back up a database that a running app might still be using — a scheduled nightly backup job, for example. It's the same mechanism most language bindings expose as a backup() method (Python's sqlite3 module has source_conn.backup(dest_conn); other bindings offer an equivalent call).

Rule of thumb: if the database might ever be open elsewhere while you back it up, use .backup or the Online Backup API, not a raw file copy. If you can guarantee it's fully closed everywhere, a plain file copy is fine too — it's simply not the safer default.

VACUUM — Reclaiming Space and Defragmenting

Deleting rows in SQLite doesn't automatically shrink the database file — the freed pages are kept around, marked available for reuse by future inserts. Over time, especially after large deletes (say, a bulk cleanup of old RecipeBox reviews), the file can carry a lot of empty space. VACUUM rebuilds the entire database file from scratch into a fresh, compact copy, reclaiming that space and defragmenting the data in the process.

VACUUM;

Note: VACUUM rewrites the whole database file, not just the changed parts. On a large database this can be slow, and it temporarily needs roughly as much free disk space as the database itself to build the new copy before replacing the old one. Run it during maintenance windows on large databases, not casually on every app launch.

A common pattern is to run VACUUM after a bulk delete you know freed a lot of space, or on an occasional schedule for an app that accumulates and prunes a lot of data over time (old RecipeBox activity logs, for instance) — not after every ordinary delete.

PRAGMA integrity_check — Verifying the Database Isn't Corrupted

PRAGMA integrity_check walks the entire database file, verifying its internal structure — page links, indexes matching their tables, and so on. It returns the single word ok if everything checks out, or a list of specific problems if it doesn't.

PRAGMA integrity_check;
-- ok

Reach for it after recovering a database from an unusual situation — a crashed process, a copy that might have happened mid-write, a file transferred over a flaky connection — or just periodically on a database you care a lot about, before you trust it enough to build a backup from it.

A Simple Maintenance Routine

Task	Command	When
Back up the live database	`.backup` / Online Backup API	On a schedule — e.g. nightly
Verify integrity	`PRAGMA integrity_check;`	Before trusting a recovered or transferred file, or periodically
Reclaim space	`VACUUM;`	After large deletes, or on an occasional schedule

Next: A safe backup is one half of keeping RecipeBox's data trustworthy — the other half is keeping it safe from bad actors in the first place. Head to Security Best Practices next.

Prev Next