How can I backup and restore individual table(s) and individual index(s) on a a new fresh db?
This section describes how to get a part of db tables and restore on a new db.
Additionally, part of the steps from below can be used to backup separate table and recover on same db.
Note: Only effective with Virtuoso 6.0 and later.Sample scenario
- Create a function for dumping the key:
create procedure bkp_key (in f any, in tb_name varchar, in key_name varchar) { backup_prepare (f); backup_index (tb_name, key_name); backup_flush (); backup_close (); } ;
- On the source db execute:
bkp_key ('mylog.txn', 'DB.DBA.T1', 'T1');
- This will dump in a "mylog.txn" file the T1 table's primary key.
- On source db stop server and do:
virtuoso +crash-dump +foreground +mode l ## ( lower case L )
- This will dump the schema tables only into the trx file.
- On the target db make sure there is no db file and place the trx file produced by previous step.
Execute:
virtuoso +restore-crash-dump +foreground ;
- This will create a new db with same db schema as on the source db.
- Start the target and do:
replay ('mylog.txn') ;
- This will insert the PK data into the table from the source db dump.
- If the table in question has other indexes must drop them and re-create them, since they are empty as in previous step we have been restoring only the PK.
