Database Restore after Snapshot restore

Installation Home Assistant OS
1

Hi all,

I screwed up and now have to pay for it. I used to have a working backup automation, but somehow I switched to new router at the beginning of the year and failed to update / reinstall the Samba-Backup mechanism due to time etc… I always pushed this out thinking: next weekend I will do it.

Now SD card failure has hit me (I know, SSD are more resilient). I was able to restore the system to a state in early May as I have a full snapshot from that time.
I was able to rescue my config / homeassistant folder from the old SD Card (I’m on Home Assistant OS and a Raspi4).

I restored the system now to the state of early May on a new SD card, but obviously I lost all my longterm and energy data in between.

My idea is now to use the system restore from May and import the latest sqlite database file (home-assistant_v2.db) from early July manually. It does not seem to work as soon as I import it and restart, I get no energy statics or database history at all (despite the file being around 350MB).

I tried to apply the sqlite3 recover option and upload it again, as described here: Power down and database corrupt - #4 by e-raser but it did not help.

What am I doing wrong and any ideas how to restore the data between May and July?

2

In that folder will be a file named home-assistant_v2.db

Stop Home Assistant.

Copy the home-assistant_v2.db file to your new installation config folder.

Start Home Assistant.

You can stop and start home assistant from the CLI:

ha core stop

ha core start
3

Hey @tom_l thanks for your reply and your hint.

Unfortunately this does not work for me. I copied the database file (approx. 350MB) over to the config folder, but I is either overwritten (after next start it’s only 5KB) or the historical data is not being used. I opened both databases (from snapshot and from SD card restore) in a database viewer (TablePlus) an they seem to be similar (of course the one from July has more rows, but schema seems to be comparable).

While I see historical data in the snapshot until May, the data is completely empty for 2023 and every year before if I copy over the database.

Any further suggestions?

4

Ok, I was able to restore the missing data with a simple update of Home Assistant.
Snapshot restored the version 2023.2.x while the one that crashed was 2023.5.x
Just by updating the System to 2023.6 I was able to see the missing data.

Thanks again @tom_l for the hint in the right direction!