Hi all,
After some more searching/trying/failing I found a solution that worked for me. This is largely based on solution provided in other threads, however for me some intermediate steps were unclear, so I’ll share what I did in a way that makes sense to me (for others like me or future-me when this happens again).
First things first, HA runs as VM on a windows NUC without monitor/keyboard, so I use remote desktop if I need to access it. Furthermore, important to have SAMBA or something similar setup to get files on/off HA.
I could have done this on the NUC via remote desktop, but chose to use my laptop.
What I did
- Locate and copy your corrupted DB from HA to your laptop/PC
- Download SQLite (SQLite Download Page)
- Place the .exe file from SQLite and the corrupted DB in the same folder
- Open up Command Prompt (cmd) and change directory (cd) to the folder the DB is in
- Run this command (on 1 line, don’t split it, the “|” is correct) and then WAIT and in my case, ignore the error messages you see (important I’ll explain why this threw me off)
sqlite3 ./home-assistant_v2.db “.recover” | sqlite3 ./home-assistant_v2.db.fix
Where./home-assistant_v2.dbis your corrupted DB and./home-assistant_v2.db.fixthe name of your new fixed DB - Once this is done, you know because you can enter a new CMD line, you should have a new DB in your folder
- Before you delete your corrupted DB in HA and replace it with the fixed one, you should stop HA Core. I did this by going to developer tools > Services, searched for
Home Assistant Core Integration: Stopand ran that - Now using SAMBA I copied the fixed DB to the HA Folder, deleted the corrupted one and renamed the fixed DB to
home-assistant_v2.db - I then restarted my HA instance via the VM (your interface wouldn’t work because you stopped Core. This could probably also be achieved via SSH, but I didn’t have that on my laptop, so went for the easy solution
For a couple of days now, the nightly purge happens without corrupting the DB, so fingers crossed this fixed for me, for now.
Lastly, why is the WAIT and ignore errors important? Very quickly after executing the cmd, I got an error/warning about a purge error on lineXXX and then nothing else. I thought that meant it stopped and my froze and I clicked away the CMD window. Going through this a couple of time and not understanding why it worked for others and not me, I got distracted by something and didn’t immediately close the CMD window. A while later, I got back to my laptop and saw that the cmd finished with a handful of similar error messages, and that is when I understood while it looked frozen, it actually continued to run.
Now I was afraid the errors meant I still had a bad DB, but tried to put it back anyway (nothing to lose kind of situation) and turned out, it worked.
Let me know if you have any questions or comments about the write up.
All the best!