How to: Recover corrupted SQLite database

[penguin]

Cause

The DB was corrupted due to the write disk being full

 

Prerequisites

 

Download SQLite binary for Windows:

• https://www.sqlite.org/download.html
• Precompiled Binaries for Windows

 

Recover corrupted SQLite database

 

Dump the corrupted database to SQL

 

sqlite3.exe home-assistant_v2.db.corrupt .recover > home-assistant_v2.sql

 

 

Now we build a new DB with the SQL, but we can see that we have some issues, the table sqlite_stat1 does not exist

So the next steps is to edit the SQL and remove all lines related to sqlite_stat1

 

C:\Users\user\Downloads\sqlite-tools-win-x64-3470100>sqlite3.exe home-assistant_v2.db

SQLite version 3.47.1 2024-11-25 12:07:48

Enter ".help" for usage hints.

sqlite> .read home-assistant_v2.sql

Parse error near line 144: object name reserved for internal use: sqlite_stat1

Parse error near line 360933: no such table: sqlite_stat1

Parse error near line 360934: no such table: sqlite_stat1

Parse error near line 360935: no such table: sqlite_stat1

Parse error near line 360936: no such table: sqlite_stat1

Parse error near line 360937: no such table: sqlite_stat1

Parse error near line 360938: no such table: sqlite_stat1

Parse error near line 360939: no such table: sqlite_stat1

Parse error near line 360940: no such table: sqlite_stat1

Parse error near line 360941: no such table: sqlite_stat1

Parse error near line 360942: no such table: sqlite_stat1

Parse error near line 360943: no such table: sqlite_stat1

Parse error near line 360944: no such table: sqlite_stat1

Parse error near line 360945: no such table: sqlite_stat1

Parse error near line 360946: no such table: sqlite_stat1

Parse error near line 360947: no such table: sqlite_stat1

Parse error near line 360948: no such table: sqlite_stat1

Parse error near line 360949: no such table: sqlite_stat1

sqlite> exit

 

After we removed the corrupted information, we create again the fresh database from the SQL

 

C:\Users\user\Downloads\sqlite-tools-win-x64-3470100>sqlite3.exe home-assistant_v2.db

SQLite version 3.47.1 2024-11-25 12:07:48

Enter ".help" for usage hints.

sqlite> .read home-assistant_v2.sql

sqlite>

 

Now you just need to stop home-assistant container, copy the recovered DB file, set the right permissions and restart the container. It worked fine