Yesterday I suffered a corruption on my Home Assistant database so thought I'd share the series of steps I followed to recover the database and (pretty much) all the data.
Home Assistant is pretty robust but if it decides that the database is corrupt it just renames the existing database and creates a new empty database. So it carries on working, all of your automations, templates, entities, dashboards, etc all carry forward and are not lost, but all of your entity state history and long/short term statistics are lost.
The alternative is to recover from your last backup but you'll lose all data since the backup whereas this process shouldn't lose anything. I have had to follow it more than once but that probably says I am fiddling unnecessarily ....
I'm going to assume a few pre-requisites:
- You have Samba add-on installed to give access to the HA filesystem from a PC/laptop
- You have SSH add-on installed to give remote access to the HA server and Putty or similar to access from a PC/laptop
- You have SQLite installed on a PC/laptop to enable direct access into the HA database
Item 3 might not be required, you might be able to use sqlite installed in HA, I have never tried this and do all the database manipulation on a Windows PC. But Mac would work just as well.
Step 1 shutdown HA and extract the corrupt HA database
On your PC, open a Putty terminal session to HA (username is root, password is as defined in the SSH add-on configuration). You can't use the 'open web interface' option in the SSH add-on, you need to use a separate direct session.
enter the commands
- ha core stop [this will shutdown HA]
- cd /config
- ls [find the filename that HA has moved the corrupt database to, mine was home-assistant_v2.db.corrupt.2025-03-14T20:13:36.173579+00:00]
- mv home-assistant_v2.db.corrupt* ha_v2.BAD.db
Step 2 Copy the corrupt db to your PC
Using the samba share \homeassistant.local copy \config\ha_v2,BAD.db to your windows PC
- Investigate and re-create the HA database, retaining as much data as possible
Open a DOS or command line window
- cd /[pathname where I stored the above ha_v2.BAD.db file]
- sqlite3 ha_v2.BAD.db
- pragma integrity_check;
- .exit
The pragma integrity_check; (trailing semi-colon is essential) will check the SQLite database for integrity errors. I got about 20 telling me of various index issues, so confirming the database was corrupt.
Try the following DOS commands to recover the database:
- sqlite3 ha_v2.BAD.db “.dump” >dbdump.txt
- sqlite3 ha_v2.fix.db <dbdump.txt
dbdump.txt will contain all the data extracted from the database. If all goes well, ha_v2.fix.db will be your newly re-created database. However for me, and for others attempting this recovery, sometimes you end up with a zero byte database file which is clearly not what's wanted
If this happens, try this more advanced spell:
- sqlite3 ha_v2.BAD.db “.recover” >dbrecover.txt
- sqlite3 ha_v2.fix.db <dbrecover.txt
This should give a ha_v2.fix.db of a similar size to your original HA db. You will get a few errors about ignoring/loading an internal table.
Check this for errors:
- sqlite3 ha_v2.fix.db
- pragma integrity_check;
- .exit
Should get the result ‘ok’ from the integrity_check
Step 4. Move the fixed database back into HA
copy ha_v2.fix.db to \homeassistant.local\config via samba share
Step 5. Install the fixed database in HA
Using putty:
- cd /config
- mv home-assistant_v2.db ha_v2.empty.db
- mv ha_v2.fix.db home-assistant_v2.db
- ha core start
Check the HA startup log for errors, etc, all should be good and all data is recovered
Step 6. Tidy up
In putty:
- cd /config
- rm ha_v2.BAD.db ha_v2.empty.db
If you don't remove the corrupted database from HA, it'll be backed up alongside your normal HA db in your next backup, so doubling your backup size (don't ask me how I know this)
You have a copy on your PC anyway if you ever need to refer to it
More on this topic here: https://community.home-assistant.io/t/db-image-malformed-how-to-fix-it/76413/32