DB2 Version 10.1 for Linux, UNIX, and Windows

Implications for restoring databases

The RESTORE DATABASE command is used to restore a database from a backup image.

During a restore operation it is possible to choose the location of the database path, and it is also possible to redefine the storage paths that are associated with the storage groups. The database path and the storage paths are set by using a combination of TO, ON, and DBPATH ON with the RESTORE DATABASE command, or using the SET STOGROUP PATHS command.

For example, here are some valid RESTORE commands:
   RESTORE DATABASE TEST1
   RESTORE DATABASE TEST2 TO X:
   RESTORE DATABASE TEST3 DBPATH ON D:
   RESTORE DATABASE TEST3 ON /path1, /path2, /path3
   RESTORE DATABASE TEST4 ON E:\newpath1, F:\newpath2 DBPATH ON D:
As it does in the case of the CREATE DATABASE command, the database manager extracts the following two pieces of information that pertain to storage locations:
  • The database path (which is where the database manager stores various control files for the database)
    • If TO or DBPATH ON is specified, this indicates the database path.
    • If ON is used but DBPATH ON is not specified with it, the first path listed with ON is used as the database path (in addition to it being a storage path).
    • If none of TO, ON, or DBPATH ON is specified, the dftdbpath database manager configuration parameter determines the database path.
    Note: If a database with the same name exists on disk, the database path is ignored, and the database is placed into the same location as the existing database.
  • The storage paths of each storage group (where the database manager creates automatic storage table space containers)
    • If ON is specified, all of the paths listed are considered storage paths, and these paths are used instead of the ones stored within the backup image. If the database contains multiple storage groups, every defined storage group uses the new storage group paths.
    • If the SET STOGROUP PATHS command is used, the storage paths provided are used for the specified storage group instead of the ones stored within the backup image.
    • If ON is not specified and the SET STOGROUP PATHS command is not used, no change is made to the storage paths (the storage paths stored within the backup image are maintained).
To make this concept clearer, the same five RESTORE command examples presented previously are shown in the following table with their corresponding storage paths:
Table 1. Restore implications regarding database and storage paths
RESTORE DATABASE command No database with the same name exists on disk Database with the same name exists on disk
Database path Storage paths Database path Storage paths 
RESTORE DATABASE TEST1
 dftdbpath Uses storage paths of the existing database Uses database path of existing database Uses storage paths defined in the backup image 
RESTORE DATABASE TEST2 TO X:
 X: Uses storage paths of the existing database Uses database path of existing database Uses storage paths defined in the backup image 
RESTORE DATABASE TEST3
DBPATH ON /db2/databases
 /db2/databases Uses storage paths of the existing database Uses database path of existing database Uses storage paths defined in the backup image 
RESTORE DATABASE TEST4
ON /path1, /path2, /path3
 /path1 All storage groups use /path1, /path2, /path3 for their storage paths Uses database path of existing database All storage groups use /path1, /path2, /path3for their storage paths 
RESTORE DATABASE TEST5
ON E:\newpath1, F:\newpath2
DBPATH ON D:
 D: All storage groups use E:\newpath1, F:\newpath2 for their storage paths Uses database path of existing database All storage groups use E:\newpath1, F:\newpath2 for their storage paths

For those cases where storage paths have been redefined as part of the restore operation, the table spaces that are defined to use automatic storage are automatically redirected to the new paths. However, you cannot explicitly redirect containers associated with automatic storage table spaces using the SET TABLESPACE CONTAINERS command; this action is not permitted.

Use the -s option of the db2ckbkp command to show whether storage groups exist for a database within a backup image. The storage groups and their storage paths are displayed.

For multi-partition databases, the RESTORE DATABASE command has a few extra implications:
  1. The database must use the same set of storage paths on all database partitions.
  2. Issuing a RESTORE command with new storage paths can be done only on the catalog database partition, which sets the state of the database to RESTORE_PENDING on all non-catalog database partitions.
Table 2. Restore implications for multi-partition databases
RESTORE DATABASE command Issued on database partition # No database with the same name exists on disk Database with the same name exists on disk (includes skeleton databases)
Result on other database partitions Storage paths Result on other database partitions Storage paths
RESTORE DATABASE TEST1 Catalog database partition A skeleton database is created using the storage paths from the backup image on the catalog database partition. All other database partitions are placed in a RESTORE_ PENDING state. Uses storage paths defined in the backup image Nothing. Storage paths have not changed so nothing happens to other database partitions Uses storage paths defined in the backup image
Non-catalog database partition SQL2542N or SQL2551N is returned. If no database exists, the catalog database partition must be restored first. N/A Nothing. Storage paths have not changed so nothing happens to other database partitions Uses storage paths defined in the backup image
RESTORE DATABASE TEST2 ON /path1, /path2, /path3 Catalog database partition A skeleton database is created using the storage paths specified in the RESTORE command. All other database partitions are place in a RESTORE_ PENDING state. All storage groups use /path1, /path2, /path3 for their storage paths   All storage groups use /path1, /path2, /path3 for their storage paths
Non-catalog database partition SQL1174N is returned. If no database exists, the catalog database partition must be restored first. Storage paths cannot be specified on the RESTORE of a non-catalog database partition. N/A SQL1172N is returned. New storage paths cannot be specified on the RESTORE of a non-catalog database partition. N/A
Related tasks:
Using restore
Related reference:
RESTORE DATABASE command