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

Configuration
,
1

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

2

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.

3 Likes
3

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.

4

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

1 Like
5

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).

6

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.

7

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?

3 Likes
8

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).

4 Likes
9

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

10

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.

11

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.

12

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.

13

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?

1 Like
14

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

15

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”.

16

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?

17

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

1 Like
18

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.

19

I just wrote a script that imports historical long-term statistics from a backup into a current sqlite database. In my case, I had a fresh Home Assistant database, and a recent copy of the old one. This script copies the long-term stats from the old one into the new database, while carefully translating the statistics IDs between the two databases.

Using this approach, it is also pretty easy to export just the long-term statistics out of the running database for backup purposes, and restore using this script as required.

It’s for sqlite only. It will not work for mariadb users. You can get it here:

I suggest taking backup copies of the database, and using the --dry-run argument to test things before allowing it to actually perform the import.