Repair SQLite DB [macOS]

repair-sqlite-db.sh

#!/bin/bash
set -e

function ph_is_installed() {
    command -v $1 > /dev/null 2>&1 && return 0 || return 1
}

if ! ph_is_installed sqlite3; then
    if [ "Darwin" != $(uname) ]; then
        echo "This script is currently only compatible with macOS"
        exit 1
    fi

    if ! ph_is_installed brew; then
        echo "Homebrew package manager not found, installing..."
        /usr/bin/ruby -e "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install)"
    fi
    echo "sqlite3 not found, installing..."
    brew install sqlite
fi

if ! ph_is_installed sqlite3; then
    echo "Could not find sqlite3!"
    exit 1
fi

if [[ $# -eq 0 ]]; then
    echo "Usage: $0 file1.db [file2.db] [file3.db] [...]"
    exit 1
fi

while (( "$#" )); do
    DB="$1"
    shift

    if [ ! -f "$DB" ]; then
        echo "$DB: is not a file"
        continue
    fi

    if ! file "$DB" | grep 'SQLite 3.x'; then
        echo "$DB: Ignored invalid SQLite 3.x file"
        continue
    fi

    if sqlite3 "$DB" 'PRAGMA quick_check;' | grep 'ok' &>/dev/null; then
        echo "$DB: quick_check OK!"
        continue
    fi

    echo "$DB: Repairing..."
    sqlite3 "$DB" ".dump" > "${DB}.sql"
    if [ ! -f "${DB}.sql" ]; then
        echo "$DB: Export failed!"
        continue
    fi

    if tail -n1 "${DB}.sql" | grep ROLLBACK &>/dev/null; then
        sed -i '' 's/primary key autoincrement//g' "${DB}.sql"
        sed -i '' '/sqlite_sequence/d' "${DB}.sql"
        sed -i '' '/^CREATE INDEX /d' "${DB}.sql"
        sed -i '' '/^ROLLBACK/d' "${DB}.sql"

        echo 'COMMIT;' >> "${DB}.sql"
        cat "${DB}.sql" | sqlite3 "${DB}.repair"
        if [ ! -f "${DB}.repair" ]; then
            echo "$DB: Import failed!"
            continue
        fi
        rm "${DB}.sql"

        mkdir -p corrupted
        mv "${DB}" corrupted/"${DB}.corrupt"
        mv "${DB}.repair" "${DB}"
        echo "$DB: Repaired!"
        continue
    else
        echo "$DB: does not appear to be corrupted, ignoring..."
        rm "${DB}.sql"
        continue
    fi
done

echo
echo "Finished processing all files."

I needed this for a project where I was getting the following error:

• Error: database disk image is malformed

I based it on sqlite3 file.db 'PRAGMA quick_check;' and the fact that if you use .dump on a corrupted file, you can still get most of the data. However the dump contains a final line of ROLLBACK; which aborts any import attempt, so this script replaces that last line with COMMIT; in the hope this will get some data imported (YMMV).

To run on a bunch of files, pass in a glob argument like ./repair-sqlite-db.sh *.db. The script runs file file.db on each input and looks for SQLite 3.x in order to avoid processing incompatible file types.

Corrupted database files will be moved to a directory called corrupted in the same directory as where the script is executed from.

Tested on macOS Mojave but it looks portable to me.

YMMV.