If you are not able to properly backup your data warehouse (DWH) with the means provided by your DBMS, there is still a way to perform a backup. Just follow these steps:
- Create a parallel instance of Data Virtuality Server (don't forget to configure the port offset!).
- Connect to the 'new' DWH as DWH (this is where your DWH will be stored to).
- Connect to the DWH you want to backup directly as a new data source on the parallel instance with schema name 'source DWH' and importer.tableTypes="TABLE".
- Execute the following Code on within datavirtuality Studio connected to the parallel instance (successful inserts will be logged):
DECLARE string sourceTable;
DECLARE string targetTable;
LOOP ON (SELECT "Name" as TableName, NameInSource as DVName FROM "SYS.Tables" WHERE SchemaName='sourceDWH' ORDER BY "Name") as cur
sourceTable = 'sourceDWH.'||cur.TableName;
targetTable = cur.DVName;
EXECUTE IMMEDIATE 'SELECT a.* INTO '||targetTable||' FROM (SELECT * FROM '||sourceTable||') as a';
EXEC SYSADMIN.LogMsg(level => 'INFO', context => 'org.datavirtuality', msg => 'BACKUP-backup performed of '||sourceTable);