i have a question about best practice or possibilities in cloning a SAP HANA Database schema.
Some informations about our scenario:
- We developing a java-application in the Hana Cloud Platform which stores the data in the SAP HANA Database
- We are using Core Data Services for the database development
- The database project includes about 100 files (procedures, functions, views)
- All database objects are stored in one schema
Our problem is that we need our database schema multiple times in the same SAP HANA instance. (1 schema for development, 1 for QA, 1 for live-demos, ..., ...)
So i need a smooth possibility to clone the database schema.
Currently i have found following ways to clone the schema (but they are not very good):
1. Copy all CDS Files into a second SAP HANA Repository package.
- When i update existing tables the tables keeps their data
- I have to manually rename the schema name in every file.
- I have to manually change the namespace in each file (cause the package is different, and the package-name has to be the namespace...)
- (Same problem when i want to update the cloned schema)
The effort for cloning the schema is very big and thats not really smooth.
2. Export and Import the schema
With this two commands i can clone a schema very quick
EXPORT "schema"."*" AS BINARY INTO '/tmp' WITH REPLACE CATALOG ONLY;
IMPORT "schema"."*" AS BINARY FROM '/tmp' WITH REPLACE CATALOG ONLY RENAME SCHEMA "schema" TO "schema_clone";
- Very quick and smooth to use.
- When i update existing tables all data get lost (very bad)
- Because the SAP HANA Instance is in the HCP, we don't have access to the Export/Import-Directory.
So we run in following problem: Clone schema with procedure X. Rename procedure X in Y. Clone Schema again.
--> Both procedures will be imported, cause the old procedure still exists in the folder /tmp. We still can't use a different folder because we can't create one and we only can export in existing folders.
Loosing all data and importing deleted old objects is also not very smooth.
Does anybody know a better way to clone schemas?
Thanks for help