Page 1 of 1
Restore of history
Posted: Tuesday 23 February 2021 13:38
by itsbvka
Due to a wrong script I had a constant crash of Domoticz, the easiest way to recover was to restore an earlier domoticz.db database which worked fine.
However, since the backup was a couple weeks old, I lost a few weeks of history data. is there a way to extract that data from the database that caused the corruption and insert it into the restored one?
Re: Restore of history
Posted: Tuesday 23 February 2021 18:28
by waaren
itsbvka wrote: ↑Tuesday 23 February 2021 13:38
Due to a wrong script I had a constant crash of Domoticz, the easiest way to recover was to restore an earlier domoticz.db database which worked fine.
However, since the backup was a couple weeks old, I lost a few weeks of history data. is there a way to extract that data from the database that caused the corruption and insert it into the restored one?
Yes there is.
[EDIT] removed incomplete answer to avoid confusion. Look at next post for working method
Re: Restore of history
Posted: Tuesday 23 February 2021 19:19
by itsbvka
Thanks!
This looks promising and as I would expect it. However when executing line by line it does not give any message and in the end the database is the same size as the original. How can I see what is happening and check that the progress is as it should be?
Restarting with the <to be> updated database it does not show the history in the graphs so I think somewhere I'm missing the real update
Re: Restore of history
Posted: Tuesday 23 February 2021 20:55
by waaren
itsbvka wrote: ↑Tuesday 23 February 2021 19:19
However when executing line by line it does not give any message and in the end the database is the same size as the original. How can I see what is happening and check that the progress is as it should be?
Can you try again with this? and if it does not work then please share what you see.
Code: Select all
sudo service domoticz stop
cd <domoticz dir>
sudo cp domoticz.db safecopy.db # just to be safe
sudo cp <backup database> workcopy.db
daysBackBegin=-20 # adjust to your needs
daysBackEnd=-2 # adjust to your needs
# copy paste below in CLI until next comment line
sudo sqlite3 workcopy.db <<END_SQL
ATTACH "domoticz.db" AS dz;
INSERT INTO dz.Meter_Calendar SELECT * FROM Meter_Calendar WHERE DATE (Date) > DATE ("now","$daysBackBegin days") AND DATE (Date) < DATE ("now","$daysBackEnd days"); SELECT "Inserted records in table Meter_Calendar: ", changes();
INSERT INTO dz.Temperature_Calendar SELECT * FROM Temperature_Calendar WHERE DATE (Date) > DATE ("now","$daysBackBegin days") AND DATE (Date) < DATE ("now","$daysBackEnd days") ; SELECT "Inserted records in table Temperature_Calendar: ", changes();
INSERT INTO dz.Rain_Calendar SELECT * FROM Rain_Calendar WHERE DATE (Date) > DATE ("now","$daysBackBegin days") AND DATE (Date) < DATE ("now","$daysBackEnd days"); SELECT "Inserted records in table Rain_Calendar :", changes();
INSERT INTO dz.Wind_Calendar SELECT * FROM Wind_Calendar WHERE DATE (Date) > DATE ("now","$daysBackBegin days") AND DATE (Date) < DATE ("now","$daysBackEnd days") ; SELECT "Inserted records in table Wind_Calendar :", changes();
INSERT INTO dz.MultiMeter_Calendar SELECT * FROM MultiMeter_Calendar WHERE DATE (Date) > DATE ("now","$daysBackBegin days") AND DATE (Date) < DATE ("now","$daysBackEnd days") ; SELECT "Inserted records in table MultiMeter_Calendar :", changes();
INSERT INTO dz.Percentage_Calendar SELECT * FROM Percentage_Calendar WHERE DATE (Date) > DATE ("now","$daysBackBegin days") AND DATE (Date) < DATE ("now","$daysBackEnd days") ; SELECT "Inserted records in table Percentage_Calendar :", changes();
INSERT INTO dz.Fan_Calendar SELECT * FROM Fan_Calendar WHERE DATE (Date) > DATE ("now","$daysBackBegin days") AND DATE (Date) < DATE ("now","$daysBackEnd days") ; SELECT "Inserted records in table Fan_Calendar :", changes();
END_SQL
# copy paste end
service domoticz start
Re: Restore of history
Posted: Tuesday 23 February 2021 21:43
by itsbvka
Thanks, this time it worked like a charm!
I've set daysBackBegin to -11 and daysBackEnd to -1 to match the missing days
Output of the counters is this,showing the updated table counts
6695
5701
1267
1917
14408
12154
0
6758
5728
1267
1926
14497
12226
0
BTW, the copies were a little wrong, you copy the domoticz.db to both safecopy and workcopy, it needed to copy domotizc.db to safecopy and the original db (with history) to workcopy.db
Thank you very much!
Re: Restore of history
Posted: Saturday 12 February 2022 10:13
by Tonyticz
Hi, I hope you can help me (even this is an old post).
When I run the script only the usage and not the return of my P1 Smart Meter are in the graphs.
I'm missing the Electra return, gas and SolarEdge.
How can I fix this?