Depending on your specific needs, you may need to create information models, tables, or stored procedures on a schema. This is usually done in a development system or you may want to reuse these customized objects. Instead of recreating everything in a different system, you can simply export the schema from the original system and then import the schema into the target system.
This article imparts how to export and import schemas in the SAP HANA studio and provides some additional information (for e.g. troubleshooting and SAP Business One Backup Service). Most importantly please note that schema export-import is not equivalent to full instance backup and recovery. Regular full instance backup must be performed. This point is further explained in SAP Note 2121486 (S-User login required).
Though we ONLY recommend SAP Business One Backup Service for schema export and import but following statements can be executed via SAP HANA studio to export and import a schema. You may choose to import a schema directly or import the schema with a different name:
- EXPORT “SCHEMA_NAME”.“*” AS BINARY INTO ‘export_path’ WITH REPLACE THREADS 10;
Example: EXPORT “SBODEMOUS”.”*” AS BINARY INTO ‘/usr/backup’ WITH REPLACE THREADS 10;
- IMPORT “SCHEMA_NAME”.“*” AS BINARY FROM ‘import_path’ WITH IGNORE EXISTING THREADS 10;
Example: IMPORT “SBODEMOUS”.”*” AS BINARY FROM ‘/usr/backup’ WITH IGNORE EXISTING THREADS 10;
- IMPORT “SCHEMA_NAME”.“*” AS BINARY FROM ‘import_path’ WITH IGNORE EXISTING THREADS 10 RENAME SCHEMA “SCHEMA_NAME” TO “NEW_SCHEMA_NAME”;
Example: IMPORT “SBODEMOUS”.”*” AS BINARY FROM ‘/usr/backup’ WITH IGNORE EXISTING THREADS 10 RENAME SCHEMA “SBODEMOUS” TO “SBOTEST”;
Note: It is highly recommended to use “WITH IGNORE EXISTING” clause in import command, using “WITH REPLACE” clause may lead to data loss for dependent objects as it might overwrite/erase any existing dependencies in target system.
You can configure database traces in debug mode for detailed investigation regarding the error if you encounter any during import process. Configure the indexserver trace and then check the errors in the collected trace, as below:
1. Set the trace level to DEBUG for importexport in the indexserver traces, as below:
- In the SAP HANA studio, open the administration console of your system.
- On the Trace Configuration tab, click the pencil icon (Edit Configuration) to edit the database trace.
- In the Trace Configuration window, select the Show All Components checkbox.In the search bar, search for “import”.
- Scroll down to the “INDEXSERVER” service.
- Set the system trace level of “importexport” to “DEBUG” for the indexserver service.
- To save the configuration, choose the Finish button.
2. Perform the import or export again.
3. In the SAP HANA studio, in the administration console of your system, on the Diagnosis File tab, find the latest indexserver or indexserver_alert trace.
4. Open the trace file and look for the error message in the end.
5. After completing the investigation, change the importexport trace level back to ERROR.
Example trace entries for a failed import from an indexserver trace file:
446678 i ImportExport ImportExport.cpp(02099) : Scanning import path /DVD_HANA/test/SBO_TEST/: found 0 objects
446732 e ImportExport ImportExport.cpp(02112) : No objects to import found at location /DVD_HANA/test/SBO_TEST/
446753 i ImportExport ImportExport.cpp(06619) : Getting dependencies for 0 objects
451946 i ImportExport ImportExport.cpp(06870) : Creating 0 objects
458774 i ImportExport ImportExport.cpp(07043) : Building import list from 0 objects
458817 i ImportExport ImportExport.cpp(07336) : importing 0 items with flags 4 using 10 threads
458857 w ImportExport ImportExport.cpp(07382) : Data import skipped!
You can also monitor the export/import process using the following views:
For export: M_EXPORT_BINARY_STATUS
For import: M_IMPORT_BINARY_STATUS
SAP Business One Backup Service
As of release 9.0 PL14 and 9.1 PL04, SAP Business One, version for SAP HANA provides a backup service with a graphical user interface in the System Landscape Directory (SLD). This backup service automates and simplifies the export and import processes for your company schemas. An introduction video clip is available on YouTube.
It is recommended to use SAP Business One Backup Service over manual export and import and commands.
Enhancements of the backup service are gradually introduced in different patches. For best practices and known limitations, please refer to SAP Note 2072868 (S-User login required).
Caution: The schema export-import function provided by the backup service is based on the same SQL statements as manual export-import. You must be aware of the limitation of schema export-import, as outlined in SAP Note 2121486 (S-User login required). Regular full instance backup is required for system recoverability – the backup service provides the instance backup function as of release 9.1 PL05.
RSP for SAP Business One, version for SAP HANA
Additionally, Remote Support Platform for SAP Business One, version for SAP HANA 3.1 patch level 07, you can set up backup scheduling for schema export-import and instance level backup. For more information please refer to SAP Note 2157386 (S-User login required).