Can SQLite backup restoration guarantee exact page count parity after incremental steps?

No, SQLite backup restoration cannot guarantee exact page count parity if the source database is modified during the incremental backup process. The sqlite3_backup API captures a consistent snapshot of the database as it existed when the backup began; if the source adds or deletes pages while the incremental steps are running, the destination will reflect the state of the source at the moment of initialization. ### The Mechanics of Incremental Backup When you use sqlite3_backup_step(n), SQLite copies a specified number of pages from the source to the destination. If the source is live, the API handles concurrency by potentially re-copying pages that changed during the backup process. However, the final page count of the destination will only match the source's page count at the time sqlite3_backup_init was called. ### The Impact of WAL Mode If the source is in Write-Ahead Logging (WAL) mode, the main database file may not contain the most recent data, which resides in the `-wal` file. To ensure the backup includes all committed transactions, you must perform a checkpoint before starting the backup:

PRAGMA wal_checkpoint(FULL);

Without this, the backup might only capture an older state of the database, leading to a discrepancy between the expected "live" state and the restored file. ### Verification and Integrity To confirm the destination is valid after an incremental backup, follow these steps:

• Capture Source Count: Run PRAGMA page_count; on the source immediately before initialization.
• Execute Backup: Loop sqlite3_backup_step() until the function returns SQLITE_DONE.
• Verify Destination Count: Run PRAGMA page_count; on the destination. If it does not match the source count, the source was modified during the process.
• Integrity Check: Run thePRAGMA integrity_check; on the destination to detect structural corruption or missing pages that might have occurred during the copy.

Summary of Assumptions

This explanation assumes you are using the standard sqlite3_backup API. If you are performing manual file-level copies (e.g., cp while the database is active), you cannot guarantee parity or integrity without locking the database or using the backup API.