How to make a full backup and restore with all the data as well?

[La-te]

Apparently the full backup and restore is not restoring some of the data. Namely, it is not restoring the database where there are for example energy and long-term statistics and probably some other history data too. That came obvious to me when I had to restore a backup.

What is left out from the full backup actually? How I could include all in the backup and how I could restore everything? I’m used to to play with Linux so at minimum I would need to know what files I need to backup and restore. For databases it would be helpful to share what method should be used for the backup as usually it is not a good idea to just copy a live database file.

I’m running HA on rpi4 with the standard sqlite db. Should I just automate an export and copy of it and then restore over the current db if needed after a fresh install and backup restore? Would it work or not?

Some issues discussed here

Backup option for Energy Dashboard

Last two weeks i made some hardware en configuration changes for my home assistant and ended up reinstalling and putting back a full-backup from the supervisor menu. Both times every configuration and setting came back and worked except my energy history. I don’t have experience with databases so it would be nice that the history data of my energy measurements would be backupt true the normale supervisor backup option.

How to make a periodic automatic backup of the database?

Hello, I made some errors in the HA settings, so I thought that the easiest way would be to restore from a regular backup using the Google Drive Backup Home Assistant addon. The restore was successful, but I found that the backups unfortunately do not contain database backups. All the history of my sensors is gone. It’s been a few weeks of history, so I’ll put up with it. But I would like to avoid it next time. Even in case of SD card failure. How can I set up a daily backup of the entire dat…

[tom_l]

There is development ongoing to lock the database to prevent corruption during backups but it is not available yet.

Your best bet would be to use the MariaDB addon instead of the default SQLite DB. This can be stopped while you make a backup. Failing that…

Your second best bet would be to make a full backup of home assistant. Then stop home assistant and copy the database file either by using SSH or the Samba addons. config/home-assistant_v2.db

Restore the backup to the new system then stop Home Assistant and copy across the database file. Then start home assistant.

[La-te]

Are you saying that my backup actually had all the data so the sqlite db but it was most probably corrupted and restore didn’t bring a full operational backup copy back?

Is there any hope for a working and compatible backup if I go and look for my other backups and manually restore the db file? I have several backup copies of different HA versions but maybe only one another from 2021.12.x. However, due to a number of sensors there are more than hundred events per minute recorded so if sqlite db copy can’t handle that very well the possibility must be nonexistent.

Looks like I need to follow the procedure of stopping home assistant and manually copying the db for now.

[tom_l]

@La-te

Is there any hope for a working and compatible backup if I go and look for my other backups and manually restore the db file?

No they will most likely be corrupt too. However it may be possible to repair it. See:

Fix corrupted / malformed SQLite database (home-assistant_v2.db)

I restored a snapshot when switching hardware / sd card (HASS OS on Pi 2 B 32 bit to HASS OS on Pi 3 B+ 64 bit). Result: The home-assistant_v2.db from a restored snapshot is malformed or corrupted. This leads so error messages and not functional Home Assistant (history graphs not generated/shown, sensor state changes aren´t stored etc.). I tested it several times, in every test case the SQLite database got corrupted. Either during backup (creating the snapshot) or restore process. sqlalchem…

[MaxK (Mark)]

I use Google Drive Backup to automate the backup of my configuration. And I periodically do a full image backup of the SD card to a second SD card; then I run from the second SD card (and put the first one in a safe place).

[La-te]

Thanks a LOT! This seemed to help me to do the trick. Initially, something happened in the upgrade and after a while disk was full and my Home Assistant in reboot loop.

I took a fresh sd card and a fresh installation of HA to do the restore where I found the data is missing. Hence, I still had the HA sqlite db on the card. It was possible to restore the data with the third method in the post so .dump the database as a new database. It seems to be working now and my history is there. Really good!

I removed some wrong long-term statistics data from the db as otherwise the long-term statistics didn’t work. Potentially that was since the db HA version is not matching the installed HA version. There was some bug related to that and fixed so normal updates must have done the similar operation without user interaction.

[La-te]

@tom_l

Your second best bet would be to make a full backup of home assistant. Then stop home assistant and copy the database file either by using SSH or the Samba addons. config/home-assistant_v2.db

What about first stop, then take a backup and then start home assistant? Like this with the Home Assistant CLI.

ha core stop
ha backups new --name backupwithcorestopped.tar
ha core start

Would that create a backup with unbroken sqlite database?

[tom_l]

@La-te

Would that create a backup with unbroken sqlite database?

Yes that is also a viable solution.

For some reason I thought the HA CLI would stop when HA was stopped, but I’m pretty sure it is part of the supervisor (as I have used the CLI when HA has failed).

[La-te]

Ok good. The initial question in the title has been now been answered and solved

[tom_l]

Now that I have added a category to your topic you can select the post that was the solution. This helps people searching in future.

[e-raser]

Seems this is working meanwhile. Had to restore a HA backup and database looks fine so far. Details @ Fix corrupted / malformed SQLite database (home-assistant_v2.db) - #23 by e-raser.

[sdevoogt (sdv)]

This procedure using the HA CLI worked for me. Before I got a corrupt database when I took a backup while HA was running and then tried to restore it.
All historical energy-data is also preserved.

[Lasoul]

Is there a way to automate this in a script or shell command?
Does stopping the recorder help while making a backup of the sqllite database?

[sgbirch (Stephen Birch)]

Is cron available on the system, that would be perfect to schedule a full backup?

[WalterDSU (W@lter)]

waiting to know a way to a really full backup with a unbroken database. lost one year data from now on. it drives me crazy.

Updated:
I used samba backup(addon) to make a full backup 4 times a week, but I found most of those backups with a broken database. only 3 or 4 backups have a good database can restore with all data. check your backup file(*.tar), those backup files with a “home- assistant v2.db-wal” is 0 size are good and can be restored fully.
SO Either way, you do your backup, check the backup file and see whether comes with a 0 size “home- assistant v2.db-wal”.

[Makis]

@tom_l

There is development ongoing to lock the database to prevent corruption during backups but it is not available yet.

Your best bet would be to use the MariaDB addon instead of the default SQLite DB. This can be stopped while you make a backup. Failing that…

Your second best bet would be to make a full backup of home assistant. Then stop home assistant and copy the database file either by using SSH or the Samba addons. config/home-assistant_v2.db

Restore the backup to the new system then stop Home Assistant and copy across the database file. Then start home assistant.

Hi Tom
after an update I lost my energy historical data. I have an older backup file and I can pull out the home_assistant-V2.db file from there. Can I just stop HA and copy with SSH the old db file to the config directory? Will then my data will be restored if I start HA again?

[tom_l]

If it’s from before the last couple of versions it may not work as there have been some updates to the database schema.

[filippomasoni]

Did the latest changes to the default SQLite database fix the corrupting issue? I’ve seen many posts of people switching from MariaDB back to SQLite. Can anyone confirm the database is actually locked during backup?
I’m using hassio-google-drive-backup add-on which I believe uses the default backup function to create the snapshots.