SQLite inside applications: why an unsynchronised live-file copy can fail

About this article: AI-assisted writing, reviewed against the cited sources. This is source research, not lab validation. Check the stated versions and your own requirements before making changes.

SQLite is an embedded database. Instead of a separate server, the application reads and writes a single file on disk. Accounting packages, monitoring agents, browsers, mobile apps, network appliances and many small line-of-business tools use it.

Because it looks like an ordinary file, it often ends up in a file-level backup with everything else. The SQLite project's own documentation explains when that works and when it does not. This guide sets out the files involved, the safe methods SQLite provides, and how to check a backup without touching the live database.

Why it matters

A file-level backup of a server or workstation copies files as they are found. The SQLite page "How To Corrupt An SQLite Database File" describes the risk directly: "Systems that run automatic backups in the background might try to make a backup copy of an SQLite database file while it is in the middle of a transaction. The backup copy then might contain some old and some new content, and thus be corrupt."

Such a copy can look normal for months until someone tries to open it after a loss (our reading). The application owner may not know SQLite is inside the product at all, so the backup administrator may be the only person able to spot the risk (our understanding).

The files that make up one database

The main database file is not always the whole story.

Rollback journal. In the traditional mode, SQLite writes recovery information to a journal file while a transaction runs. The corruption page explains: "while performing a transaction, information necessary to recover the database following a crash or power failure is stored in auxiliary journal files." A rollback journal left after an interrupted update can be hot, meaning that recovery is required; not every journal file is hot. The page adds: "The journal files have the same name as the original database file with the addition of -journal or -wal suffix."

Write-ahead log (WAL). In WAL mode, changes are appended to a separate file first. The WAL page says the file is usually named after the database file with a -wal suffix. Committed changes stay in that file until a checkpoint moves them back; the page describes this as "Moving the WAL file transactions back into the database". By default, "SQLite does a checkpoint automatically when the WAL file reaches a threshold size of 1000 pages."

So at any moment, recent committed data may exist only in the -wal file (our reading of the checkpoint description). A backup that copies the main file but skips or mistimes the -wal file can miss it.

Shared-memory file. WAL mode also uses an index file. The temporary files page says it "is located in the same directory as the database file" and carries the database name with -shm appended.

The documentation is firm about keeping these files together: "SQLite must see the journal files in order to recover from a crash or power failure. If the hot journal files are moved, deleted, or renamed after a crash or power failure, then automatic recovery will not work and the database may go corrupt."

When a plain file copy is acceptable

The same page gives the condition: "It is also safe to make a copy of an SQLite database file as long as there are no transactions in progress while the copy is taking place. If the previous write transaction failed, then it is important that any rollback journal (the -journal file) or write-ahead log (the -wal file) be copied together with the database file itself."

In practice, coordinate every process using the database so that no transaction is in progress throughout the file copy, usually by cleanly stopping the application and other database users. Preserve matching journal/WAL files when present; stopping only one writer does not establish that condition (our recommendation). A backup tool that cannot guarantee "no transactions in progress" should not be treated as producing a consistent SQLite copy on its own (our reading).

A note on network shares

The WAL page lists a limitation: "All processes using a database must be on the same host computer; WAL does not work over a network filesystem." If an application keeps a WAL-mode database on a network share, the backup is the smaller worry (our reading).

The three safe methods SQLite documents

The corruption page lists approaches that "will work even on a live database": the sqlite3_rsync utility, VACUUM INTO, and the backup API. The command-line .backup command uses the same idea as the backup API (our understanding, based on the CLI help, which describes .backup as backing up a database to a file).

The online backup API

The backup API page explains why it exists. The old method was to take a shared lock and copy the file with an external tool, but it had shortcomings, including: "If a power failure or operating system failure occurs while copying the database file the backup database may be corrupted following system recovery."

With the API, "The copy operation may be done incrementally, in which case the source database does not need to be locked for the duration of the copy, only for the brief periods of time when it is actually being read from." The result: "The effect of completing the backup call sequence is to make the destination a bit-wise identical copy of the source database as it was when the copying commenced."

There is one behaviour to know about. The API reference says: "If the source database is modified by an external process or via a database connection other than the one being used by the backup operation, then the backup will be automatically restarted by the next call to sqlite3_backup_step()." The backup page warns: "If the backup process is restarted frequently enough it may never run to completion". A busy database with constant writes from other processes can make a backup job run for a long time or never finish (our reading).

