Restoring data using the db_restore command

Note: This topic only applies to using db_restore in IAS. For using it in a standalone Db2® Warehouse, see Restoring data by using the db_restore command.

Run the db_restore command to restore a database, a schema, or a table that was backed up using the db_backup command. The restored object is in the same state that it was in when the backup copy was made.

From a database backup, you can only restore the whole database. To restore a schema or a table, a schema backup image produced by the db_backup -schema command must be used.

The db_restore command can be started only from the head node, but each MLN (multiple logical node) participates in the restore operation. Before you run the db_restore command, connect to the BLUDB database and run the db2 values current node command to confirm whether it's a catalog node. If the command returns a value of 0, the current node is the catalog node.

Important: If the db_restore command is issued from within an SSH command, the SSH command must be run outside the container, and the db_restore command must be issued as a sudo command. For example:

ssh -p 50022 bluadmin@9.32.246.72 "sudo -E db_restore -path /scratch/ -type frc -timestamp 20181231205807"

Note: For more information, refer to Incremental schema backup and restore.

Run the db_restore command as the bluadmin or database admin user.

For a schema restore to process tables that are enabled for Row and Column Access Control (RCAC), a user must be granted the LOGICAL BACKUP AND RESTORE OF RCAC PROTECTED DATA privilege. This is not required for the bluadmin user.

db_restore command syntax

For a database restore:

db_restore -type {frc|frh|inc} -timestamp timestamp -path path -[partitioned-restore]


db_restore -type {frc|frh|inc} -timestamp timestamp -tsm {sessions_used_for_backup (1-8)}
db_restore -type {frc|frh|inc} -timestamp timestamp -emc {sessions_used_for_backup (>=1)}

To terminate a failed database-level incremental restore operation

db_restore -abort

For a schema restore:

db_restore -type {frh | inc | del} -timestamp timestamp -path path -schema 
schema_name [-drop-existing] [-enable-row-mod-tracking] [-target-schema name] [-sessions N] 
[-image-check [hash | size | name |rowcount | none | only]] [-keep-rcac | -replace-rcac]

db_restore -schema schema_name -unlockschema
db_restore -schema schema_name -cleanup-failed-restore

For a single or multi table restore:

db_restore -type {frh | inc | del} -timestamp {timestamp} -path {path}
-schema {schema_name} -table {table_name} [-tablefile {table_filepath}] [-drop-existing]

Note: For more information, refer to Incremental schema backup and restore.

To display history information or other command information:

db_restore -history
db_restore -status
db_restore -version
db_restore {-h|--help}

-path

The path to the directory where the backup image is located. This directory must be locally addressable on the database server. If the backup is done to multiple paths, all the directory paths must be specified.

You can also use db_restore to restore a schema or tables from multiple paths using the -path option:

If you are using the same paths for backup and for restore, you must provide the paths in the same order as at the end of the backup. Run db_backup -history to find out the path order.
If you are using different paths for restore, ensure that the structure of the backup directory, the data/metadata files in the backup and their ownership/permissions are not altered or have not been tampered with.

-partitioned-restore

Directs restore to locate image for each partition in a subfolder of the 'MLN $4N' format. Required if backup was taken by using the -partitioned-backup option. Not required if the 'MLN $4N' string is already included in the path that is provided by the -path option.

-type

The type of restore that is to be done:

frc: A full restore from an offline backup image.
frh: A full restore from an online backup image.
inc: A full restore from an incremental backup image.
del: A full restore from a delta incremental backup.

-timestamp

A timestamp that uniquely identifies the backup image that is to be restored.

-schema

Schema to be restored. When not specified, whole database is restored. Use '"schema_name"' for delimited schema names.

-table

A table to be restored. Use '"table_name"' for delimited table names.

Table name specified to the option applies to name that existed at the start of the db_backup command that created the image. Any RENAME TABLE operations that happened after the backup was taken are ignored. If the table was renamed after the backup was taken, then two copies of the table might exist after the restore: one that was restored and the renamed original.

-tablefile

Specifies a full path with the file name. Each table name in the file is case sensitive and must be provided in a separate line.

-drop-existing

