SQLite WAL and SHM files: what to copy and keep
Keep the SQLite -wal file with the database while it’s active. It can hold committed changes that haven’t reached the main file yet. Copying only the main database can leave those changes behind.
The three files represent one working database
In WAL mode, changes are appended to a write-ahead log. The main file is updated through checkpointing. The -shm file supports coordination through a shared-memory index. Whether the main file is up to date depends on the open connections and checkpoint progress. You can’t tell from the filenames alone.
SQLite's WAL reference is the authority for these lifecycle rules. Treat the files as database-managed state rather than disposable caches.
Choose a consistent snapshot
Use the owning application's export or backup function when it provides a database snapshot. Otherwise use a SQLite-aware backup method. SQLite documents its Online Backup API; compatible tools can use it to create a consistent destination while respecting the database engine.
Copying the main file and then the WAL at different moments during active writes is not the same as making an atomic snapshot. If you plan a filesystem copy, coordinate a clean shutdown of all writers and follow the application's documented backup procedure.
Investigate a surprisingly large WAL
Record which application owns the database and whether long-lived readers or writes are active. A file's size alone is not a reason to delete it. Checkpoint decisions belong with the application's transaction and maintenance strategy. Avoid experimenting with checkpoint or journal-mode commands on the sole production copy.
Verify the inspection copy
Open the snapshot under a new connection, check its file path, list tables, and compare a few known recent records. Record when the snapshot was made and how it was produced. These details help explain why a copied database differs from the current application screen.
plainDB can inspect a local SQLite database and query its schema. That ability does not make a casual copy of live files consistent. Prepare the snapshot first, then use the viewer for your investigation. If the goal is disaster recovery, also test that your application's normal startup and workflows work against a restored copy.
Continue reading
SQLite backup workflow · Inspect the copied schema
For the desktop workflow, compare plainDB features on Mac and Windows or download the 14-day trial.