Recovering a Malformed Database Image

A query on a production file just failed with database disk image is malformed — the text of SQLITE_CORRUPT. The engine hit a b-tree page it cannot make sense of, and every statement that touches the damaged region will now fail the same way. Panic responses make it worse: re-running the write, deleting the WAL, or restoring a backup taken after the fault began all either lose data or reinstall the corruption. The disciplined path is triage first — confirm the corruption and identify what kind it is — then a best-effort salvage into a new file, verified before it goes anywhere near production. This page walks that path for a single malformed file, as the recovery companion to Integrity Checking & Corruption Recovery within the Backup, Recovery & Data Integrity guide. Use it when detection — for instance the probe in Automating integrity_check in Python — has already flagged the file and no clean backup is at hand.

Figure — The salvage path: confirm and classify the fault, run .recover from the malformed file into a fresh database, verify the rebuild with integrity_check, then atomically swap it into place.

The malformed-image salvage and swap pipeline A four-stage left-to-right pipeline. First, confirm and classify the fault, distinguishing true page corruption from SQLITE_NOTADB. Second, run the .recover command to extract salvageable rows from the malformed file into a fresh empty database. Third, verify the rebuilt database with integrity_check. Fourth, atomically replace the production file with the verified rebuild. Confirm classify vs NOTADB .recover into fresh db integrity_check verify rebuild Atomic swap os.replace()

Diagnosis

First, confirm the file really is corrupt and is not merely the wrong file. SQLITE_CORRUPT and SQLITE_NOTADB present differently and demand different responses. Open read-only and run the full check — the same one detection uses — to enumerate the damage:

PRAGMA integrity_check;   -- lists broken pages/indexes; a single 'ok' row means it is NOT corrupt

If that yields a list of problems, you have genuine page corruption. If instead the file fails to open at all and the driver reports file is not a database (SQLITE_NOTADB), the first 16 header bytes are not the SQLite magic string: the file is truncated to zero, was never a database, is encrypted, or you are pointed at the wrong path. SQLITE_NOTADB is not a salvage problem — no amount of .recover will help — so rule it out before spending effort:

import sqlite3

def classify(db_path: str) -> str:
    try:
        conn = sqlite3.connect(f"file:{db_path}?mode=ro", uri=True)  # read-only: never mutate the evidence
        try:
            rows = [r[0] for r in conn.execute("PRAGMA integrity_check;").fetchall()]
            return "healthy" if rows == ["ok"] else "corrupt"
        finally:
            conn.close()
    except sqlite3.DatabaseError as e:
        msg = str(e).lower()
        if "not a database" in msg:      # SQLITE_NOTADB: wrong/encrypted/empty file, not corruption
            return "notadb"
        return "corrupt"                 # SQLITE_CORRUPT on open: header/root page unreadable

A notadb result means stop and check whether the path, an encryption layer, or an incomplete download is the real issue. A corrupt result means proceed to salvage. Note also which sidecar files exist: a stale or damaged -wal/-shm next to the main file can itself be the source of the malformed view, so record their state before touching anything.

Solution

The salvage tool is the sqlite3 CLI .recover command. Unlike .dump, which issues ordinary SELECTs and aborts the moment it hits a corrupt page, .recover walks the file page-by-page and extracts every row it can still read, emitting SQL that rebuilds the schema and data into a fresh database. You never repair the malformed file in place — you extract from it into a new one. Drive it from Python with subprocess, then verify the result before it replaces anything:

import os
import shutil
import sqlite3
import subprocess
import logging

logger = logging.getLogger("recover")


