SQL/MP Installation and Management Guide

Planning Database Security and Recovery
HP NonStop SQL/MP Installation and Management Guide—523353-004
4-18
Backup Strategies
•
Backing up catalogs
•
Backing up partitions
•
Backing up indexes
•
Backing up views
•
Backing up collations
•
Backing up by volume or by file
•
Using OBEY command files for backup operations
For information about backing up OSS files, see the discussion of the pax utility in the
Open System Services Shell and Utilities Reference Manual.
When planning backup strategies, consider these guidelines:
•
You should back up audited SQL objects by using TMF online dumps, as
discussed under The TMF Subsystem on page 4-10. Preserving files by using
BACKUP is not effective for recovery if any files are open during the BACKUP
operation. Also, if you use the OPEN option, the image saved during the dump of
the database might not be consistent. Before you begin a BACKUP operation, you
should close and refresh the files by using PUP (D-series only) or SCF (G-series
only). Files must remain closed throughout the BACKUP operation to ensure
consistency.
•
Although not recommended as the primary archiving method, you can use
BACKUP to preserve audited in addition to the nonaudited files by using the
AUDITED option. BACKUP and RESTORE can recover the database only to the
time of the last backup; changes after that time are lost.
•
You can back up entire tables and indexes or individual partitions. Also, you can
specify indexes to be backed up or have them backed up automatically with the
underlying tables. When you back up a table, index, or view, you must also back up
any collations the object depends on because when the object is restored, it must
use the same collations as before it was backed up.
•
To archive nonaudited SQL tables, you must use BACKUP.
•
SQL catalogs are not automatically dumped to tape in a BACKUP operation unless
the SQLCATALOGS ON option is specified. Note, however, that the RESTORE
utility cannot directly recover a catalog. All the catalog tables are audited so that
they can be archived by using the TMF subsystem and recovered by using either
TMF volume recovery or file recovery.
•
Indexes backed up with INDEXES IMPLICIT, in effect by default, are not actually
copied. The index definition is backed up; when restored, the index is re-created.
Regardless of whether or not the index contained slack space, the restored index
is re-created without slack space. To back up indexes and retain slack space, use
the INDEXES EXPLICIT option.
Note. The BACKUP and RESTORE recovery method differs from recovery methods provided
by TMF. The BACKUP and RESTORE method is normally used as a secondary recovery
scheme. Remember that an SQL object restored by the RESTORE utility might not be
consistent with the current catalog description of the object.