Morning guys I hope you’re having a great Monday.
I run home assistant unsupervised on docker/portainer on Debian. I use duplicati for backups.
Last week I restored a backup but stupidly didn’t stop home assistant first so I corrupted the database file. I didn’t notice for a few days but the energy data was gone. This is the only thing I really care about seeing long history for.
I have been trying to restore the database home-assistant_v2.db from before the corruption. The problem I had is that shortly after restart HA would not like it and create a new db file with a log like this…
Logger: homeassistant.components.recorder.util
Source: components/recorder/util.py:286
Integration: Recorder (documentation, issues)
First occurred: 11:59:13 AM (1 occurrences)
Last logged: 11:59:13 AM
The database at //config/home-assistant_v2.db is corrupt or malformed
Traceback (most recent call last):
File "/usr/src/homeassistant/homeassistant/components/recorder/util.py", line 297, in validate_sqlite_database
run_checks_on_open_db(dbpath, conn.cursor())
File "/usr/src/homeassistant/homeassistant/components/recorder/util.py", line 308, in run_checks_on_open_db
sanity_check_passed = basic_sanity_check(cursor)
File "/usr/src/homeassistant/homeassistant/components/recorder/util.py", line 286, in basic_sanity_check
cursor.execute(f"SELECT * FROM {table} LIMIT 1;") # nosec # not injection
sqlite3.DatabaseError: database disk image is malformed
- Like
- Reply
- Share
- 8 m
Someone on the FB group suggested the version may be a problem so I downgraded HA to the version I was running end of Jan, which I think was 2023.1.7 and then copied the backed up db file into it. I also ran
chmod 777 home-assistant_v2.db
to prevent permissioning issues.
This seemed to work for a while so I went to bed! Only discover this morning that the database was corrupted overnight again and a new one formed, losing all the energy data again.
Any other suggestions?
Is there a way to repair a corrupt db file or a better process for restoring a backed up one?