Restore of history Topic is solved

[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?

[waaren]

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

[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

[waaren]

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

[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!

[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?