SQLite Backup Before an Agent Migration: An 'OK' File Can Be Stale

Before an AI coding agent changes a SQLite schema, check that your SQLite backup restores the committed data, not merely that a file exists. In our local test, copying only the main database while WAL mode was active produced a readable file. PRAGMA integrity_check returned ok. A committed row was nevertheless missing.

This is a practical failure boundary for developers who ask an assistant to “back up the database, then migrate it.” Git can preserve the migration code while leaving the application’s current data outside that protection. A backup instruction needs a restore criterion.

What we actually tested

We ran Python 3.14.3 with SQLite 3.51.3 on macOS on November 6, 2026. The experiment used only a disposable temporary directory and synthetic text. It did not connect to an AI product, an MCP server, a customer database, or a remote service.

The fixture creates a table with row 1, commits it, switches to WAL mode, disables automatic checkpointing for that fixture only, and commits row 2. We keep the connection open so the test can demonstrate a committed change still represented in the WAL. This is not a recommended production configuration change.

The first result is stale data, not a demonstrated corrupt file. The second includes both committed rows. The third does not preserve an application’s uncommitted work. The fourth shows why a cleanly closed database is a different case from a live one; it does not prove that an arbitrary application has stopped all writers.

Download the self-contained synthetic lab. Inspect it, then run it with an existing Python installation that includes sqlite3:

python3 sqlite-wal-backup-lab.py

It takes no paths or credentials, creates its own temporary databases, prints the results and runtime versions, and removes its temporary directory. An assertion failure means your result differs; do not treat it as a successful reproduction. The deliberately incomplete copy in the script is a negative control, not a backup tool for your real data.

Why the file can be valid but incomplete

SQLite’s WAL documentation explains that committed changes can reside in the write-ahead log before a checkpoint transfers them into the main database. Separating the main file from that state is not equivalent to asking SQLite for a database snapshot.

Do not delete a -wal file to tidy the folder. Do not replace this procedure with a sequence of ordinary copies of .db, -wal, and -shm while writers continue to run: we did not test that as an atomic snapshot method.

A checksum of a copied file answers whether those bytes match another file. It does not answer whether those bytes contain the committed record your application needs. Likewise, SQLite’s integrity check checks database structure and certain constraints, not whether your expected business records are present. It does not replace a separate foreign-key check either.

The backup primitive, and its boundaries

For an application you control, Python exposes SQLite’s backup mechanism. The essential operation in our lab is:

# source_connection is an already-open SQLite connection.
# destination_path names a separate, disposable destination in this example.
with sqlite3.connect(destination_path) as destination:
    source_connection.backup(destination)

The direction matters: source backs up into destination. SQLite’s Online Backup API replaces the destination database contents. Do not point the destination at an existing database you want to keep, and do not confuse this with a restore command. Use a fresh destination under the appropriate access controls.

The API supports backing up a live database; our test establishes the four outcomes above, not reliability under continuous production writes. We did not test disk exhaustion, interruption, busy-loop behavior, encryption extensions, network filesystems, attached databases, or crash recovery. Application files stored outside SQLite also need their own consistent backup plan. A database snapshot alone does not include uploaded documents or a queue maintained elsewhere.

If a desktop application owns the database and does not document direct access, use its supported backup/export process. Do not open an arbitrary internal database just because its filename ends in .db.

A restore gate before allowing the migration

For a local application you own, make the decision in this order:

1. Identify the data boundary. Record the intended database, application version, migration revision, and any external files required for a restore. Keep secrets out of the note.
2. Use a supported snapshot method. Prefer the application’s documented backup operation, or a SQLite-aware backup in your own code. Keep the original intact.
3. Open the backup separately. Check structure and, where applicable, foreign-key constraints. Query known committed IDs, record counts, and a meaningful recent record. Counts alone can miss substitutions.
4. Run the migration on a disposable restored copy. Verify the application can read it and that important relationships and values still hold. Our downloadable lab stops before this application-specific step.
5. Decide whether to proceed. Missing expected data, uncertain source ownership, an incomplete backup, or a failed restore blocks the real migration. A green integrity result by itself does not permit it.

Choose a quiet maintenance window when comparing an expected manifest against a changing application. Otherwise, document the snapshot boundary so a later write is not mistakenly called lost data. Do not invent a universal “latest timestamp” query: the relevant records depend on your schema.

A rule your coding assistant can follow

Add a scoped instruction to your project’s rules rather than asking for a generic “safe migration”:

Before changing this application's SQLite schema:
- Identify the source database and the documented backup method.
- Do not delete journal/WAL files or copy only a live main DB file.
- Create a separate backup without overwriting an existing destination.
- Restore into a disposable location and verify agreed committed records.
- Test the migration there; report the result and remaining limitations.
- Stop if the source, restore criterion, or backup outcome is uncertain.
- Do not upload database contents, credentials, or logs to external services.

This is an instruction, not an enforcement mechanism. The operator still needs to review the commands and evidence. Browse the rules gallery for ways to place project instructions; do not assume that a rule proves the assistant complied.

Does this problem require a paid tool?

The synthetic test and the SQLite backup primitive do not require a paid service. Start with a supported backup and a restore test, not a purchase. A managed backup product becomes a separate evaluation only if you need unattended retention, off-machine recovery, access auditing, or recovery-time commitments that your current process cannot demonstrate. Ask the supplier to show restoration under your application’s conditions; this article does not test or recommend a specific backup product.

Team credential access is another separate decision. If the migration also involves shared service credentials, our secrets-management offboarding checklist helps identify access and revocation requirements before choosing a service. A password manager does not snapshot SQLite, and buying one does not solve the missing-row result shown here. We have not measured purchases or revenue from this article.

For a related boundary, see what Git stash does with ignored files. That guide concerns Git’s file selection; this experiment concerns the committed state of a database that may span more than its main file.