Last week I had some strange issue with my server, Home Assistant went into recovery mode due to ip_bans.yaml containing some duplicate entries (strangely those entries where over a year old, so they could not be there before). Even my automations.yaml got corrupted, with entire lines replaced with some random characters. The issue corrupted my database as well.
I was trying to recover the data based on this guide here Help with corrupt databases initially it looked successful, PRAGMA integrity_check; found a few error that I have subsequently corrected with REINDEX;, after examining the database in an sql client looked like everything is alright, containing data since 2021.
However home asisstant does not display any of the old data…
I have daily backups of the database, but copying over an old version also does not seem to help,initally it works, then it gets flagged as corrupted for some reason.
The only database related errors in the logs seems to be these:
2024-07-29 23:22:29.120 WARNING (Recorder) [homeassistant.components.recorder.util] The system could not validate that the sqlite3 database at //config/home-assistant_v2.db was shutdown cleanly
2024-07-29 23:22:29.145 WARNING (Recorder) [homeassistant.components.recorder.util] Ended unfinished session (id=32 from 2024-07-23 03:45:38.770773)
2024-07-29 23:22:29.145 WARNING (Recorder) [homeassistant.components.recorder.util] Ended unfinished session (id=561 from 2024-07-19 18:38:44.075342)