Back Up a Live SQLite Database Without Copying the Wrong File
SQLite looks like a file, so a first backup plan often sounds simple: copy app.db to a backup directory. That can be safe when the database is closed and no process can write to it. It is not a general live-backup strategy.
The risk is especially easy to miss when the database uses Write-Ahead Logging (WAL). A committed transaction may still be in the app.db-wal sidecar rather than merged into app.db. SQLite warns that separating the database from its WAL can lose committed transactions or corrupt the database. Copying one file while writes continue is not the same thing as asking SQLite for a consistent snapshot.
The useful rule is: let SQLite make the snapshot, then verify and test that snapshot. Here are three ways to do that, depending on where the backup goes and what you need from it.
Why the main database file may not be the whole story
In WAL mode, writers append changes to a write-ahead log. Readers consult that log as well as the main database, and a checkpoint later transfers changes back into the database file. SQLite manages the WAL and its shared-memory index while connections are open; applications should not treat those implementation files as independent backup units.
That leads to two different cases:
- The database is fully stopped and closed: a file-level copy can be reasonable if you can guarantee no process or connection is using the database and there is no unfinished journal state.
- The database is live, or you cannot guarantee quiescence: use SQLite's backup facilities. A recursive copy or ordinary
rsynccan observe different database pages at different moments and produce a copy that does not represent one consistent state.
For WAL databases, the -wal file is persistent state and must stay with the database if the database is copied or moved as files. Do not manually delete it to make a backup "clean"; SQLite documents that closing the last connection normally performs cleanup itself.
Option 1: the Online Backup API for a live local copy
SQLite's Online Backup API creates a snapshot from a live database. It reads the source in steps, locking it only while pages are being read, so other connections can keep using the source. It can also copy incrementally, which is useful when a large backup should avoid holding a source read lock for the entire operation.
The Python standard library exposes this API through Connection.backup():
import sqlite3
source = sqlite3.connect("app.db")
destination = sqlite3.connect("app-backup.db")
try:
source.backup(destination)
finally:
destination.close()
source.close()
This example is intentionally small. A production backup job should handle exceptions, use a unique destination or retention policy rather than overwrite its only good copy, and record whether the operation completed. The Python sqlite3 module is optional in some Python builds and uses the SQLite library linked into that interpreter, so check the deployed runtime.
The command-line shell offers the corresponding .backup command:
sqlite3 app.db ".backup app-backup.db"
This CLI command is documented but was not run during this investigation. The Python form above was run against a temporary WAL-mode database.
Option 2: VACUUM INTO for a compact snapshot
If you want a smaller, rebuilt copy, VACUUM INTO writes a consistent snapshot to a new database file:
VACUUM INTO 'app-backup.db';
The target must not already exist, unless it is empty. Unlike the Online Backup API, VACUUM INTO rebuilds the database, so it can produce a compact copy and does not carry free pages from deleted records into that output. It requires more CPU work; SQLite describes the Online Backup API as more incremental and less CPU-intensive. Choose this deliberately for copy cleanup or file size, not as a routine substitute for every backup.
There is an important failure mode: if the command is interrupted by an unplanned shutdown or power loss, the output can be incomplete or corrupt. Treat the output as untrusted until the operation completes and the result is checked. VACUUM INTO also does not erase older backups or other copies of data that your retention policy keeps.
Option 3: sqlite3_rsync for SSH-based remote copies
For a remote replica, SQLite provides sqlite3_rsync, a database-aware synchronization utility that communicates over SSH. A typical shape is:
sqlite3_rsync ./app.db backup-host:/srv/backups/app.db
This command is an illustrative, unrun example. The local and remote hosts need the utility installed, and the remote executable must be available through SSH's PATH or specified with the tool's --exe option. At least one endpoint must be on the local machine; sqlite3_rsync does not connect two remote databases directly. The destination is a snapshot of the source as it existed when the command began, and it is read-only while synchronization is running.
The useful difference from ordinary rsync is that sqlite3_rsync understands SQLite's consistency requirements. Standard rsync may produce an inconsistent or corrupt copy if the source changes while file blocks are copied, unless you can guarantee the database is not in use for the whole operation.
This tool has also become less restrictive: SQLite 3.50.0 removed its former requirement that both databases be in WAL mode and improved synchronization efficiency when the source and replica are already similar. That does not make it universal. You still need SSH access, the remote executable, a suitable local/remote topology, and a plan for destination retention and access control.
Verify the backup—and prove you can restore it
A successful command is evidence that a copy operation completed; it is not evidence that your recovery process works. Start with a structural check on the backup:
PRAGMA integrity_check;
SQLite should return ok for a structurally sound database. Run this against the backup file, not just the live source. Check that the expected file exists before opening it: the CLI creates a new database if its filename does not exist. For example:
test -f app-backup.db && sqlite3 app-backup.db "PRAGMA integrity_check;"
An integrity check cannot tell you whether the backup contains the expected tables, is recent enough, whether all required external files were captured, or whether your application can start with it. For important data, periodically restore a retained generation into an isolated path, run application-level queries or checks, and record the result. A backup that has never been restored is an assumption, not a recovery plan.
Keep multiple generations so a newly created but unusable copy does not replace the only known-good one. Protect the backup directory like the source database: it may contain the same personal or business data. For remote copies, restrict the SSH account and destination permissions; also choose encryption and retention appropriate to the data and your storage provider.
A small local check
I tested the Python Connection.backup() path with a temporary database in WAL mode: create a table, commit one row, back up the still-open source, query the destination, and run PRAGMA integrity_check. The copied row was present and the integrity check returned ok.
That is a narrow sanity check, not a benchmark or a concurrency test. It did not test concurrent writers, process crashes, VACUUM INTO, the CLI, sqlite3_rsync, SSH, or a full application restore. Those conditions need separate tests in the environment where the backup will actually run.
Which method should you choose?
For a live local copy, start with the Online Backup API or a language binding such as Python's Connection.backup(). Use VACUUM INTO when a compact, rebuilt snapshot is worth its extra work. Use sqlite3_rsync when you need an SQLite-aware remote snapshot and can satisfy its SSH and executable requirements. Copy files directly only when the database is quiescent and you understand the journal state.
Whichever method you choose, retain more than one generation, check the produced database, and rehearse a restore. The most useful backup is the one you can recover from.