Create a Consistent Android SQLite WAL Snapshot Before Import

Direct answer

A live SQLite database in WAL mode is not necessarily represented by the main database file alone. Committed page changes can remain in the `-wal` file until checkpointing, while the `-shm` file supports the WAL index for concurrent access. Copying only the main file can omit committed state. Copying the main, WAL, and SHM files one at a time while writers or checkpoints continue can combine file versions from different moments. File presence is not a consistent-snapshot protocol. Prefer a documented SQLite backup mechanism that reads database state through SQLite rather than treating its files as unrelated blobs. The C Online Backup API uses `sqlite3_backup_init()`, one or more `sqlite3_backup_step()` calls, and `sqlite3_backup_finish()`. Each step reads the source under a shared lock only for that step; the source is not locked continuously for the operation. If another connection modifies the source between steps, the next step automatically restarts the backup; a write on the same source connection updates the destination at the same time. A consumer must handle `SQLITE_DONE`, retryable `SQLITE_BUSY`/`SQLITE_LOCKED`, fatal errors, and finalization according to that API. It creates a SQLite-consistent destination under the C API contract, but is not necessarily exposed by an Android `SQLiteDatabase` wrapper or Room. `VACUUM INTO` is another supported SQLite mechanism: it leaves the source unchanged and creates a consistent copy of logical content with a compacted, potentially different physical layout. Do not infer a precise wall-clock snapshot point from “transaction began” or the user tapping Export. In a `BEGIN DEFERRED` transaction, the BEGIN statement only disables automatic commit; the transaction does not actually start until the database is first accessed. If the first access is a SELECT, that read transaction establishes the historic view, which remains stable until it ends. An incremental Online Backup operation has a different source-change contract: an external write between steps causes a restart at the next step, whereas a write through the same source connection is copied through to the destination. Record the source connection, API path, step results, restart/retry events and completion status; claim no finer logical timepoint than the actual API and observations establish. SQLite snapshot objects and backup files are different artifacts. The snapshot API records a historical WAL-mode database state for connections that can open that snapshot, and its availability depends on SQLite build support such as `SQLITE_ENABLE_SNAPSHOT` and the required connection workflow. It is not itself a portable database backup file. An app must not persist a pointer-like snapshot object and imply it can be reopened after process death or on another device. Check compile flags and runtime availability before designing around it. Keep production source ownership with its owner. A consumer that merely needs to read a snapshot should not checkpoint, truncate the WAL, change journal mode, or rewrite the user’s database as a convenience. If the chosen API requires a stable connection or a backup operation, coordinate through the database owner and follow its API contract. Do not copy sidecar files separately, delete `-wal` or `-shm`, force a checkpoint without authorization, or block active writers outside the documented mechanism. A generated copy is app-owned output with its own access, retention, and deletion policy. A consistent snapshot still needs ordinary database validation. Open the destination through the intended SQLite driver, verify that the database opens read-only where possible, inspect `user_version` and required schema objects, and run appropriate integrity checks supported by the selected runtime. Keep validation distinct from migrations: a valid old schema may need a planned migration, while an invalid or truncated file must not be “fixed” by silently applying a schema change. Room migration tests begin from exported historical schemas and validate an app’s version path; they do not prove that a copied live database was transactionally consistent. Importing into an application database is a separate step. Define whether records are merged, replaced, or staged for review; check identity collisions, foreign keys, encryption/key availability, and ownership before modifying user data. Write into a separate candidate first, validate counts and critical relationships, then commit through the app’s database owner. If import fails, keep the existing database usable and the source copy unchanged. A one-file snapshot does not prove authorization to import its content or compatibility with a different Room schema. Test the exact Android driver and backup path with a writer that commits known rows during snapshot creation, a reader holding a stable transaction, WAL content not yet checkpointed, forced cancellation, disk-full handling, process interruption, and corrupted or wrong-schema input. Compare a logically consistent set of tables and transaction markers in the completed copy, not just file sizes or timestamps. Record whether backup restarts occurred and which final result was accepted. Synthetic databases should have no real notes, prompts, transcripts, health data, or model content. Do not use a manual three-file copy as a baseline and then infer a race-free protocol from one successful run. Do not call a `VACUUM INTO` output byte-identical to the source; vacuuming can produce a different layout while preserving database content under its contract. This guide is an import/snapshot method only. It makes no claim that Cove exports Room databases, that a particular Android driver exposes native backup APIs, or that a device has passed these tests.

