Recovering Home Assistant history by merging two healthy recorder databases
Hello,
I’m looking for some advice from people who have in-depth knowledge of the Home Assistant recorder database.
Situation
I experienced a recorder database corruption around 27 June 2026.
Fortunately I still have:
Database A
home-assistant_v2_26-juni_fixed.db
recovered using SQLite .recover
PRAGMA integrity_check returns ok
size: 985 MB
contains my complete history up to 26 June
Database B
current home-assistant_v2.db
healthy database
size: 616 MB
contains all history from the recovery until today
Both databases are healthy SQLite databases.
The problem is simply that my recorder history is now split over two databases.
What I would like to achieve
I would like to reconstruct one single recorder database containing:
all history up to 26 June
all history from the current database
without breaking Home Assistant recorder integrity
The goal is not statistics only, but the complete recorder history (states, state_attributes, states_meta, etc.), so that ApexCharts and History Graphs show the complete timeline again.
What I already tried
recovered the old database using SQLite .recover
verified both databases using PRAGMA integrity_check
investigated SQLite merge possibilities
investigated:
HAmerger
Jory Hogeveen’s home-assistant-merge-db
Unfortunately both projects appear to focus mainly on recorder statistics rather than merging the complete recorder history.
Questions
Has anybody successfully merged two healthy Home Assistant recorder databases?
Is there an existing tool that merges the complete recorder tables (not only statistics)?
Which recorder tables must remain internally consistent?
For example:
states
states_meta
state_attributes
events
statistics
statistics_meta
statistics_short_term
any others?
Is there an official or recommended approach for reconstructing recorder history after recovering a corrupted database?
Any advice from someone familiar with the recorder internals would be greatly appreciated.
Thank you!
BTW: I am not looking for backup advice. Both databases are healthy. The challenge is reconstructing a single consistent recorder database from two valid databases.
The Recorder Integration, documented here, stores every [default] state change and event.
But, only for 10 days [default].
Every day, all the recorder history that is older than 10 days, with the exception of the long-term statistics data [statistics table], is purged (deleted and removed).
Your old recovered database of 27 Jun will have “complete history” for 18 to 27 June.
Your current database will have “complete history” 19 to 28 July.
Add them together, then at the next purge tomorrow morning, all data before 20th July will be purged.
I concur with boheme61 - a little learning is a dangerous thing.
A little learning is a dangerous thing ;
Drink deep, or taste not the Pierian spring :
There shallow draughts intoxicate the brain,
And drinking largely sobers us again.
Fired at first sight with what the Muse imparts,
In fearless youth we tempt the heights of Arts ;
While from the bounded level of our mind
Short views we take, nor see the lengths behind,
But, more advanced, behold with strange surprise
New distant scenes of endless science rise !
So pleased at first the towering Alps we try,
Mount o’er the vales, and seem to tread the sky ;
The eternal snows appear already past,
And the first clouds and mountains seem the last ;
But those attained, we tremble to survey
The growing labours of the lengthened way ;
The increasing prospect tires our wandering eyes,
Hills peep o’er hills, and Alps on Alps arise !
I compared the same entity (sensor.binnen_temperatuur) in both databases.
Old DB: 2026-06-16 → 2026-07-04, metadata_id = 79
New DB: 2026-07-17 → current, metadata_id = 199
So the entity exists in both databases, but the internal metadata_id differs. This suggests that a successful merge would need to map states_meta by entity_id and rewrite states.metadata_id accordingly.
Does the recorder rely only on this mapping, or are there additional internal relationships that also need to be rewritten?
Yeah, that would be the logical conclusion. Write a script to walk the production meta tables (plural if you want stats) and update the old tables with the updated ids, making sure to move existing/conflicting IDs out of the way first. Or just update the few sensors you care about manually and import those. Then import those rows. (All basic SQL easily found even pre AI.). But, seems a bit tedious if not pointless if your states tables are going to be purged eventually.
If you are doing this amount of work, considering switching to a real database like MariaDB. Contrary to popular opinion you can have years of full resolution data in MariaDB - sure you need fast SSDs, memory and CPU. Plenty of web shops run 1TB MariaDB.
The file based SQLLite, is prone to disk corruption. It is great for a small lightweight database. But as you are moving away from that and have had corruption. Investing time combining two together may result in the same outcome.
Could you get the best of all worlds, your varying flavors of functional databases being split, mangled, merged, and gobbled up into the next big thing? The power of Open Source is magnificent, giving you flexibility to roll your own if you’re not happy. I note you have preserved (wisely) a copy of both databases, and have delved into experimenting. Could a deeper understanding of housekeeping, the role of the recorder, database sizing, design, configuration, and retention resolve your issue, and offer guidance and scope for HomeAssistant to become more sleek and robust?
I read this earlier, just keeping up with trends and crises.
[Note: I’ll follow it over there thanks, especially in the comments section], the issues of massaging, modifying, deleting adding, importing and generally playing with healthy and broken databases related to HomeAssistant done found in other threads and even apps written to assist you over here. My memory a little rusty,, but the constant refreshing with other issues distracting.