Fix "database disk image is malformed" in SQLite
What SQLite's malformed database error means, how to check how bad it is, and how to recover the data with .recover, with real recovery numbers.
Quick Answer
Part of the database file is corrupt. Work on a copy, check how much is damaged, then rebuild a clean file with the .recover command:
cp app.db app-broken.db # never repair the only copy
sqlite3 app-broken.db "PRAGMA integrity_check"
sqlite3 app-broken.db .recover | sqlite3 app-recovered.db
sqlite3 app-recovered.db "PRAGMA integrity_check" # should print: ok
Copy the -wal and -journal files too if they exist, and stop the app writing to the file first.
What the error means
SQLite found a page in the file that doesn't have the structure it expects. That's result code 11, SQLITE_CORRUPT. The causes are almost always outside SQLite: a disk or power failure mid-write, the file being copied or synced while in use, two programs writing it through a network drive, or something overwriting bytes in the file.
| Client | What you see |
|---|---|
| sqlite3 CLI | Error: stepping, database disk image is malformed (11), exit status 11 |
| Python sqlite3 | sqlite3.DatabaseError: database disk image is malformed, sqlite_errorname == "SQLITE_CORRUPT" |
| Node better-sqlite3 | SqliteError, err.code === "SQLITE_CORRUPT" |
A related message, file is not a database (code 26, SQLITE_NOTADB), means the file's header isn't SQLite at all: a wrong path, an encrypted database opened without its key, or a file that was never a database. Recovery tools can't help with that one.
In VisuaLeaf
Measure the damage on a copy
VisuaLeaf won't repair a damaged file; recovery is a job for the sqlite3 shell, as shown below. What it helps with is seeing how much still reads, before and after:
- Make a copy of the .db file, along with any
-walfile, and open the copy, never the original. - In the SQL editor, run
PRAGMA integrity_check;. Each problem comes back as a row you can scroll and copy. - Run
SELECT COUNT(*)on each table to see which tables still read and which stop with the malformed error. - After running
.recover, open the new file the same way and compare the counts table by table.
Comparing row counts per table tells you what the recovery lost, which integrity_check alone doesn't. See the SQL Editor docs.
Step 1: check how bad it is
PRAGMA quick_check; -- faster, skips index-versus-table checks
PRAGMA integrity_check; -- full check; prints "ok" on a healthy file
Each problem line names a "Tree" with a number. That number is the rootpage of a table or index, so you can tell what's damaged:
SELECT type, name, rootpage FROM sqlite_schema;
If every damaged tree is an index, your rows are intact and recovery is lossless. If a table is damaged, some rows on the broken pages are gone.
What we found testing this
Setup: a 20,000-row table with one index (185 pages of 4 KB). We overwrote 4 pages with junk to damage the table, and separately damaged one index page.
| Recovery method | Table damaged | Only index damaged |
|---|---|---|
.recover | sqlite3 new.db | 19,413 of 20,000 rows, file checks ok | All 20,000 rows, file checks ok |
.dump | sqlite3 new.db | 0 rows: the dump ends in ROLLBACK; -- due to errors | Not tried; .recover worked |
.dump with that ROLLBACK changed to COMMIT | 19,413 rows | |
REINDEX, DROP INDEX, VACUUM INTO | Fails with error 11 | All three fail with error 11 |
- The common ".dump and reload" advice silently restores nothing. On damaged data,
.dumpwrote 19,413INSERTs and thenROLLBACK; -- due to errorsas its last line, so loading it creates no table at all. Use.recover, or change that last line toCOMMIT;. - Index-only damage doesn't break most queries, but you can't fix it in place.
SELECT count(*)and lookups through the damaged index both returned correct results. Onlyintegrity_checkshowed the damage, andREINDEX,DROP INDEXandVACUUM INTOall failed..recoverrebuilt a clean file with every row. - Corruption shows up as soon as a query touches the bad pages, even a plain
count(*). A database can look fine for weeks if the damaged pages are rarely read, so runPRAGMA quick_checkafter any crash.
Step 2: recover into a new file
sqlite3 app-broken.db .recover > recovered.sql
sqlite3 app-recovered.db < recovered.sql
sqlite3 app-recovered.db "PRAGMA integrity_check; SELECT count(*) FROM orders;"
.recover reads every page it can and skips what it can't. Rows it finds but can't place in a table go into a lost_and_found table, so check for one in the new file. Compare row counts with what you expect, then swap the recovered file in while the app is stopped.
Older sqlite3 builds don't have .recover. Install a recent SQLite command-line shell; it only needs to read the broken file.
Prevent it from happening again
- Back up with SQLite, not a file copy.
VACUUM INTO 'backup.db'or the CLI's.backup backup.dbmake a consistent copy while the app runs. Copyingapp.dbalone, without its-walfile, can produce a broken or out-of-date copy. - Keep the file off network and synced folders. Dropbox, OneDrive and SMB shares can write half a file or mix two versions.
- Never delete
-walor-journalfiles by hand. They hold writes that haven't reached the main file yet. - Leave
PRAGMA synchronousat its default unless you accept losing the database on a power cut.
Frequently Asked Questions
What does "database disk image is malformed" mean?
Part of the SQLite file is corrupt (SQLITE_CORRUPT, code 11). Some pages no longer have a valid structure, usually after a crash, a power loss, or the file being copied or synced while it was in use.
Can I recover a malformed SQLite database?
Usually most of it. Run sqlite3 broken.db .recover and pipe the output into a new database. In our test that kept 19,413 of 20,000 rows with table damage, and every row when only an index was damaged.
Why does restoring a .dump of a corrupt database give an empty database?
When .dump hits errors it ends the script with ROLLBACK instead of COMMIT, so the whole restore is undone. Use .recover, or change that last line to COMMIT.
What is the difference between malformed and "file is not a database"?
Malformed means the file is SQLite but some pages are damaged. "file is not a database" (code 26) means the header is not SQLite at all, for example a wrong file or an encrypted database.