Datastore export and import

To back up the datastore or transfer it to another database, you can basically use the tools of your database such as Oracles exp/imp expdp/impdp or PostgreSQLs pg_dump. There is nothing wrong by using these tools and in fact it might be the best choise for certain tasks.

However these tools have two major drawbacks for some cases:

• They are not database independent, meaning it is not possible to export from Oracle and import to PostgreSQL, or vice versa.

• They do not allow to filter records such as archived data or rows marked for delete during export or import, leading to hight time comsumption when transfering from production to test or quality database where archived data is propably not used.

BPS has therefore an own set of export/import scripts using a database independent XML file to store the data.

The export utility will by default export all vital data, but exclude tables with temporary and non-vital data such as actors, locks and audits.

The export script is started from the command line using the bps interpreter. Apply the -h switch to get syntax, available options and switches of the current release:

  C:\Program Files\IBK Software AG\BPS2> bin\bps export -h
  SYNOPSYS
  
      bps export CONNECTSTRING [FILENAME] [OPTION]...
  
  DESCRIPTION
  
      This program exports the BPS database into a XML file.
  
      By default all standard BPS sequences and tables holding permanent data
      are included, and auditing tables as well as tables for transient data
      are excluded from the export.
  
      Custom tables and sequence may be added to the export by using the -i, -I,
      -e and -E options.
  
      NOTES
  
       - Enter the CONNECTSTRING for the bps owner as userid/pincode@connection.
       - When FILENAME is omitted it defaults to bps-<schema>-<timestamp>.bpx.
       - If no extension is present in the given FILENAME, .bpx will be added.
  
      EXAMPLES
  
      Basic export of the BPS permanent data and sequences to the default file
      name:
        bps export 0/1234@plant
  
      Basic export of the BPS permanent data to c:\test\myfile.xml:
        bps export 0/1234@plant c:\test\myfile.xml
  
      Include custom tables ct_*, except ct_temp and ct_scratch:
        bps export 0/1234@plant -i ct!_% -e ct!_temp,ct!_scratch
  
  
  OPTIONS
  
      -n, --nodefaulttables
          Do not include the default tables, only take those declared by -i and
          -e.
  
      -N, --nodefaultsequences
          Do not include the default sequences, only take those declared by -I
          and -E.
  
      -h, --help
          Show help (this).
  
      -i INCLUDETABLES, --includetables=INCLUDETABLES
          A comma separated list of table patterns to include.
  
          - The patterns must be lowercase and are for SQL LIKE
            compare, where ! is used as escape character.
  
      -e EXCLUDETABLES, --excludetables=EXCLUDETABLES
          A comma separated list of table patterns to exclude.
  
          - The patterns must be lowercase and are for SQL LIKE
            compare, where ! is used as escape character.
  
          - Tables being referenced by other exported tables will
            still get exported even when listed here.
  
      -I INCLUDESEQUENCES, --includesequences=INCLUDESEQUENCES
          A comma separated list of sequence patterns to include.
  
          - The patterns must be lowercase and are for SQL LIKE
            compare, where ! is used as escape character.
  
      -E EXCLUDESEQUENCES, --excludesequences=EXCLUDESEQUENCES
          A comma separated list of sequence patterns to exclude.
  
          - The patterns must be lowercase and are for SQL LIKE
            compare, where ! is used as escape character.
  
  Elapsed time was 0 hours, 0 minutes and 0.245 seconds.
  
  C:\Program Files\IBK Software AG\BPS2>

The import utility will by default import all tables and sequences found in the export file. All tables of the schema will be truncated before import to make sure no constraints are violated.

Ther are options to exclude tables, archived records or records marked for delete.

The import script is started from the command line using the bps interpreter. Apply the -h switch to get syntax, available options and switches of the current release:

  C:\Program Files\IBK Software AG\BPS2> bin\bps import -h
  SYNOPSYS
  
      bps import CONNECTSTRING FILENAME [OPTION]...
  
  DESCRIPTION
  
      This program imports the BPS database from a BPS export file (bpx).
  
      By default all tables and sequences in the export file are imported.
  
      Tables may be excluded by using the -e option.
      Sequences may be excluded by using the -E option.
      Records may be excluded by using the -a and -d switches.
  
      NOTES
  
       - Enter the CONNECTSTRING for the bps owner as userid/pincode@connection.
       - If no extension is present in the given FILENAME, .bpx will be added.
  
      EXAMPLES
  
      Basic import of the entire BPS export file bps-plant-20100108135920.bpx:
        bps import 0/1234@plant bps-plant-20100108135920
  
      Exclude history records and records marked for delete:
        bps import 0/1234@plant mydump.bpx -h -d
  
      Exclude custom tables with a name ct_*:
        bps import 0/1234@plant mydump.bpx -e ct!_%
  
  
  OPTIONS
  
      -d, --nodeleted
          Exclude records of regular BPS tables with delete status.
          This switch is likely to produce constraint errors for the records
          depending an a excluded record, so import will continue after insert
          errors.
  
      -a, --noarchived
          Exclude records of regular BPS tables with history status.
          This switch is likely to produce constraint errors for the records
          depending an a excluded record, so import will continue after insert
          errors.
  
      -h, --help
          Show help (this).
  
      -e EXCLUDETABLES, --excludetables=EXCLUDETABLES
          A comma separated list of table patterns to exclude.
  
          - The patterns must be lowercase and are for SQL LIKE
            compare, where ! is used as escape character.
  
      -E EXCLUDESEQUENCES, --excludesequences=EXCLUDESEQUENCES
          A comma separated list of sequence patterns to exclude.
  
          - The patterns must be lowercase and are for SQL LIKE
            compare, where ! is used as escape character.
  
  Elapsed time was 0 hours, 0 minutes and 0.175 seconds.
  
  C:\Program Files\IBK Software AG\BPS2>