sqlite3_backup WAL snapshot lacks exposed frame number for application-level verification

The Short Answer

No, there is no supported mechanism in the sqlite3_backup API, VACUUM INTO, or standard file-copy methods to retrieve the WAL frame number or transaction ID associated with a snapshot. These identifiers are internal to the SQLite pager and are not exposed to the application layer.

Explanation of Behavior

The sqlite3_backup_init function initiates a read transaction on the source database. In WAL mode, this creates a snapshot of the database at a specific point in time. While SQLite uses frame numbers and salts internally to maintain this consistency, the API only exposes page-level progress (via sqlite3_backup_remaining), not the logical transaction boundary. Because the backup process is designed to be transparent to the application, it does not surface the underlying WAL metadata.

Comparison of Methods

Method	Consistency	Exposes TxID/Frame?	Note
sqlite3_backup	Snapshot	No	Safe for concurrent use.
VACUUM INTO	Snapshot	No	Creates a fresh, defragmented file.
Locked File Copy	Physical	No	Requires exclusive lock; blocks writers.

Recommended Pattern for Verification

To establish a verifiable link between a backup and a logical point in time, you must embed a marker directly into the database schema. This moves the verification from the transport layer (WAL frames) to the data layer (rows).

1. Create a Version Table: Maintain a table (e.g., sys_backup_marker) with a single row containing a unique identifier, such as a UUID or a monotonically increasing version number.
2. Atomic Marker Update: In a transaction, update this marker to the desired value immediately before initiating the backup.
3. Initiate Backup: Call sqlite3_backup_init using the same connection or ensuring no other writer can intervene between the marker update and the backup start.
4. Post-Restore Verification: After restoring the backup, query the sys_backup_marker table. If the restored value matches your expected marker, the backup is logically complete relative to that transaction.

Verification Example

-- 1. Set the marker
BEGIN TRANSACTION;
UPDATE sys_backup_marker SET version_id = 'backup_2026_10_12_01';
COMMIT;

-- 2. Perform sqlite3_backup_init() here

-- 3. Verify after restore
SELECT version_id FROM sys_backup_marker;
-- Expected: 'backup_2026_10_12_01'

Assumptions and Constraints

This approach assumes you have write access to the source database. If the database is read-only, you cannot use internal markers and must rely on external metadata (such as the timestamp of the last known successful write) which is inherently less precise. Note that PRAGMA integrity_check only confirms that the file is not corrupted; it cannot tell you if a specific transaction was included.

Diagnostic Detail Needed: Are you performing backups on a read-only database handle? If so, the marker-row pattern is not viable, and an alternative synchronization strategy would be required.