Required with the -schema and -table or -tablefile parameters if the table(s) already exists, otherwise it results in an error. The schema/table is first dropped and then recreated from the backup. If the schema/table doesn't exist, -drop-existing is optional to use.

This option must be specified if target schema/table exists.

Note: Some schema objects can not be dropped via the db_restore with the -drop-existing option. If you receive a failure during restore to drop any objects, you will need to drop the objects manually and re-issue the restore.

-tsm

Specifies that the image is to be restored directly from IBM Spectrum Protect.

-emc

Specifies that the image is to be restored directly from EMC NetWorker.

-sessions

Specifies the number of I/O sessions between Integrated Analytics System and IBM Spectrum Protect (-tsm) or EMC Net Worker (-emc). This parameter has no effect when you restore from tape, disk, or another local device. It is recommended to use the same number of sessions as of the backup image being restored; the default number is 1.

-history

Displays information about the 10 most recent restore operations. Information about other restore operations is stored in the file /scratch/bluadmin_BNR/restore_history.txt.

Note:

/scratch/bluadmin_BNR/restore_history.txt is not updated after every restore. It is only updated on db_restore -history command invocation.

If db_restore -history command fails, the work around is:

Check /scratch/bluadmin_BNR/restore_history.txt
If the file does not exist or is not updated, use db2 list history command as described in https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.1.0/com.ibm.db2.luw.admin.cmd.doc/doc/r0001991.html

-abort

Use this option only when an error has occurred during an incremental restore operation and you need to terminate it to connect to the database.

Note: Do not use -abort option while an incremental restore is in progress.

-enable-row-mod-tracking

Restores a schema backup image taken from schema not enabled for row modification tracking and enables target schema for row modification tracking.

All tables that can be created as row modification tracking will be, and will have SYSROWID, CREATEXID, DELETEXID columns created and properly populated.

If a table cannot be enabled for row modification tracking, it will still be created and restored with data. These tables must be manually dropped from the schema or moved to another schema before the schema enabled for row modification tracking can be backed up. For more information, refer to Schema enabled for row modification tracking.

-status

Print if db_backup or db_restore is in progress.

-unlockschema

Unlock schema after a failed restore. Does not complete previously failed operation. Can only be specified for restore of schema that is enabled for row modification tracking.

-cleanup-failed-restore

Cleanup after a failed schema restore. Drops all objects and the schema itself. In case the schema cannot be dropped, it is unlocked. Can only be specified for restore of schema that is enabled for row modification tracking.

-target-schema name

Optional. Restores a backup image using different schema name without dropping the original schema. -drop-existing affects new schema name.

-sessions N

Spawns N threads to restore individual tables concurrently. The default value is 4.

Optional. Performs pre-restore integrity checks of the backup image. All schema integrity checks that were set during backup are performed by default. However, selecting a value with this option causes other checks to be skipped unless they were selected too.

hash: Computes and compares hash values for all files in a backup image.
size: Compares the sizes of all files in a backup image.
name: Identifies missing or unexpected file names.
rowcount: Processes data files and compares the current number of rows to the number at backup time.
none: Does not perform any integrity checks.
only: Performs integrity checks only without invoking a restore (validates a backup image).

-keep-rcac

RCAC enabled tables are truncated (instead of DROP/CREATE), preserving all RCAC rules. This option is only allowed when target DDL (for all RCAC-enabled tables) exactly matches what is stored in the backup image. This is the default behavior.

-replace-rcac

RCAC enabled tables are dropped and recreated. Also, all rules are restored and reenabled. The option requires the user to run db_restore to hold the SECADM privilege.

Examples

Restore from a full offline backup. No rollforward is needed when you restore from a full offline backup. You should be able to connect to the database after the db_restore command finishes:
```
db_restore -path /backup/Backup-Demo/Off-bkp -type frc -timestamp 20170824205807
```
Restore from a full online backup. The db_restore command automatically takes care of the rollforward operation internally, which is the default behavior:
```
db_restore -path /backup/Backup-Demo/Onl-bkp -type frh -timestamp 20170824221601
```
Restore from an incremental backup. The db_restore command automatically takes care of the rollforward operation internally, which is the default behavior:
```
db_restore -path /backup/Backup-Demo/online-inc -type inc -timestamp 20170824221601
```

