I had a similar experience. But without doing any db restoration.
Basically, I tried the crude way of simply making multiple backups and importing them into a new HAOS VM until one was not corrupted. For me, iteration #3 seemed to work. The history was there and went back months. All was working.
Yesterday (days later), I noticed the history only goes back a couple of days (the day of the migration) and there is a corrupted database file.
What difference a semicolon can make 
Turns out the quick check is really quick actually:
sqlite> PRAGMA quick_check;
*** in database main ***
Page 729453: free space corruption
next: integrity_check;
actually very quick too:
sqlite> PRAGMA integrity_check;
*** in database main ***
Page 729453: free space corruption
row 733 missing from index ix_events_event_type_time_fired
row 733 missing from index ix_events_context_id
row 733 missing from index ix_events_time_fired
database disk image is malformed
and the last check:
PRAGMA foreign_key_check;
states|152065729|events|1
Any ideas what this all means? Is it totally broken or is their a chance to get this db back to a working state?
Are you sure the restore was complete while you were browsing the history? My history is always available while the restore takes place but once it’s finished it will mark the (just imported) db as corrupt and start with a fresh one.
You can look ad the suffix of the corrupt db to see the exact timestamp.
Not completely sure.
On a fresh HAOS VM, instead of making a new account, I choose to import the backup file. And when it was done doing that and the dashboard loaded, the graphs etc were populated with past data. So I assumed to import succeeded.
But its possible it was still working on it in the background and declared the database corrupt later. I don’t know how it works internally.
I also battled with these issues for a while.
Interestingly, #60874 was merged in December 2021, but as far as I can tell, it only appears in pre-release 2021.12.0b5 and not in any release changelogs since. I probably have missed it somewhere.
Still, I’ve encountered this issue in March and April 2022 so it seems to be still not fixed entirely. Until then, I’ve written a bash script that creates a backup every night via a conjob. So far, it seems to work fine and I wanted to share it with you guys.
The following script
For now, this seems to to the trick and I’ve not seen any corrupted databases for a few days now.
#!/bin/bash
BASEDIR="/opt/ha-backups/"
SOURCE="homeassistant:/config/home-assistant_v2.db"
TARGET="$BASEDIR$(date +%F\_%H%M%S)_home-assistant_v2.db"
echo "$(date +%F\_%H%M%S) Starting Backup" >> $BASEDIR"log.txt"
echo -n "$(date +%F\_%H%M%S) Shutting down HA: " >> $BASEDIR"log.txt"
ha core stop >> $BASEDIR"log.txt"
echo "$(date +%F\_%H%M%S) Copying database file" >> $BASEDIR"log.txt"
docker cp $SOURCE $TARGET
echo -n "$(date +%F\_%H%M%S) Checking for issues..." >> $BASEDIR"log.txt"
RESULT=$(sqlite3 $TARGET "pragma integrity_check")
if [ "$RESULT" != "ok" ]; then
echo "issues were found. Fixing..." >> $BASEDIR"log.txt"
echo
sqlite3 $TARGET -cmd ".output tmp.sql" ".dump"
rm $TARGET
sqlite3 $TARGET -cmd ".read tmp.sql" ".quit"
rm tmp.sql
echo
echo "$(date +%F\_%H%M%S) The backup was dumped and rebuilt. Now copying fixed backup back to HA." >> $BASEDIR"log.txt"
docker cp $TARGET $SOURCE
else
echo " no issues were found :)" >> $BASEDIR"log.txt"
fi
echo -n "$(date +%F\_%H%M%S) Starting HA: " >> $BASEDIR"log.txt"
ha core start >> $BASEDIR"log.txt"
My crontab for this looks like this:
# HomeAssistant Backup
0 4 * * * /opt/ha-backups/backup-and-fix
I hope this scripts are useful for other people as well until these issues are finally solved.
Today I had to restore a Home Assistant (partial) backup - due to an irrepealable ShellyForHASS (shelly custom integration) issue. First time to restore a backup ever (since running HA productively). Well:
Database is fine after restore, passed all integrity checks, all data there. 
So it seems like these mentioned fixes (How to make a full backup and restore with all the data as well? - #2 by tom_l) have been implemented/rolled out meanwhile:
The only negative side effect was the missing “config” SAMBA share:
It was available but not accessable, no content. Not via SAMBA clients, not for any addon.
Solution:
/config again.That is certainly not a perfect “restore experience” but related to the database pretty smooth. Makes me a lot more confident to restore backups in the future.
Anyway, that SAMBA thing is something probably worth reporting.
Where to? To that addon repository and the HA OS repository? What do you experts think.
[*1]
Maybe, or coincidentally there were no writes durring you backup creation. It deserves confirmation from someone who is confident about this improvement. I’m not aware it was mentioned in changelogs released during last half a year.
Unfortunately one can not ask in both referenced GitHub PRs. Maybe it was only merged into the dev channel? I don’t know.
What do you think @tom_l (as I referenced your OP [here](How to make a full backup and restore with all the data as well? - #2 by tom_l otherwise I wouldn’t even know bout that), has that ever been shipped with a stable HA release?
I had a empty file after fixing with “.dump” too.
Fixing with “.recover” resolved my problem !
Thanks !!
Great news 
As confirmed in
the two fixes taking care SQLite database is not being corrupted during the backup process (Allow to lock SQLite database during backup by agners · Pull Request #60874 · home-assistant/core · GitHub and Refactor recorder queue handling by emontnemery · Pull Request #61161 · home-assistant/core · GitHub) have been merged already a long time ago, roughly around February 2022.
…which is the final confirmation of my experience of restoring a backup without having a corrupted database back in July this year (Fix corrupted / malformed SQLite database (home-assistant_v2.db) - #23 by e-raser).
Really wondering if I missed that in the release notes or if they just shipped it silently (why not promoting this fix as it is really really REALLY great news?!?).
Well my database corrupted last night during a restore of a backup from a few hrs eariler, so is this not supposed to happen any more?
I’m using the default built-in database.
The backup I restored to was created automatically when core was updated; it ran for a few hrs then corrupted. The corruption was foreign key, iirc.
I fixed it by restoring from a different, previous backup.
Small update which confirms (in regard to Fix corrupted / malformed SQLite database (home-assistant_v2.db) - #27 by e-raser) that backing up the SQLite database nowadays is quite safe because of
Observations:
So I guess HA simply
(*)
Logger: frontend.js.latest.202207071
Source: components/system_log/__init__.py:190
First occurred: 00:01:08 (33 occurrences)
Last logged: 14:15:33
:0:0 Script error.
http://my.home-assistant/frontend_latest/18edb15a.js:269:10429 RangeError: number argument must be finite
:0:0 ResizeObserver loop completed with undelivered notifications.
wow, this post saved my stats for energy use after a corruption! thank you so much!
FYI my HA runs on an RPI4 with SSD via USB - the backup to Samba runs at 4AM and somehow my DB became corrupted. no restores, no reboots and no idea why… I did the integrity check on the original DB (that HA copied at 4am) and it worked. all stats backed up (and full config backed up at 4am on schedule).
Eek! It happened again this morning, same time, same error and same corruption. I’ve just done the pragma integrity_check again but not sure why it’s happening at the same time each day. I mentioned backups (samba addon) but that’s happening at 2AM so something else is going on at 4AM.
I have mariadb installed and will look to use that going forward but my energy data is critical to me, thats what I need to somehow keep. any ideas if the sqllite data can be migrated to mariadb?
Check if the specific issue time matches the default purge time:
Best migration guides from SQLite to MySQL are at Migrating home assistant database from sqlite to mariadb (little warning: for some the migration created new issues especially for data and energy data, for others it worked without any issues - so there‘s no absolutely safe, risk-free and guaranteed way at the moment).
Hello together,
every few days my HA is also crashing and I can find those errors in my log files.
I am absolutely not sure what causes the issue.
Yesterday I took a new SSD and did a fresh install and restored an old backup.
So I think that the DB can’t be corrupt.
Is that assumption correct?
I also did the following in the past:
Was that correct in the way to check?
Thank you. I have absolutely no clue on what I can check in addition …
This are the errors found related to SQL:
2:10:25 PM – (WARNING) Home Assistant Supervisor - message first occurred at 2:10:24 PM and shows up 5 times
/addons/core_deconz/info return code 500
2:10:25 PM – (ERROR) Home Assistant Supervisor - message first occurred at 2:10:03 PM and shows up 8 times
Error while processing event StatisticsTask(start=datetime.datetime(2023, 5, 31, 12, 5, tzinfo=datetime.timezone.utc), fire_events=True): [Errno 30] Read-only file system: '//config/home-assistant_v2.db' -> '//config/home-assistant_v2.db.corrupt.2023-05-31T12:10:24.828445+00:00'
2:10:24 PM – (ERROR) Recorder
The system will rename the corrupt database file //config/home-assistant_v2.db to //config/home-assistant_v2.db.corrupt.2023-05-31T12:10:24.828445+00:00 in order to allow startup to proceed
2:10:24 PM – (ERROR) Recorder
Can't read Supervisor data:
2:10:24 PM – (WARNING) Home Assistant Supervisor
Could not fetch changelog for core_deconz:
2:10:24 PM – (WARNING) Home Assistant Supervisor - message first occurred at 2:10:24 PM and shows up 5 times
/addons/core_deconz/changelog return code 500
2:10:24 PM – (ERROR) Home Assistant Supervisor - message first occurred at 7:49:08 AM and shows up 7 times
Unrecoverable sqlite3 database corruption detected: (sqlite3.DatabaseError) database disk image is malformed [SQL: INSERT INTO statistics_short_term (created, created_ts, metadata_id, start, start_ts, mean, min, max, last_reset, last_reset_ts, state, sum) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) RETURNING id] [parameters: (None, 1685535024.2400837, 63, None, 1685534700.0, 6108.217472046666, 6108.0, 6109.0, None, None, None, None)] (Background on this error at: https://sqlalche.me/e/20/4xp6)
2:10:24 PM – (ERROR) Recorder
Error executing query: (sqlite3.DatabaseError) database disk image is malformed [SQL: INSERT INTO statistics_short_term (created, created_ts, metadata_id, start, start_ts, mean, min, max, last_reset, last_reset_ts, state, sum) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) RETURNING id] [parameters: (None, 1685535024.2400837, 63, None, 1685534700.0, 6108.217472046666, 6108.0, 6109.0, None, None, None, None)] (Background on this error at: https://sqlalche.me/e/20/4xp6)
2:10:24 PM – (ERROR) Recorder
Lost connection to UniFi Network
And as mentioned, did a reboot, copied the DB and checked … no issues …
Those (amongst others) really don’t sound that good (pretty obvious):
Unrecoverable sqlite3 database corruption detected: (sqlite3.DatabaseError) database disk image is malformed
2:10:24 PM – (ERROR) Recorder
Error executing query: (sqlite3.DatabaseError) database disk image is malformed
Might be a storage (disk) issue? If it reboots regularly often low RAM is a basic issue.
It is a Pi4 with 8GB Ram.
And as mentioned, disk is a completely new HDD.
Recommendations?
I am not married with my history data, because it is at least only from the last 10 days.
Try to delete thw whole DB and restart?