A database export misses rows committed during WAL use

A local note database has WAL mode enabled. A background writer commits a note while another component copies the main database file and then its sidecars. The resulting folder contains three plausible files, but no component has proven they belong to one consistent database state.

Decision

Stop file-by-file copying. Use a backup path supported by the actual SQLite driver and coordinated by the database owner, then validate a separate destination before any import or migration.

Expected outcome

The accepted snapshot represents one SQLite-consistent view under the selected API’s contract; the live source remains under its owner and can continue serving existing readers.

Evidence to collect

The claim becomes reviewable only when the following evidence is attached to the same artifact and test run:

• Journal mode/driver/connection owner identity
• Backup step, restart, first-read, and VACUUM fixtures
• Independent destination validation and safe import state

A completed byte stream is not enough. Final placement, integrity, runtime load, and recovery must also pass.

Implementation workflow

1. Identify journal mode, driver, and source owner. Record the exact Android SQLite/Room driver, runtime SQLite version/build flags, journal mode, source connection owner, and intended destination. Do not infer C API availability from SQLite file format or a Room migration interface.
2. Select a supported consistent-copy mechanism. Choose the backup API exposed by the actual runtime, a supported wrapper, or `VACUUM INTO` when its documented behavior fits. Confirm locking, incremental steps, source-change handling, return codes, cancellation, and destination replacement semantics. If a `BEGIN DEFERRED` read is used, record the first database access that starts the read transaction; the BEGIN statement alone has not established a snapshot.
3. Run the operation through the database owner. Coordinate the snapshot with the component that owns the database connection. Do not independently copy main/WAL/SHM, delete sidecars, switch journal mode, or force checkpointing to simplify consumer logic.
4. Validate the separate destination. Open the completed copy with the intended driver, verify expected schema/version and transaction markers, and run the supported integrity checks. Distinguish a valid old schema needing migration from malformed or incomplete bytes.
5. Stage import and test concurrent outcomes. Keep the original source and current app database unchanged while a candidate is checked. Exercise committed writes during backup, stable reader transactions, restart/retry events, cancellation, disk-full and process interruption; publish only scoped logical-consistency evidence.

Keep each transition observable. A failure should identify the stage, artifact, runtime, and recovery action without logging private user content.

Acceptance criteria

• The exact WAL mode, SQLite version/build features, Android driver, source owner, and supported backup API are recorded; C API availability is not assumed from Room.
• The workflow never copies main, `-wal`, and `-shm` independently during active use or treats a transient snapshot object as a portable backup file.
• A DEFERRED read view starts at the first database access, not at the BEGIN statement; backup claims follow the exact source-connection change/restart contract and observed API result.
• The source is not checkpointed, truncated, journal-mode-switched, or rewritten by a read-only snapshot consumer without an explicitly authorized owner operation.
• Snapshot validation, Room schema migration, application data merge, and user authorization remain separate; failed imports leave the current database usable.

Decision table

Failure patterns to prevent

• Copying main/WAL/SHM independently while active
• Treating BEGIN DEFERRED as the snapshot instant
• Assuming Room exposes the SQLite C Online Backup API

Also prevent silent fallback, unpinned artifacts, missing cancellation, and conclusions that combine unlike configurations. Store unsuccessful runs alongside successful ones.

Minimum reproducibility record

Release checklist

• ☐ The primary query is answered without an unsupported number.
• ☐ Every artifact and runtime is pinned.
• ☐ The representative task and failure policy are explicit.
• ☐ Lifecycle, cancellation, cleanup, and fallback are tested.
• ☐ User-content and network boundaries are documented.
• ☐ Result wording applies only to the recorded configuration.
• ☐ The page links to raw method or evidence when results are added.

Sources and related evidence

• Android privacy and security
• SQLite — Write-Ahead Logging
• SQLite — Online Backup API overview
• SQLite C API — sqlite3_backup_step() and source-change behavior
• SQLite — VACUUM and VACUUM INTO
• SQLite — Transactions and DEFERRED read-transaction start
• SQLite — Database Snapshot C API
• Android Developers — Migrate Room database versions

Chinese deployment and troubleshooting content is organized in the .奇连 AI 端侧专题.

Frequently asked questions