Restoring a User Database or Table - TARA/ABU

Restoring a User Database or Table

All-AMPs Restore of a Complete Database

An all-AMPs restore of a complete database from an archive of a complete database:

1 Drops all objects in the resident version of the database. Join and hash indexes created prior to Teradata Tools and Utilities 13.00.00 are also dropped. See “Join and Hash Indexes in Restoring” on page 51 for information on join and hash indexes in Teradata Tools and Utilities 13.00.00 and later.

2 Restores all objects in the archive. For a complete list of objects supported by Teradata ARC, see “Appendix A Database Objects” on page 261.

All-AMPs Restore of a Selected Table

An all-AMPs restore of a selected data table from an archive of a complete database restores only the dictionary rows and data for the requested table. Other objects in the database are not restored. (For a complete list of objects supported by Teradata ARC, see “Appendix A Database Objects” on page 261.) Additionally, join and hash indexes created prior to Teradata Tools and Utilities 13.00.00 are not restored. See “Join and Hash Indexes in Restoring” on page 51 for information on those indexes in Teradata Tools and Utilities 13.00.00 and later.

All-AMPs Restore of a Full Database from a Selected Table Archive

An all-AMPs restore of a full database from an archive that was created for specific data tables restores only the dictionary rows and data for the tables that were originally archived.

Restoring Selected Partitions

Selected partitions can be directly backed up and restored with a PARTITIONS WHERE option that restricts the list of rows processed. The PARTITIONS WHERE option operates on complete table partitions. A RESTORE (or COPY) operation wipes out selected partitions (specified by the PARTITIONS WHERE option) of an existing target table before recovering the rows stored on the backup tape. A backup operation on selected partitions is allowed with the same all-AMP BAR styles as for full tables.

Restoring selected partitions is affected by the maintenance activities that can occur on a table. For example, a user can add or delete a column, add or delete a secondary index, or change the partitioning expression.

If a selected partition was archived prior to one of these changes, restoring that partition back to the table might not be possible. Restoring selected partition data back to a table even if the target table has some characteristics that are different from the source stored on tape is allowed. However, not all changes are allowed and some will prevent the restoration of the selected partition data. For a list of acceptable and non-acceptable differences, see “Changes Allowed to a PPI Table” on page 180 and “Restrictions on Archiving Selected Partitions” on page 179.

Note: Restoring selected partitions might take up CPU resources.

For an overview of backing up partitioned data with Teradata ARC, and the keywords for restoring partitioned data, see “Archiving Selected Partitions of PPI Tables” on page 35.

All-Amps Restore of Selected Partitions

The following options restore partitioned data:

PARTITIONS WHERE specifies the conditional expression that determines which rows to restore to the table. Use this option only if the following conditions are true:

The object is an individual table rather than a database.

The source and target tables have a defined PARTITIONS BY expression.

The restore is an all-AMP restore.

The table is excluded from the database object (via EXCLUDE TABLES) if the table belongs to a database object that is specified in the RESTORE script.

LOG WHERE conditionally inserts (log) archived rows that fall outside the partitions specified by the PARTITIONS WHERE conditional expression into a Teradata-generated error table. Teradata ARC inserts into the error table any rows that match the LOG WHERE conditional expression and fall outside the partitions specified by the PARTITIONS WHERE conditional expression.

Error Table Name and Structure

The restore/copy process for selected partitions creates Teradata-generated error tables. Rows are stored in error tables for the following reasons:

A row that is in the range of selected partitions being restored fails the target table integrity checks

A user does not want to restore a row but it satisfies the LOG WHERE condition

An error occurs while evaluating these conditions for a row

The restore/copy process for selected partitions creates an error table with the characteristics listed in Table 5:

Table 5: Error Table Characteristics 

Names
Database Name	If a database name is not specified (in the ERRORDB option or as a database qualifier of the error table name in the ERRORTABLES option), the error table resides in the same database as the target table being restored or copied. Otherwise, the error table resides in the specified database.
Error Table Name	If the error table name is not specified (in the ERRORTABLES option), the error table has the name RS_targettablename truncated on the right as needed to 30 characters; if truncated, a warning message is issued. For example, if restoring table TB1, the default name for the error table is RS_TB1.
	If a table has the same name as an existing error table in a database, an error occurs. Before the restore/copy job can be submitted, drop or rename this table, or specify another table or database name. Review the contents of the existing table to determine whether to drop it: If it is an error table, review for logged errors. If it is not an error table, consider using a different database or renaming for the error table.
