DB2 Version 9.7 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's also possible to redefine the storage paths that are associated with the database. The database path and the storage paths are set by using a combination of TO, ON, and DBPATH ON with the RESTORE DATABASE command.

For example, here are some valid RESTORE commands for databases enabled for automatic storage:

   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 (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 ON is not specified, 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 above 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
RESTORE DATABASE command	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	/path1, /path2, /path3	Uses database path of existing database	/path1, /path2, /path3
`RESTORE DATABASE TEST5 ON E:\newpath1, F:\newpath2 DBPATH ON D:`	D:	E:\newpath1, F:\newpath2	Uses database path of existing database	E:\newpath1, F:\newpath2

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 or not automatic storage is enabled for a database within a backup image. The storage paths associated with the database are displayed if automatic storage is enabled.

For multi-partition automatic storage enabled databases, the RESTORE DATABASE command has a few extra implications:

The database must use the same set of storage paths on all database partitions.
Issuing a RESTORE command with new storage paths can only be done on the catalog database partition, which will set 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)
RESTORE DATABASE command	Issued on database partition #	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
`RESTORE DATABASE TEST1`	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.	/path1, /path2, /path3		/path1, /path2, /path3
`RESTORE DATABASE TEST2 ON /path1, /path2, /path3`	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