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

Planning Database Security and Recovery
HP NonStop SQL/MX Installation and Management Guide—523723-004
5-25
Using Backup and Restore 2 to Create Offline RDF
Backup Databases
3. Create DDL scripts for all replicated database objects. Use the LOCATION clause
to specify the Guardian file names, making sure that that they are the same on the
backup system as the primary system. Change the primary catalog name to the
backup catalog name for each table created. Alternately, the catalog name can be
removed, and a default catalog name can be used.
4. Execute the DDL statements in the specified order to create the backup database.
Populating an Online Backup Database
For each table, execute statements of the form:
INSERT INTO backup table SELECT * FROM primary table
Note that the INSERT...SELECT statement refers to a table in the backup and primary
catalog.
Synchronizing an RDF Backup Database
Whether created and populated online or offline, an RDF backup database must be
synchronized with the primary database before use. This process is described in the
RDF System Management Manual.
Using RDF to Recover Dropped Tables
NonStop SQL/MX can automatically save the DDL for any individually dropped table,
which can be retrieved later if the dropped table needs to be re-created. In addition, if
the table needs to be recovered with TMF or re-created for use in an RDF backup
database, the full Guardian file names are preserved and can be used to create
identical file names.
You might not want the EMS log and OSS file space occupied by dropped table
information. To control the saving of dropped table DDL, use a control query default:
control query default SAVE_DROPPED_TABLE_DDL 'OFF';
Alternately, you can change the setting in the system defaults table:
insert into
system-catalog.SYSTEM_DEFAULTS_SCHEMA.SYSTEM_DEFAULTS
(ATTRIBUTE, ATTR_VALUE) values ('SAVE_DROPPED_TABLE_DDL',
'OFF');
The default setting is ON to ensure that you do not lose DDL for individually dropped
objects without intending to do so. Note that the DDL is saved only for user base tables
that are explicitly dropped with a DROP TABLE statement, DDL is not saved for tables
that are implicitly dropped. For example:
•
DROP SCHEMA CASCADE statement
•
DROP TRIGGER statement