Indexes
Primary Indexes	The primary index of an error table is non-unique and is not partitioned. The primary index columns are identical to the primary index columns of the target table.
Secondary Indexes	An error table does not have any secondary indexes or participate in any referential integrity relationships. See “Error Tables and Indexes” on page 58 for an example.
Restrictions for Error Tables
Do not share an error table between two or more restore/copy jobs. Each table targeted by a restore/copy job must have its own error table to ensure jobs run correctly.
Do not drop the error table until the restore/copy job finishes. The error table must not exist for non-restart of restore/copy job. It must already exist for a restart of restore/copy job.
Other Characteristics
Columns	All columns have the same data types and attributes as the target table except for column-level integrity constraints and not null attributes.
DBCErrorCode	DBCErrorCode has INTEGER data type, not null. It indicates the Teradata error causing the row to be inserted into the error table.
DBCOldROWID	DBCOldROWID has BYTE(10) data type, not null. It indicates the internal partition number, row hash, and row uniqueness value of the row at the time the backup was created.
Empty Table	If the error table is empty when the restore/copy job for the target table finishes, it is dropped.
No Constraints from Target Table	An error table does not have any of the NOT NULL, table-level, or column-level integrity constraints of the target table. This ensures that rows can be inserted into the error table that might fail the integrity constraints.
Protection Type	An error table has the same FALLBACK or NO FALLBACK protection type as the associated target table.
SET Table	An error table is always a SET table even if the target table is a MULTISET table. Duplicate rows are discarded.

Error Tables and Indexes

As noted in Table 5, error tables do not have secondary indexes, and primary indexes are non-unique. For example, assume that selected partitions of the following target table are being restored:

CREATE TABLE SalesHistory

   (storeid INTEGER NOT NULL,

   productid INTEGER NOT NULL CHECK(productid > 0),

   salesdate DATE FORMAT 'yyyy-mm-dd' NOT NULL,

   totalrevenue DECIMAL(13,2),

   totalsold INTEGER,

   note VARCHAR(256))

UNIQUE PRIMARY INDEX (storeid, productid, salesdate)

PARTITION BY RANGE_N(salesdate BETWEEN

   DATE '1997-01-01' AND DATE '2000-12-31' 

   EACH INTERVAL '7' DAY)

INDEX (productid)

INDEX (storeid);

The restore job creates the following error table by default. The bold text points out pertinent differences from the target table definition.

CREATE SET TABLE RS_SalesHistory, FALLBACK     

   (storeid INTEGER,

   productid INTEGER,

   salesdate DATE FORMAT 'yyyy-mm-dd',

   totalrevenue DECIMAL(13,2),

   totalsold INTEGER,

   note VARCHAR(256),

   DBCErrorCode INTEGER NOT NULL,

   DBCOldROWID BYTE(10) NOT NULL   )

PRIMARY INDEX (storeid, productid, salesdate)

Restoring from a Dictionary Archive

If a database from a dictionary archive created by specifying individual tables is restored, only the dictionary rows for the specified tables are restored. Table 6 alphabetically lists the dictionary rows that are restored by an all-AMPs data restore or a dictionary tables restore of a user database.

Table 6: User Database Data Dictionary Rows Restored 

Table Rows	Description
Indexes	Definition of all indexed columns for all objects in the database
IndexName	Definition of named indexes in the table
TVM	Definition of all objects in the database
TVFields	Definition of all columns for all objects in the database
TriggersTbl	Definition of all triggers in the database

Table 7 lists the dictionary table rows that are restored by an all‑AMPs restore of a specific user data table or journal table.

Table 7: User Table Dictionary Rows Restored

Tables Rows	Description
Indexes	Columns in tables that are indexes
IndexName	Indexes in the table that have names
TVM	Table names
TVFields	Columns for the table

Restoring from a Journal Archive

If an all-AMPs archive of a journal table is restored, the restored permanent journal goes to a different subtable than the journal currently writing the updates.

A restored journal overlays any change images that were restored to the same AMPs. During normal processing, the Teradata Database writes after-change images to a different processor than the one with the original data row. If after-change images from an archive are restored, the rows are written to the processor that contains the data row to which the change applies.