Recovering specific records from a backup

[Aldo Caruso]

Hello,

  I need to recover many specific records of a table from a backup.
Those records where mistakenly deleted, that is why I have to resort to
a database backup that I have taken before the deletion.

  I restored the backup to a database with another name / alias. My
intention is to create a script in some language ( Python for instance )
that connects to both databases, selects the records in the restored
database and inserts them in the current one.

  Is there a way to do this from isq-fb executing sql sentences, or the
only way is the one I described ( by means of a script or program in
some language that bridges between both databases )

Thanks in advance for any help

Aldo

[Mark Rotteveel]

You can use EXECUTE BLOCK with an EXECUTE STATEMENT ... ON EXTERNAL to
retrieve data from another database (or insert into another database).

Mark
--
Mark Rotteveel

[Ertan Küçükoglu]

Hello,

I once had a "mysterious missing records" problem. Turned out to be a wrong delete statement in our application.

I learned that FirebirdSQL can execute statements in an external database. Below script served me well for importing missing records at that time. 

set term ^ ;

execute block 

as

declare aorderdetailid integer;

declare aorderid integer;

declare aorderdayid integer;

declare aproductid varchar(100);

declare aproductname varchar(200);

declare aprice double precision;

declare aqty double precision;

declare aqtyupdate double precision;

declare aunit varchar(20);

declare afree smallint;

declare avat double precision;

declare apaidqty integer;

declare aentryuser varchar(100);

declare aprinted smallint;

declare aadditionprintedqty double precision;

declare akitchensend smallint;

declare aproductnotes varchar(150);

declare acost double precision;

declare awaiting smallint;

begin

   for execute statement 'select orderdetailid, orderid, orderdayid, productid, productname, price, qty, qtyupdate, unit, free, vat, paidqty, entryuser, printed, additionprintedqty, kitchensend, productnotes, cost, waiting from orderdetail where orderid in (select orderid from orders where rdate >= ''2021-07-01'' and rdate <= ''2021-07-04'')'

     on external data source 'localhost:E:\DBs\arsiv\ARSIV.FDB' as user 'your_db_user' password 'your_db_password'

     into :aorderdetailid, :aorderid, :aorderdayid, :aproductid, :aproductname, :aprice, :aqty, :aqtyupdate, :aunit, :afree, :avat, :apaidqty, :aentryuser, :aprinted, :aadditionprintedqty, :akitchensend, :aproductnotes, :acost, :awaiting do

   begin

     insert into orderdetail(orderdetailid, orderid, orderdayid, productid, productname, price, qty, qtyupdate, unit, free, vat, paidqty, entryuser, printed, additionprintedqty, kitchensend, productnotes, cost, waiting) 

     values(:aorderdetailid, :aorderid, :aorderdayid, :aproductid, :aproductname, :aprice, :aqty, :aqtyupdate, :aunit, :afree, :avat, :apaidqty, :aentryuser, :aprinted, :aadditionprintedqty, :akitchensend, :aproductnotes, :acost, :awaiting);

   end

end^

set term ; ^

It seems complicated at first, but it is very simple in its core.

0- Adjust the above script to your needs.

1- Use the command line ISQL tool to connect to the database where you have missing records.

2- Run your own script to insert only missing records.

3- Do not forget to COMMIT.

I hope that helps.

Thanks & Regards,

Ertan Küçükoğlu

Aldo Caruso <aldo....@argencasas.com>, 28 Ağu 2021 Cmt, 17:24 tarihinde şunu yazdı:

--
You received this message because you are subscribed to the Google Groups "firebird-support" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-suppo...@googlegroups.com.
To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/a2ef3ba2-79ad-f88b-f23f-249eb81c664e%40argencasas.com.

[Tomasz Tyrakowski]

If the number of records to transfer is relatively small (i.e. thousands
rather than millions), you can select them from the backup database in
FlameRobin and use FlameRobin's function "copy as insert statements"
(right-click on the data set grid and the function is in the context
menu). Then connect to the production database, paste the inserts from
the clipboard and execute them.
That's an easy way for not too large record sets, provided you don't
need any special transformations in between (e.g. generating new keys etc.).

regards
Tomasz
________________________________


Ta wiadomość zawiera poufne informacje przeznaczone tylko dla adresata. Jeżeli nie jesteście Państwo jej adresatem, bądź otrzymaliście ją przez pomyłkę, prosimy o powiadomienie o tym nadawcy oraz trwałe jej usunięcie.

[Mark Rotteveel]

An alternative approach could be to create an external table in the
source database, insert the records missing from the target database
into that external table.

Create the same external table in the target database and point to the
same file and select the data from it.

This approach is probably not very useful if you need the target
database to determine which records are missing, but it might be faster
if the databases are in different locations.

Mark
--
Mark Rotteveel

[Aldo Caruso]

Thanks to all of you for the good ideas that you have proposed !

Aldo

El 28/8/21 a las 11:24, Aldo Caruso escribió: