A script to fix corrupted home assistant database on macos

recover_ha_db.sh

#!/usr/bin/env bash
set -euo pipefail

REMOTE_USER=root
REMOTE_HOST=192.168.1.yourlocalip
REMOTE_DIR="~/homeassistant"
LOCAL_DIR="."

REMOTE="${REMOTE_USER}@${REMOTE_HOST}"

echo "Starting parallel jobs..."

# Will be filled by second_work and reused for cleanup
CORRUPT_FILE=""
FILE_NAME=""

# -------- First work (remote cleanup + stop HA) ----------
first_work() {
  echo "[1] Stopping Home Assistant and removing DB on remote..."

  ssh "$REMOTE" << 'EOF'
set -e
ha core stop
rm -f ~/homeassistant/home-assistant_v2.db
EOF

  echo "[1] Done."
}

# -------- Second work (copy corrupt DB, recover locally, send back) ----------
second_work() {
  echo "[2] Searching for corrupt DB on remote..."

  CORRUPT_FILE=$(ssh "$REMOTE" \
    'ls -1 ~/homeassistant/home-assistant_v2.db.corrupt* 2>/dev/null | head -n 1')

  if [[ -z "$CORRUPT_FILE" ]]; then
    echo "[2] No corrupt DB file found on remote!"
    exit 1
  fi

  FILE_NAME=$(basename "$CORRUPT_FILE")

  echo "[2] Found: $FILE_NAME"
  echo "[2] Copying to local directory..."

  scp "$REMOTE:$CORRUPT_FILE" "$LOCAL_DIR/"

  echo "[2] Recovering SQLite database..."
  sqlite3 "$LOCAL_DIR/$FILE_NAME" ".recover" | sqlite3 "$LOCAL_DIR/home-assistant_v2.db"

  echo "[2] Uploading recovered DB back to remote..."
  scp "$LOCAL_DIR/home-assistant_v2.db" \
      "$REMOTE:$REMOTE_DIR/home-assistant_v2.db"

  echo "[2] Done."
}

# Run both in parallel
first_work &
PID1=$!

second_work &
PID2=$!

# Wait for both to finish
wait $PID1
wait $PID2

echo "Both parallel jobs finished."

# -------- Final step ----------
echo "Starting Home Assistant again on remote..."
ssh "$REMOTE" "ha core start"

echo "Home Assistant started."

# -------- Cleanup ----------
echo "Cleaning up..."

# Delete local files
rm -f "$LOCAL_DIR/$FILE_NAME"
rm -f "$LOCAL_DIR/home-assistant_v2.db"

# Delete corrupt DB on remote
ssh "$REMOTE" "rm -f $CORRUPT_FILE"

echo "Cleanup done."
echo "All done."