Wanted: ability to export/import a schema

502 Views

Currently there is no easy way to extract and restore objects, permissions, data, etc for a particular schema.

Say you needed to extract a schema to be restored on to multiple iq instances (prod -> regression and testing and development). Doing a backup/restore of the database is not practical as the schema may be only a few hundred megabytes while the database is terabytes in size plus there is the whole mess of adding removing users due to the different environment.

In Oracle, we can do this very easily with:

export schemas

expdp system/tiger@db11g schemas=SCOTT,USER1,USER2 directory=TEST_DIR dumpfile=schemas.dmp logfile=expdp.log

import only USER1 schema

impdp system/tiger@db11g schemas=USER1 directory=TEST_DIR dumpfile=schemas.dmp logfile=impdp.log

Surely the smart people in SAP Sybase can create such a feature without too much trouble.

Including link to datapump. Oracle's had this for more than a decade:

http://www.oracle-base.com/articles/10g/oracle-data-pump-10g.php