Home Assistant Corrupt DB Merge

Recover long-term statistics from a corrupt Home Assistant database by merging them into a fresh one.

If your home-assistant_v2.db (SQLite) became corrupted and you've already started over with a fresh database, this tool merges the historical statistics, statistics_short_term, and statistics_meta rows from the old/corrupt DB into the new one — so years of long-term history aren't lost.

Per Home Assistant's data model, the recovered rows surface as long-term statistics only — visible via "show more" on cards/entities, not in the normal recorder/history views.

Requirements

• Python 3.10+ (stdlib only — no pip install).
• Tk for the GUI: python3-tk on Debian/Ubuntu. The CLI works without Tk.

Install

Clone the repo or just download ha_corrupt_db_merge.py. There's nothing to build.

git clone <this-repo>
cd ha-corrupt-db-merge

Usage

GUI

python3 ha_corrupt_db_merge.py

A single window opens: pick the old (corrupt) DB, pick the new (fresh) DB, optionally tick the entities whose running sum should be rebased onto the old totals (energy meters, gas counters, etc.), then click Run. Progress and a log stream into the window.

CLI

# Basic merge
python3 ha_corrupt_db_merge.py --old-db OLD.db --new-db NEW.db

# Rebase running sums for every has_sum=1 entity
python3 ha_corrupt_db_merge.py --old-db OLD.db --new-db NEW.db --sum-all

# Rebase running sums for specific entities
python3 ha_corrupt_db_merge.py \
    --old-db OLD.db --new-db NEW.db \
    --sum-entity sensor.energy_meter \
    --sum-entity sensor.gas_meter

# List entities eligible for sum rebasing
python3 ha_corrupt_db_merge.py --new-db NEW.db --list-sum-entities

# Custom output path
python3 ha_corrupt_db_merge.py --old-db OLD.db --new-db NEW.db -o merged.db

Run python3 ha_corrupt_db_merge.py --help for the full flag list.

How it works

1. Copies the new DB to a working file alongside it (<stem>_new-<unix-ts>.db). The original input files are never modified.
2. Replaces statistics and statistics_meta in the working file with the rows from the old DB (column intersection — schema differences between HA versions are handled gracefully).
3. For each entity you selected for sum rebasing: reads the last sum/state from the old DB and the first sum/state from the new DB, then computes an offset so the new running total continues from where the old one left off.
4. Walks the new DB's statistics_meta row by row, updating existing entries or inserting new ones, and records old→new id mappings.
5. Copies the new DB's statistics_short_term and statistics, rewriting metadata_id via the mapping and rebasing sum for selected entities.

The result is a single SQLite file you can drop in as your new home-assistant_v2.db.

Recovering your stats

1. Stop Home Assistant.
2. Back up your current (working) DB somewhere safe.
3. Run this tool with the corrupt DB as --old-db and the current DB as --new-db.
4. The output file lands next to your new DB as <name>_new-<timestamp>.db.
5. Replace home-assistant_v2.db in your HA config directory with the output (rename it accordingly).
6. Start Home Assistant. Check entities for the merged history under "show more" → long-term statistics.

If anything looks off, just put your backup back — the tool never touched it.

Notes

• Sum rebasing is opt-in per entity. It's only useful for monotonically-increasing meters where you want one continuous running total across the gap. For non-cumulative sensors (temperature, humidity, etc.) you don't need it.
• Schema differences are tolerated. HA's statistics_meta schema has gained columns over the years (mean_type, mean_weight, unit_class, etc.). The tool copies by column intersection and logs warnings for missing/extra columns.
• Cancellation is cooperative. The GUI's Cancel button (or Ctrl-C in CLI) interrupts at safe boundaries between SQL operations. Bulk copies inside a transaction run to completion or roll back atomically — no torn state.

Disclaimer

This tool reads from your databases but only writes to a fresh copy of the new DB. Even so: back up everything before you start. Database recovery is inherently risky, and your data is your responsibility.

License

MIT