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

Planning Database Security and Recovery
HP NonStop SQL/MX Installation and Management Guide—544536-007
5-8
Recovering Dropped Database Objects
Recovering Dropped Database Objects
The method for recovering an accidentally dropped SQL/MX object depends on
whether that object is a catalog, table, index, or view.
Recovery of a single view or index is usually a straightforward operation. Recovery of a
table or catalog, however, can be complex and difficult if there are multiple dependent
objects. So, for safety's sake, you should take these precautions to prevent accidental
loss of an object or to simplify the recovery process if it does become necessary:
•
Ensure that the SAVE_DROPPED_TABLE_DDL attribute, defined in the
SYSTEM_DEFAULTS table, remains set on.
•
Maintain OBEY command files containing current MXCI command scripts for re-
creating your SQL/MX tables, indexes, and views. In particular, you must maintain
scripts for creating all views, because NonStop SQL/MX does not save the DDL for
views. You can use the MXCI SHOWDDL command to display the DDL syntax
used to create an object.
•
Save GRANT and REVOKE statements that you execute. The SHOWDDL
command does not display security information for tables. When a table is
dropped, the security information is saved in the form of DELETE and INSERT
statements on the security-related metadata tables. For more information, see
Recovering Table Privileges on page 12-9.
•
If you alter an object, be sure to alter the OBEY file used to create that object too.
•
Any time you perform an MXCI DDL operation, also request a TMF online dump for
the affected object. With each new dump, you decrease the number of tapes that
must be processed during any future recovery operation.
Caution. When set ON, the SAVE_DROPPED_TABLE_DDL attribute preserves the DDL
(including full Guardian file names) required to re-create SQL/MX tables and indexes if they
are dropped. The SAVE_DROPPED_TABLE_DDL attribute is set ON by default, and HP
recommends that you do not change or override this setting. Otherwise, recovery of dropped
SQL/MX objects becomes much more difficult and you will probably need to work closely with
the Global Mission Critical Solution Center (GMCSC) to accomplish the recovery.
The SAVE_DROPPED_TABLE_DDL attribute only affects the DROP TABLE statement, not the
DROP VIEW statement or the DROP INDEX statement. Therefore, it only protects you against
dropping an entire table, and thus against losing the data in that table.
SAVE_DROPPED_TABLE_DDL does not protect against losing the definitions of indexes or
views that were individually dropped. To ensure that individual indexes and views can be
re-created if necessary, you should either run the SHOWDDL command immediately after
issuing the CREATE VIEW or CREATE INDEX statement or save the actual DDL used for such
statements.
Always remember that no DDL is ever saved for a view, regardless of whether the view was
dropped individually or as part of a DROP TABLE, CASCADE command.
This discussion assumes that the SAVE_DROPPED_TABLE_DDL attribute setting is ON.