Restoring Db2 Warehouse from an offline backup using the restore script

Important: IBM Cloud Pak for Data Version 4.7 will reach end of support (EOS) on 31 July, 2025. For more information, see the Discontinuance of service announcement for IBM Cloud Pak for Data Version 4.X.

Upgrade to IBM Software Hub Version 5.1 before IBM Cloud Pak for Data Version 4.7 reaches end of support. For more information, see Upgrading IBM Software Hub in the IBM Software Hub Version 5.1 documentation.

You can use a provided script file to restore Db2 Warehouse backup images, with or without encryption.

The restore script can restore:

  • An encrypted backup
  • An unencrypted backup
  • An external Db2 Warehouse database from the same or different form factor (such as a bare metal Db2 deployment within the same platform family)
  • The backup from an earlier Db2 Warehouse version

The following sections provide usage information for the db_restore_extdb.sh script, and details on using the script to restore with different options.

Note: You can only use the script to restore full offline backup images. If you are using incremental/online backup images, use the native Db2 Warehouse restore commands.

Syntax

Read syntax diagramSkip visual syntax diagram /db2u/scripts/db_restore_extdb.sh --bkp-dirdirectory--bkp-tarfilename --bkp-timestamptimestamp --dbnamedbname--dblistlist --keystore-dirdirectory--keystore-tarfilename --drop-default-db --replace --cleanup --verbose --help

Options:

--bkp-dir directory
Specifies the directory where the backup images are located. Choose this option or the --bkp-tar option.
--bkp-tar filename
Specifies a .tar file that contains the backup images.
--bkp-timestamp timestamp
The timestamp of the backup image to use for the restore.
--dbname dbname
The name of the database to use when restoring the backup image.
--dblist
A comma-separated list of database names to use when restoring the backup image.
--keystore-dir directory

The directory where the backup native encryption keystore is located. This directory must only contain the keystore files. Choose this option or the --keystore-tar option.

--drop-default-db
Drops the existing databases. This keyword must be included when you restore a Db2 Warehouse database.
--replace
Replaces the existing database.
--keystore-tar
Specifies a .tar file that contains the backup native encryption keystore.
--cleanup
Specifies to clean up all backup images.
--verbose
Use this flag to view explicit commands that the script is performing or to view errors.
--help
Use this flag to display help for the tool.

Restoring an encrypted backup

To restore an encrypted backup, follow these steps:

  1. Save the Db2 Warehouse database backup images and the native encryption keystore in a single location. For example, /mnt/backup/db2ubkp.
  2. The Db2 Warehouse instance owner/group must have read-write access to the backup directory where the backup images and keystore files are saved. You can set the required mode-bits and permissions by issue a command:
    chmod 755 /mnt/backup/db2ubkp
chown db2inst1:db2iadm1 /mnt/backup/db2ubkp
  3. Run the following command to restore the backup:
    db_restore_extdb --bkp-dir /mnt/backup/db2ubkp --bkp-timestamp timestamp --drop-default-db --keystore-dir /mnt/backup/db2ubkp

    Replace dbname with the case-sensitive database name, and timestamp with the timestamp of the backup image. You can obtain the output log for the restore command by running cat ${SUPPORTDIR}/db_restore_extdb.log.

    Note: If your backup was copied from a different instance or remote cluster, you must use the --drop-default-database parameter, instead of the --replace parameter.

Restoring an unencrypted backup

To restore an unencrypted backup, follow these steps:

  1. Save the Db2 Warehouse database backup images in a single location. For example, /mnt/backup/db2ubkp.
  2. The Db2 Warehouse instance owner/group must have read-write access to the backup directory where the backup images and keystore files are saved. You can set the required mode-bits and permissions by issue a command:
    chmod 755 /mnt/backup/db2ubkp
chown db2inst1:db2iadm1 /mnt/backup/db2ubkp
  3. Run the following command to restore the backup:
    db_restore_extdb --bkp-dir /mnt/backup/db2ubkp --bkp-timestamp timestamp --drop-default-db

    Replace dbname with the case-sensitive database name, and timestamp with the timestamp of the backup image. You can obtain the output log for the restore command by running cat ${SUPPORTDIR}/db_restore_extdb.log.

Restoring an external database

The backup image (and keystore, if encrypted) can also be restored from an external source. For example, you might want to re-initialize Db2 Warehouse by using a database from an on-premises deployment.

You can use the --dbname dbname option to set the database name to the same as the source in this scenario.