def recover_malformed(bad_path: str, live_path: str) -> None:
    # 1. quarantine the malformed file; keep it for forensics, do not delete.
    quarantine = bad_path + ".corrupt"
    shutil.copy2(bad_path, quarantine)

    # 2. pipe `.recover` from the bad file into a brand-new database. `.recover`
    #    is best-effort: it salvages readable rows and skips unreadable pages.
    rebuilt = live_path + ".rebuilt"
    if os.path.exists(rebuilt):
        os.remove(rebuilt)
    recovery_sql = subprocess.run(
        ["sqlite3", bad_path, ".recover"],
        capture_output=True, text=True, check=True,
    ).stdout
    subprocess.run(["sqlite3", rebuilt], input=recovery_sql,
                   text=True, check=True)

    # 3. verify the rebuild BEFORE swapping. A rebuild that is itself corrupt
    #    must never reach production.
    conn = sqlite3.connect(f"file:{rebuilt}?mode=ro", uri=True)
    try:
        conn.execute("PRAGMA journal_mode=WAL;")   # match production durability settings
        rows = [r[0] for r in conn.execute("PRAGMA integrity_check;").fetchall()]  # must be ['ok']
        if rows != ["ok"]:
            raise RuntimeError(f"rebuilt image still malformed: {rows[:5]}")
    finally:
        conn.close()

    # 4. atomic swap: os.replace is atomic on the same filesystem, so readers
    #    never observe a half-written file. Remove stale sidecars first.
    for suffix in ("-wal", "-shm"):
        stale = live_path + suffix
        if os.path.exists(stale):
            os.remove(stale)
    os.replace(rebuilt, live_path)
    logger.warning("recovered %s; malformed original kept at %s", live_path, quarantine)

The read-back integrity_check on the rebuilt file is the load-bearing step: .recover is best-effort and can produce a structurally sound database that is nonetheless missing whatever lived on the pages it could not read. Verifying before the os.replace guarantees you never swap one malformed file for another. When you do have a clean backup, prefer restoring it over salvage — the incremental route in Incremental Backups with the SQLite Backup API is designed to give you exactly that verified source.

Verification

Confirm the rebuilt database is both consistent and complete before trusting it. Consistency first — the rebuild must pass the same full check that the original failed:

conn = sqlite3.connect("file:/var/lib/app/data.db?mode=ro", uri=True)
try:
    assert conn.execute("PRAGMA integrity_check;").fetchone()[0] == "ok"   # rebuild is structurally sound
    assert conn.execute("PRAGMA foreign_key_check;").fetchall() == []      # referential integrity intact
finally:
    conn.close()

Then completeness — .recover is best-effort, so compare row counts against your last-known-good figures or a business invariant, because a clean check does not prove nothing was lost:

conn = sqlite3.connect("file:/var/lib/app/data.db?mode=ro", uri=True)
try:
    n = conn.execute("SELECT count(*) FROM readings;").fetchone()[0]
    assert n >= expected_min_rows, f"recovery dropped rows: {n} < {expected_min_rows}"
finally:
    conn.close()

If the full check returns ok, foreign_key_check is empty, and the row counts sit within tolerance, the rebuild is safe to keep. A shortfall in row count is your signal that pages were lost and that you should reconcile against a backup or upstream source rather than accept the gap silently.

Failure Modes & Gotchas

.dump aborts on the first bad page; .recover does not. Reaching for the familiar .dump on a corrupt file usually fails part-way: it runs ordinary SELECTs and the moment one crosses a malformed page it raises SQLITE_CORRUPT and stops, giving you a truncated, useless dump. .recover exists precisely for this case — it reads the file at the page level and salvages what .dump cannot reach. Use .dump for healthy databases; use .recover for malformed ones.

Copying the bad -wal/-shm alongside re-corrupts the rebuild. The write-ahead log and shared-memory index belong to the original file’s page layout. Carrying them over to a rebuilt or restored database — or leaving stale ones in place during the swap — lets SQLite replay frames against a file they no longer match, reintroducing corruption. Always remove the sidecars before the swap, as the routine above does, and let the engine create fresh ones on next open.

Salvage can silently drop the newest un-checkpointed frames. If corruption struck while committed data still sat in the WAL and had not been checkpointed into the main file, .recover reading the main database may miss those most-recent writes entirely. Where the WAL is still intact, checkpoint it into the main file first (PRAGMA wal_checkpoint(TRUNCATE) on a readable copy) so those frames are folded in before you salvage. Accept that the very latest transactions before a hard corruption event are the ones most likely to be unrecoverable, and reconcile them from an upstream source if one exists.

The SQLite command-line .recover documentation details the salvage command, and the sqlite3 module documentation covers the connection and error handling used above.