Restore from a full online backup from more than one path:

db_restore -path /backup/Backup-Demo/Off-bkp/path1, /backup/Backup-Demo/Off-bkp/path2 -type frc -timestamp 20170824205807

db_restore -path /backup/Backup-Demo/Off-bkp/path1,/backup/Backup-Demo/Off-bkp/path2 -type frc -timestamp 20170824205807

Restore from an offline IBM Spectrum Protect backup:

db_restore -type frc -timestamp 20170824205805 –tsm

db_restore -type frc -timestamp 20170824205805 –tsm -sessions 2

Restore from an online IBM Spectrum Protect backup:

db_restore -type frh -timestamp 20170824205805 –tsm

db_restore -type frh -timestamp 20170824205805 –tsm -sessions 4

Restore from an online EMC NetWorker backup:

db_restore -type frh -timestamp 20180410024512 –emc

db_restore -type frh -timestamp 20180410024512 –emc -sessions 5

Restore from an offline EMC NetWorker backup:

db_restore -type frh -timestamp 20180511024512 –emc

db_restore -type frh -timestamp 20180511024512 –emc -sessions 10

Restore from an Incremental EMC NetWorker backup:

db_restore -type inc -timestamp 20180612024512 –emc

db_restore -type inc -timestamp 20180612024512 –emc -sessions 15

Restore from an incremental IBM Spectrum Protect backup:

db_restore -type inc -timestamp 20170824205805 –tsm

db_restore -type inc -timestamp 20170824205805 –tsm -sessions 6

Restore schema:

db_restore -schema gbnr_schema -type frh -timestamp 20171210221601 -path /backup/Backup-Demo/Onl-bkp/

Restore a table in a schema:

db_restore -schema gbnr_schema -table gbnr_table -type del -timestamp 20171211221601 -path /backup/Backup-Demo/Del-bkp/

Restore schema with a delimited name:

db_restore -schema '"Gbnr sChEmA"' -type inc -timestamp 20171212221601 -path /backup/Backup-Demo/Inc-bkp/

Restore table with a delimited name:

db_restore -schema gbnr_schema -table '"Gbnr tAbLe"' -type frh -timestamp 20171213221601 -path /backup/Backup-Demo/Onl-bkp/

Restore table when it is already existing in the schema:

db_restore -schema gbnr_schema -table gbnr_table -drop-existing -type del -timestamp 20171214221601 - /backup/Backup-Demo/Del-bkp/

Restore a subset of tables:

db_restore -schema gbnr_schema -tablefile /backup/Backup-Demo/Table-File -type inc -timestamp 20171211221601 -path /backup/Backup-Demo/Inc-bkp/

Restore a subset of tables when they are already existing in the schema:

db_restore -schema gbnr_schema -tablefile /backup/Backup-Demo/Table-File -drop-existing -type del -timestamp 20171211221601 -path /backup/Backup-Demo/Del-bkp/

Restore from a full online schema backup from more than one path:

db_restore -schema gbnr_schema -type frh -timestamp 20200123221601 -path /external_mnt/NFS1/path1, /external_mnt/NFS2/path2

db_restore -schema gbnr_schema -type frh -timestamp 20200123221601 -path /external_mnt/NFS1/path1,/external_mnt/NFS2/path2

Restore the backup taken on a regular schema and enable target schema for row modification tracking:

db_restore -schema gbnr_schema -type frh -timestamp 20171210221601 -path /backup/Backup-Demo/Onl-bkp/ -enable-row-mod-tracking

Restore a schema when it is already existing:

db_restore -schema gbnr_schema -type inc -timestamp 20171210221601 -path /backup/Backup-Demo/Onl-bkp/ -drop-existing

Unlock a schema enabled for row modification tracking after a failed restore:
```
b_restore -schema gbnr_schema -unlockschema
```
Clean up schema enabled for row modification tracking after a failed restore:
```
db_restore -schema gbnr_schema -unlockschema
```
Check restore version:
```
db_restore -version
```
Check restore status:
```
db_restore -status
```