SQL/MX 2.x Installation and Management Guide (G06.24+, H06.03+)

Performing Recovery Operations
HP NonStop SQL/MX Installation and Management Guide—523723-004
12-9
Recovering Tables
Recovering Table Privileges
NonStop SQL/MX does not save the DDL for GRANT and REVOKE statements, so if
you accidentally drop a table that includes security information created by these
statements, you must restore the table’s security attributes in the table’s metadata
before you can recover the table data, labels, and resource forks from a TMF online
dump. For more information, see Example: Recovering Table Privileges on page 12-11.
Table Recovery Procedures
To recover a dropped table, you must execute these statements or commands in the
order shown:
1. The CREATE TABLE statement
2. ALTER TABLE statements necessary to add columns originally added by ALTER
TABLE
3. CREATE INDEX statements needed to enforce unique constraints or primary key
constraints
4. Any other CREATE INDEX statements
5. ALTER TABLE statements necessary to add constraints
6. ALTER TRIGGER DISABLE statements
7. If applicable, DELETE and INSERT statements to reapply previously granted or
revoked table privileges. (For more information, see Recovering Table Privileges
on page 12-9.)
8. A RECOVER FILES command with the names of all partitions and resource forks
of the tables and indexes just re-created
The recovery process for tables can be much more complex than one involving only
views and indexes. For example, if TMF is not configured for file recovery, recovery
may not be possible. For this reason, it is vital that you heed the precautions listed
earlier in this discussion. And, as cautioned previously, you should always leave the
SAVE_DROPPED_TABLE_DDL attribute set ON.
Caution. Unless performed with great care and precision, SQL/MX table recovery involves
risk of database corruption and loss of data integrity. This operation should be performed only
by experienced users of NonStop SQL/MX and TMF who understand:
•
How objects are defined in the SQL/MX metadata and the ramifications of altering those
definitions.
•
How to use the mxtool utility as described in the SQL/MX Reference Manual.
If no one with this expertise is present at your site, contact the HP Global Customer Support
Center (GCSC) or your service provider before proceeding.