The reference also notes that a step may return SQLITE_BUSY, and "In this case the call to sqlite3_backup_step() can be retried later."

VACUUM INTO

VACUUM INTO writes a fresh, compacted copy. The VACUUM page says: "If the INTO clause is included, then the original database file is unchanged and a new database is created in a file named by the argument to the INTO clause." It describes it as "an alternative to the backup API for generating backup copies of a live database."

Trade-offs, in the project's words:

• "The advantage of using VACUUM INTO is that the resulting backup database is minimal in size and hence the amount of filesystem I/O may be reduced."
• "Also, all deleted content is purged from the backup, leaving behind no forensic traces."
• "On the other hand, the backup API uses fewer CPU cycles and can be executed incrementally."

Two practical limits:

• "The file named by the INTO clause must not previously exist, or else it must be an empty file, or the VACUUM INTO command will fail with an error." Use a unique staged output for each run, preserving the last known-good backup. Check successful completion, validate the new output, and confirm its freshness and inclusion in the protected backup before applying retention. Never clear the only good copy to make room for the next attempt; do not select incomplete output after a failed run (our recommendation).
• "The VACUUM INTO command is transactional in the sense that the generated output database is a consistent snapshot of the original database. However, if the VACUUM INTO command is interrupted by an unplanned shutdown or power loss, then the generated output database might be incomplete and corrupt." The page adds that with synchronous at NORMAL or FULL, SQLite syncs the output to disk after writing it.

The "no forensic traces" point cuts both ways. If an investigation might need deleted records, a vacuumed copy will not have them (our reading).

sqlite3_rsync

The corruption page says sqlite3_rsync is "available beginning with SQLite 3.47.0 (2024-10-21) and later" and "will make a copy of a live SQLite over SSH using a bandwidth-efficient protocol." It is useful for copying to another machine (our reading). Check its documentation and version requirements before use.

Choosing a method

None of these methods is chosen by the backup software alone. The application must allow it, or someone must schedule a step that runs alongside the application (our reading).

• The vendor provides an export or backup command: use it first, because it may also capture settings or files outside the database (our recommendation).
• You control a script on the host: .backup or VACUUM INTO to a new file, then let the file-level backup copy that output (our recommendation).
• The database is busy with many writers: watch backup API runs for restarts that never finish; VACUUM INTO may be more predictable, at a CPU cost (our reading).
• You can stop the application: a stopped application with no transactions in progress allows a plain copy, including any -wal or -journal file.

Inspection and scratch-copy checks

Inspection and database recovery have different effects; a read-only open is not sufficient for every restored file. Run the checks in a Linux shell (bash) or Windows PowerShell, with the sqlite3 command-line tool installed. Do not run them against a database on network storage, and agree a time with the application owner (our recommendation).

1. Find SQLite files and their companions. List the application's data directory and note any files ending in -wal, -shm or -journal next to a database file. WAL file size alone does not measure uncheckpointed data or recovery lag: SQLite normally recycles the file after checkpointing instead of truncating it. Record the size as an observation and ask the application owner to assess checkpoint progress, writers and long-running readers through supported diagnostics.

2. Check the journal mode on the live file, opened read-only. The CLI documentation says "The --readonly option opens the database in read-only mode. Write will be prohibited."

sqlite3 -readonly app.db "PRAGMA journal_mode;"

The PRAGMA documentation says this form "queries the current journaling mode". One caution: the WAL page says a read-only WAL database can be opened if "The -shm and -wal files already exist and are readable" or if "There is write permission on the directory containing the database so that the -shm and -wal files can be created." Even a read-only open may create those companion files (our reading). If that is not acceptable, check a copy instead.

3. Preserve the restore, then check a separate scratch copy. Keep the original restored files and matching journal/WAL files intact as evidence. Make a separate writable copy in an isolated scratch folder. A hot rollback journal requires SQLite to write recovered pages before normal reads; a read-only open can legitimately fail until that recovery completes. Let the compatible application or SQLite perform supported crash recovery on the writable scratch copy, with its matching companions present. Close that connection cleanly, then run the read-only integrity check on the recovered scratch database:

sqlite3 -readonly restored.db "PRAGMA integrity_check;"

The PRAGMA page says this "does a low-level formatting and consistency check of the database" and that "If pragma integrity_check finds no errors, a single row with the value 'ok' is returned." PRAGMA quick_check "does most of the checking of PRAGMA integrity_check but runs much faster."

