Back Up Live SQLite Without Losing WAL Data

SQLite often sits inside a small application that has become important without acquiring a formal recovery plan. The scheduled job copies a .db file, the destination grows every night, and nobody has opened a restored copy through the application.

Before changing that job, establish which database file the running application actually uses, its journal mode, and whether it stores uploads or other state outside the database. A reliable database snapshot cannot recover an attachment that was never included.

Why copying the visible file can fail

In write-ahead logging mode, committed changes can remain in a separate -wal file until checkpointing transfers them into the main database. SQLite explicitly treats that WAL file as part of the database's persistent state. Separating the two can lose committed transactions or leave an unusable copy. A sequential copy of several changing files does not, by itself, establish a consistent point in time. SQLite's WAL documentation explains the relationship.

Do not fix this by deleting WAL files, forcing a journal-mode change, or assuming a successful filesystem copy means a successful database backup. Those actions change the running system without answering whether the resulting backup is complete.

Ask the application supplier for its supported backup method first. An embedded application may use a bundled SQLite library, encryption or its own file coordination. Its documented export procedure may also capture configuration that a database-only tool misses.

Choose an intentional snapshot method

The SQLite Online Backup API produces a consistent destination database and can copy incrementally while other users continue working. A backup tool using that API must still handle errors, busy databases and unsuccessful completion. Schedule a retry or alert; do not promote a partial output simply because the file exists.

Another option is VACUUM INTO. It creates a compact database containing the same logical content in a separate destination, leaving the source unchanged. The destination must be absent or empty. An interrupted operation can leave an incomplete output, so completion matters here too.

Choose between these methods on the application's support, database size, write activity and available resources. Incremental copying may suit a busy service; a compact export may suit a smaller scheduled workload. A maintenance window with the application stopped cleanly can also be reasonable when the business accepts the interruption and the supplier documents the required files.

Whichever method you select, write each candidate backup to a new, restricted location. Record its start and finish times, source identity, application version and completion status. Only after validation should it become the recovery copy sent to independent storage. Keep the previous successful copy while the new one is being produced.

Validate the database and the business data

Run checks against the backup in an isolated working directory. For a confirmed standalone copy, a read-only diagnostic might be:

sqlite3 -readonly /srv/restore-test/app.db 'PRAGMA quick_check;'

Use a path that already exists and contains the copied database. quick_check is a useful initial screen, but it omits some checks performed by integrity_check; neither substitutes for application-level validation. SQLite documents their differences and provides foreign_key_check separately for foreign-key errors in its PRAGMA reference.

Decide what a correct restore should show before running it. For an example stock application, check a known product, a recent stock movement, an attached delivery note and a report total. Compare the data with the backup's recorded time. An old but internally consistent database can pass integrity checks while failing the business recovery requirement.

Start a matching application version against the restored copy with outbound integrations disabled. Avoid accidentally sending invoices, processing queued tasks or connecting the test application to production storage. If startup performs a schema migration, preserve the original backup and let it modify only a disposable copy.

Put the restore procedure beside the backup job

The procedure should identify who can obtain the backup, the application package needed to open it, any encryption material, the destination permissions and the tests that establish success. Include non-database files and explain whether they must match the database snapshot exactly.

Rehearse after meaningful application upgrades and changes to storage or backup tooling. Measure the whole recovery, including obtaining the software and checking the data. A ten-second file transfer is not evidence of a ten-second service recovery.

For an incident, preserve the failed database and associated files before repair attempts. Restore into a separate location, verify the result, then plan the application switch. That leaves a route back if the chosen snapshot is too old or the restored service behaves differently from the original.

References

• SQLite, Write-Ahead Logging. Accessed 17 October 2026.
• SQLite, Online Backup API. Accessed 17 October 2026.
• SQLite, VACUUM and VACUUM INTO. Accessed 17 October 2026.
• SQLite, PRAGMA statements. Accessed 17 October 2026.