SQLite File Copied During a Write Passes AltSql Restore and Fails the SQLite Integrity Check
Most case studies show a tool doing its job. This one shows AltSql Restore 0.5.0 passing a backup it should have failed, because that’s what the recorded run says, and because the way the backup was made is common enough to matter.
The database is Chinook, the public sample database of a digital music store, as its project ships it for SQLite: one file of 1,007,616 bytes with 11 tables and 12 indexes. In this test an application keeps it as app/chinook.db in SQLite’s default journal mode, and the nightly backup is a plain cp of that file. Every night ended with altsql-restore checking the copy, and with SQLite’s own PRAGMA integrity_check run on the same copy for comparison. The three nights ran one after another on 31 October 2026.
| Night | How the copy was made | AltSql Restore | SQLite integrity check |
|---|---|---|---|
| 1 | cp while nothing was writing |
PASSED | ok |
| 2 | cp half-way through the nightly import |
PASSED | 100 errors |
| 3 | sqlite3 .backup during the same import |
PASSED | ok |
Night 2: The Copy Taken Mid-Import
The application’s nightly import adds 20,000 invoices, each with one invoice line, in a single transaction. A transaction that size doesn’t fit in SQLite’s page cache, so SQLite starts writing changed pages into the database file before the commit and saves the original pages in a journal file beside it. Half-way through, the folder looked like this:
2072576 chinook.db
71192 chinook.db-journal
That’s the moment cp copied chinook.db. It didn’t copy the journal, and without the journal nothing can undo the pages the import had already written.
Restore read the copy page by page and passed it:
Invoice 469 412 +13.8% a839db69b11476a4 changed
InvoiceLine 2,337 2,240 +4.3% 1128006bade2cbf7 changed
Checks:
- SQLite file: 246 pages of 4096 bytes, 11 tables, 12 indexes walked page by page, 0 free pages
Verdict: PASSED
SQLite’s own check disagreed:
$ sqlite3 backups/night2.db 'PRAGMA integrity_check'
row 413 missing from index IFK_InvoiceCustomerId
row 414 missing from index IFK_InvoiceCustomerId
row 415 missing from index IFK_InvoiceCustomerId
...
It stops listing at 100 errors, and it reached 100. The two tools didn’t even agree on the counts. Restore walked the invoice table and found 469 rows, 57 of them from the import that never committed. SQLite counted 412 invoices and 2,155 invoice lines through its indexes. Neither pair matches any state the database was ever in at a commit.
Why It Passed
The file’s header still described the last committed state, 246 pages, because SQLite rewrites the header at commit time. Restore checks that every page in those 246 is the right kind, that every cell and payload sits inside its page, that no page is reached twice and that the free list adds up. All of that held. The table pages and the index pages were each valid on their own. They just didn’t describe the same rows, and Restore 0.5.0 doesn’t compare an index with its table.
It was logged in the project notes as a finding from these case studies, with the files to reproduce it. A check that each index holds as many entries as its table has rows would have caught this copy. Release 0.5.1 adds that check, see below.
There’s a second cost. A run that passes becomes the baseline, so with 0.5.0 night 3 was compared with the 469 invoices of the broken copy. The counts the history keeps are only as good as the backups it saw.
The Fixed Release Fails This Copy
AltSql Restore 0.5.1 came out the same day. Its SQLite reader now makes three of the checks PRAGMA integrity_check makes: every index must hold one entry per row of its table (a partial index may hold fewer, never more), rowids must rise, and every page must belong to a table, an index or the free list. The three nights were run again with the released 0.5.1 binary, the same script and the same files. The night 2 copy had the same bytes as before (SHA-256 dae1722f...), and this time:
Invoice 469 412 +13.8% a839db69b11476a4 changed
InvoiceLine 2,337 2,240 +4.3% 1128006bade2cbf7 changed
Verdict: FAILED
- the restore failed: backup.sqlite: damaged SQLite file (table Invoice has 469 rows, its index IFK_InvoiceCustomerId has 412 entries: copied in the middle of a write?)
exit status: 1
Nights 1 and 3 still passed, and the history now reads PASSED, FAILED, PASSED. Since a failed run never becomes the baseline, night 3 was compared with night 1, the last copy that was whole.
Night 3: The Safe Way to Copy
The import ran again, and this time the backup used SQLite’s own backup command:
sqlite3 app/chinook.db ".timeout 120000" ".backup backups/night3.db"
It waited for the import’s lock and returned once the transaction had committed, 2 seconds later in this run. The copy held all 20,412 invoices and 22,240 invoice lines, both tools agreed on both counts, and the integrity check said ok.
How to Copy a Live SQLite File
Copy a live SQLite database with sqlite3 .backup or VACUUM INTO, never with cp alone, whichever tool checks it afterwards. If an application must be copied with cp, stop it first. In WAL mode the same rule applies for a different reason: the newest commits live in the -wal file, and the manual says to checkpoint before copying. With 0.5.1 a torn copy like this one fails, but a check can only report the damage, not undo it: the backup from that night is still lost.
The run is recorded under engines/restore/results/case-studies/3-sqlite-mid-write: the writer that pauses the import half-way so the copy lands at a known moment, both tools’ output for each night, and the reports. The folder now holds the 0.5.1 run; the 0.5.0 run quoted above is in the repository’s history, in the commit that first added the case studies.