A result of ok reports no errors found by that integrity check; it does not prove freshness, application completeness or a successful application restore. An open failure caused by permissions, locks or required crash recovery is not itself an integrity-check report of corruption. Preserve the exact error and investigate on the scratch copy.

Safe restore rehearsal

Use an authorised, isolated test machine with the same application version and a non-production copy of the data (our recommendation).

1. Create a known baseline in a synthetic test dataset: record expected IDs, counts and application facts at a defined recovery point. Hold the fixture stable across the export, or use an application-supported boundary that can be tied to it. A current production count is not a valid baseline for an older backup.
2. Produce and protect a unique export with the supported method. Record completion status, errors, output identity and time, and verify that the exact fresh output and required application files were included in the protected backup. Retain the last good copy if any step fails.
3. Restore that protected export to the isolated machine. For a raw file-copy test, preserve the restored evidence and matching journal/WAL files, then perform any required crash recovery on a separate writable scratch copy as described above.
4. Check the recovered scratch database with PRAGMA integrity_check, then start the compatible test application and compare facts with the baseline for the same recovery point.
5. Exercise the live-copy method on the test machine under a synthetic write load. Check operation completion and verify known committed test records against the documented snapshot boundary, rather than a later count from the running source.
6. Record the achieved recovery point, any data loss relative to the intended point, and elapsed time. Separate real loss from a comparison against the wrong baseline.

Diagnosing what you find

• integrity_check reports errors on the backup: the copy was taken mid-transaction; the -wal or -journal file was missed or mismatched; the source was already damaged; or storage or transfer corrupted the copy.
• The restored data is older than expected: recent commits were still in a -wal file the backup did not capture; the safe copy step failed silently; or the file-level backup ran before the copy finished.
• The backup API job never finishes: other processes keep writing, so the copy restarts; or SQLITE_BUSY errors are not being retried.
• VACUUM INTO fails each night: the target file already exists and is not empty; there is not enough disk space; or the script has no write permission to the target.

If a database is corrupt, the CLI's .recover command "attempts to reassemble the database based on data extracted directly from as many database pages as possible." Run it only on a copy, and treat its output as salvage, not a backup.

Worked example (illustrative)

The following is invented. A small medical practice uses a booking application that stores data in SQLite on a Windows server. The nightly file-level backup copies the data folder. A rehearsal restores the copy, and the application opens, but the last two days of appointments are missing. The database is in WAL mode, and the backup copied the main file at a moment when recent commits sat in the -wal file.

In the invented corrected workflow, the vendor-supported export writes to a unique dated file. Each run must report success, pass freshness and output checks, and have its exact output recorded in the protected backup before the workflow reports success; failures retain the previous good copy and raise an error. On an isolated test system with synthetic appointment data, the team then restores that exact protected export, completes any required scratch-copy recovery, checks integrity and confirms the appointment IDs and counts recorded for its recovery point. That is the hypothetical acceptance result; quarterly rehearsals and routine checks maintain it. No author-run lab test is claimed.

Risks and limits

• Each application may hold data outside SQLite, such as attachments or configuration, that also needs a backup.
• Vendors may forbid outside access to their database file; check before running anything.
• No command in this guide was run in a lab. Test first in an isolated environment.

Sources

All accessed 14 November 2026.

• SQLite, SQLite Backup API: https://www.sqlite.org/backup.html
• SQLite, Online Backup API (C interface): https://www.sqlite.org/c3ref/backup_finish.html
• SQLite, Write-Ahead Logging: https://www.sqlite.org/wal.html
• SQLite, VACUUM: https://www.sqlite.org/lang_vacuum.html
• SQLite, How To Corrupt An SQLite Database File: https://www.sqlite.org/howtocorrupt.html
• SQLite, Command Line Shell For SQLite: https://www.sqlite.org/cli.html
• SQLite, Pragma statements: https://www.sqlite.org/pragma.html
• SQLite, Temporary Files Used By SQLite: https://www.sqlite.org/tempfiles.html
• SQLite, File Locking And Concurrency In SQLite Version 3: https://www.sqlite.org/lockingv3.html

Related future topics

• Finding the embedded databases inside commercial applications
• Pre-backup scripts: running application exports before file-level backups
• Checking backup freshness, not just backup success

This article was originally published on 14 November 2026.