Restoring data by using the db_restore command

This command restores a database or schema that was backed up by using the db_backup utility. The restored database or schema 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.

For an IBM® Db2® Warehouse MPP deployment, the db_restore command can be started only from the head node but each MLN (multiple logical node) participates in the restore operation.
Note: For information, refer to Incremental schema backup and restore.
Important:
  • Run the db_restore command as the bluadmin or database admin user.
  • In MPP, the db_restore command should be invoked only from the Catalog node.
To determine if you are on the catalog node, type the following command when you are connected to the BLUDB database: 
db2 values current node
If the output of the command shows a value of 0, you are logged onto the Catalog node.

The db_restore command

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

For a database restore:
db_restore -type {frc|frh|inc} -timestamp timestamp -path path  [-comprlib compression_library] [-partitioned-restore]
db_restore -type {frc|frh|inc} -timestamp timestamp -tsm  [-comprlib compression_library]
db_restore -type {frc|frh|inc} -timestamp timestamp -emc  [-comprlib compression_library]
To abort the database incremental restore operation:
db_restore -type inc -abort
For a schema or table restore:
db_restore -type {frh|inc|del} -path path -timestamp timestamp -schema schema [-target-schema name]
[-drop-existing] [-enable-row-mod-tracking] [-unlockschema] [-cleanup-failed-restore] [-sessions N]
[-table table | -tablefile filepath]  [-keep-rcac | -replace-rcac] [-image-check [hash | size | name | rowcount | none | only] [-status]
db_restore -type {frh | inc | del} -timestamp timestamp -tsm -schema schema_name [-target-schema name] 
[-drop-existing] [-enable-row-mod-tracking] [-unlockschema] [-cleanup-failed-restore] [-sessions N] 
[-table table | -tablefile filepath] [-keep-rcac | -replace-rcac] [-image-check [name | rowcount | none | only]] [-status]
Note: For more information, refer to Incremental schema backup and restore.
For a schema restore from IBM Spectrum Protect server::
db_restore -tsm -delete-backup -timestamp <timestamp>
To display history information, or display command usage information:
db_restore -history
db_restore {-h|--help}
-type {frc|frh|inc|del}
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 timestamp
A timestamp that uniquely identifies the backup image that is to be restored. This is part of the file name of the backup image.
-path path
The full 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.
-partitioned-restore
Directs restore to locate image for each partition in a subfolder of format 'MLN $4N'. Required if backup was taking using -partitioned-backup option, unless string 'MLN $4N' is already included in path provided using -path option.
-schema schema_name
Name of the database schema to be restored. If not specified, the entire database is restored.
-table table_name
Name of the table to be restored. If not specified, the entire schema is restored.

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.

-comprlib compression_library
Indicates the name of the library that is used to decompress or decrypt a backup image. The path to the following libraries is $HOME/sqllib/lib.
Encryption libraries: libdb2encr.so
    Compression library: libdb2compr.so
    Encryption and compression libraries: libdb2compr_encr.so

The name must be a fully qualified path that refers to a file on the server. If this parameter is not specified, the Db2 database system attempts to use the library that is stored in the image. If the backup image is not compressed or encrypted, the value of this parameter is ignored. If the specified library cannot be loaded, the operation fails.

-drop-existing
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.
For a table restore:
  • If the table already exists and -drop-existing option is specified, the table is first dropped, then recreated from the backup.
  • If the table already exists and -drop-existing option is not specified, error message indicating that the table already exists is displayed.
  • If the table does not exist, the restore operation proceeds regardless of whether the -drop-existing option is specified.
-tsm
Specifies that the image is to be restored directly from IBM Spectrum® Protect. Contact your IBM representative for assistance.
-tsm -delete-backup <timestamp>
Requests to delete backup image associated with the timestamp on IBM Spectrum Protect Server (TSM). Only allowed for schema backups.
-tsm -list-backup
Lists timestamps for all backup images stored on IBM Spectrum Protect Server (TSM). Only allowed for schema backups.
-emc
Specifies that the image is to be restored directly from EMC NetWorker. Contact your IBM representative for assistance.
-abort
Terminate a database incremental restore operation before its completion. Use this option only when instructed to do so by an IBM representative.
-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.
-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.

-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
-sessions N
Spawns N threads to restore individual tables concurrently. The default value is 4. For a multi-schema restore, N threads are spawned for each schema.
-image-check hash | size | name | rowcount | none | only
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 re-created, and all rules are restored and re-enabled. Requires user running db_restore to hold SECADM privilege.
-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.
-image-check
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).

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 -type frc -timestamp 20170824205807 -path /mnt/external/Backup-Demo/Off-bkp
  • 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 -type frh -timestamp 20170824221601 -path /mnt/external/Backup-Demo/Onl-bkp
  • Restore from an incremental online backup. The db_restore command automatically takes care of the rollforward operation internally, which is the default behavior.
    db_restore -type inc -timestamp 20170824221601 -path /backup/Backup-Demo/online-inc
  • Restore from a full online backup from more than one path.
    db_restore -type frc -timestamp 20170824205807 -path /mnt/external/Backup-Demo/Off-bkp/path1, /mnt/external/Backup-Demo/Off-bkp/path2
  • Restore from an offline IBM Spectrum Protect backup.
    db_restore -type frc -timestamp 20170824205805 –tsm
  • Restore from an online EMC NetWorker backup.
    db_restore -type frh -timestamp 20180511024512 –emc