I restored a snapshot when switching hardware / sd card (HASS OS on Pi 2 B 32 bit to HASS OS on Pi 3 B+ 64 bit).
The home-assistant_v2.db from a restored snapshot is malformed or corrupted. This leads so error messages and not functional Home Assistant (history graphs not generated/shown, sensor state changes aren´t stored etc.).
I tested it several times, in every test case the SQLite database got corrupted. Either during backup (creating the snapshot) or restore process.
sqlalchemy.exc.DatabaseError: (sqlite3.DatabaseError) database disk image is malformed 2020-12-19 16:08:43 ERROR (SyncWorker_6) [homeassistant.components.recorder.util] Error executing query: (sqlite3.DatabaseError) database disk image is malformed (Background on this error at: http://sqlalche.me/e/13/4xp6)
As I was searching the HA community I quickly discovered this is a common issue, there are lots of topics related to this problem, seems to be a MASSIVE one.
The “solution” (in my opinion a workaround, at best) proposed in almost all those topics: delete the database, start from scratch. I was really wondering how everyone can accept the “just delete that damn sqlite db and your good to go!” workaround so all history data is gone.
For me personal it´s just not acceptable to loose all my history data every now and then. Especially when you´re not yet into putting medium and long-term data in e. g. InfluxDB. You never know when you´ll need to restore a snapshot, that´s what backups are for: being prepared for the worst case. So currently that´s not a reliable backup and restore progress at all.
So I decided to go the hard, not that quick way to keep my database (took me 10 to 15 minutes, saving many days of data):
I ran a “pragma integrity_check;” on it and several findings were returned:
When I take a look at the home-assistant_v2.db from the running system before the snapshot, the integrity check passes (I had access to the original SD card):
Assumption: either the snapshot creation process is faulty or the restore snapshot process harms the database. It´s very unlikely it´s a storage issue (“corrupt SD cards”), especially cause in my case they´re brand new.
In theory there should basically be three ways to fix this issue WITHOUT loosing all data (no need to create a new database):
Manually backup the home-assistant-v2.db next to the snapshot. Maybe automate it. Use that one for restore purposes.
Perform a “pragma integrity_check;” on the corrupted database and fix those issues.
Re-create the database while keeping its data:
(source: https://zngguvnf.org/2019-12-17--homeassistant-backup-and-restore.html - see also direct post links DB image malformed. How to fix it? and DB image malformed. How to fix it? for commands on a running system)
Copy your (corrupt) database (home-assistant_v2.db) to your local linux system and do:
“sqlite3 home-assistant_v2.db “.dump” | sqlite home-assistant_v2.db_fixed”
Now delete your old (corrupt database) and replace it with home-assistant_v2.db_fixed. To do that delete the corrupt one and rename home-assistant_v2.db_fixed to home-assistant_v2.db.
I personally succeeded in using option #1 in an advanced version: luckily I had access to the original disk (SD card) so I just grabbed the database, performed an integrity check (passed) and put that database back in place, restarted HA and everything´s fine again, meaning: all recorder, history and logbook data is there.
— Some developers could take a look at it (why is the database corrupted that often?) and especially check the snapshot creation and restore progress. —