Backing Up Individual Table(s) or Index(es) and Restoring Them to a Fresh DB

What?

Selectively dumping a database table or its schema, for reload into a separate instance, or for recovery in the original instance.

Note: This is only possible with Virtuoso 6.0 and later.

Why?

There are many reasons, ranging from system corruption to migration, why you might want to reconstitute data across database management system instances.

How to Dump and Recover one or more SQL Table(s)

  1. Create a function for dumping the key: 

    SQL> 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 ();
  };

    Note: You can dump multiple tables by adding more calls to the backup_index function.
  2. On the source DB, dump the T1 table's primary key to a file named "mylog.txn", by executing: 

    SQL> bkp_key 
  ( 'mylog.txn'
  , 'DB.DBA.T1'
  , 'T1'
  );

  3. On the source DB host, stop the Virtuoso server, and dump only the schema tables (no data) into the trx file, by executing: 

    % virtuoso +crash-dump +foreground +mode l   ## ( lower case L )

  4. On the target DB host, make sure there is no existing .db file, and place the .trx file produced by previous step. Create a new DB with the schema from the source DB, by executing: 

    % virtuoso +restore-crash-dump +foreground ;

  5. Insert the PK data from the source DB dump into the new table in the new DB, by starting the new DB as normal, and executing: 

    SQL> replay ('mylog.txn') ;

    Note: that this has only restored the Primary Key index. If there are multiple indexes on the table(s), the other indexes must be dropped and re-created, as they will be empty at this point.

Related