Last active
December 2, 2022 14:39
-
-
Save skl/4dcf37c08a2c323c44e717840a8b4316 to your computer and use it in GitHub Desktop.
Repair SQLite DB [macOS]
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| #!/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." |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
I needed this for a project where I was getting the following error:
I based it on
sqlite3 file.db 'PRAGMA quick_check;'and the fact that if you use.dumpon a corrupted file, you can still get most of the data. However the dump contains a final line ofROLLBACK;which aborts any import attempt, so this script replaces that last line withCOMMIT;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 runsfile file.dbon each input and looks forSQLite 3.xin order to avoid processing incompatible file types.Corrupted database files will be moved to a directory called
corruptedin the same directory as where the script is executed from.Tested on macOS Mojave but it looks portable to me.
YMMV.