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.
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:
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 | |
|
<dftdbpath> | Uses storage paths of the existing database | Uses database path of existing database | Uses storage paths defined in the backup image |
|
X: | Uses storage paths of the existing database | Uses database path of existing database | Uses storage paths defined in the backup image |
|
/db2/databases | Uses storage paths of the existing database | Uses database path of existing database | Uses storage paths defined in the backup image |
|
/path1 | /path1, /path2, /path3 | Uses database path of existing database | /path1, /path2, /path3 |
|
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.
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. | /path1, /path2, /path3 | /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 |