Forum

[tajones]

We have a scenario right now where we would like to restore to a backup taken prior to when certain objects were created in order to remove a circular reference we cannot seem to resolve. When we restore we are still seeing those older objects even though they are not in the backup. What steps do we need to take in order to restore and end up with only those objects within the backup?

Our restore command is the following:
wxrestore -s localhost:**** -u sys -p ******* -d /tmp/prod02_restore/prod02_upto_100M_2019-06-17 -v --noimages > prod02_upto_100M_2019-06-17_RESTORE.log 2>&1 &

One odd thing to note is the last time we ran this restore command, it ran for 2.5 hours with the following error. It's odd because this is one of the views that was part of the circular reference, but we specifically chose this backup because it does not contain the other view that was involved with the circular reference. It was prior to when it was created. And SSE19.PRODUCT exists currently, even though the restore says it errored.

1170 create table/view "SSE19"."PRODUCT" (FAILED)
When executing: create or replace VIEW "PRODUCT" as
select distinct
sq1.*
...
[HY000] [Kognitio][WX2 Driver][localhost:****] CI0011: Insufficient memory

Restore took 2h 31m 36s
2612 jobs executed, of which 2611 succeeded (2 with warnings) and 1 failed.

[michaeld]

Hi,

Restoring a backup doesn't remove pre-existing objects, it only creates objects and inserts data as specified in the backup directory and the restore command. To remove preexisting objects and restore from a clean slate, you would need to take a full backup, then drop the cluster, create a new cluster, and restore to the new cluster.

However in this case your mutual view dependency problem can be solved without restoring a backup. Assuming the mutually dependent views are LUOY.PRODUCT and SSE19.PRODUCT, you can:

1. run 'explain LUOY.PRODUCT' to see the current column names and types
2. Create an empty table with 'create ram only table rot1(< list of columns matching the above>)'
3. run 'create or replace view LUOY.PRODUCT as select * from rot1'
4. now you can drop both views

The above steps could alternatively be run on SSE19.PRODUCT.

Regards,
Mike

[tajones]

I'm late to reply, but these steps did resolve the main issue. But thank you for the overview of how the backup works as well.