The RESTORE DATABASE command restores a database that has been backed up using the DB2® backup utility. The restored database is in the same state that it was in when the backup copy was made.
For information on the restore operations supported by DB2 database systems between different operating systems and hardware platforms, see "Backup and restore operations between different operating systems and hardware platforms" in the Related concepts section.
Incremental images and images only capturing differences from the time of the previous capture (called a "delta image") cannot be restored when there is a difference in operating systems or word size (32-bit or 64-bit).
Following a successful restore operation from one environment to a different environment, no incremental or delta backups are allowed until a non-incremental backup is taken. (This is not a limitation following a restore operation within the same environment.)
Even with a successful restore operation from one environment to a different environment, there are some considerations: packages must be rebound before use (using the BIND command, the REBIND command, or the db2rbind utility); SQL procedures must be dropped and recreated; and all external libraries must be rebuilt on the new platform. (These are not considerations when restoring to the same environment.)
A restore operation run over an existing database and existing containers reuses the same containers and table space map.
A restore operation run against a new database reacquires all containers and rebuilds an optimized table space map. A restore operation run over an existing database with one or more missing containers also reacquires all containers and rebuilds an optimized table space map.
This command only affects the node on which it is executed.
The required connection will vary based on the type of restore action:
To restore to a new database at an instance different from the current instance, it is necessary to first attach to the instance where the new database will reside. The new instance can be local or remote. The current instance is defined by the value of the DB2INSTANCE environment variable.
>>-RESTORE--+-DATABASE-+--source-database-alias-----------------> '-DB-------' >--+-| restore-options |-+------------------------------------->< +-CONTINUE------------+ '-ABORT---------------' restore-options |--+-------------------------------------+----------------------> '-USER--username--+-----------------+-' '-USING--password-' >--+--------------------------------------------------------------------------------------------------+--> +-REBUILD WITH--+-+-ALL TABLESPACES IN DATABASE-+--+---------------------------------------+-+-----+ | | '-ALL TABLESPACES IN IMAGE----' '-EXCEPT--| rebuild-tablespace-clause |-' | | | '-| rebuild-tablespace-clause |----------------------------------------------' | '-+-TABLESPACE--+------------------------------------------------------------------+-+--+--------+-' | | .-,---------------. | | '-ONLINE-' | | V | | | | '-(----tablespace-name-+--)--+-----------------------------------+-' | | '-SCHEMA--+-----------------------+-' | | | .-,-----------. | | | | V | | | | '-(----schema-name-+--)-' | +-HISTORY FILE---------------------------------------------------------------------+ +-COMPRESSION LIBRARY--------------------------------------------------------------+ '-LOGS-----------------------------------------------------------------------------' >--+----------------------------+-------------------------------> '-INCREMENTAL--+-----------+-' +-AUTO------+ +-AUTOMATIC-+ '-ABORT-----' >--+-----------------------------------------------------------+--> +-USE--+-+-TSM--+-| open-sessions |----------+--| options |-+ | | '-XBSA-' | | | '-SNAPSHOT--+-----------------------+-' | | +-LIBRARY--library-name-+ | | '-SCRIPT--script-name---' | +-LOAD--shared-library--| open-sessions |--| options |------+ | .-,-------------. | | V | | '-FROM----+-directory-+-+-----------------------------------' '-device----' >--+---------------------+--------------------------------------> '-TAKEN AT--date-time-' >--+------------------------------------------------+-----------> +-TO--target-directory---------------------------+ +-DBPATH ON--target-directory--------------------+ '-ON--path-list--+-----------------------------+-' '-DBPATH ON--target-directory-' >--+-------------------------------------------+----------------> '-TRANSPORT--+----------------------------+-' '-STAGE IN--staging-database-' >--+-----------------------------+------------------------------> '-INTO--target-database-alias-' >--+---------------------------------------+--------------------> '-LOGTARGET--+-directory--------------+-' '-+-EXCLUDE-+--+-------+-' '-INCLUDE-' '-FORCE-' >--+-----------------------+--+----------------------------+----> '-NEWLOGPATH--directory-' '-WITH--num-buffers--BUFFERS-' >--+---------------------+--+----------------------+------------> '-BUFFER--buffer-size-' '-REPLACE HISTORY FILE-' >--+------------------+-----------------------------------------> '-REPLACE EXISTING-' >--+---------------------------------------+--------------------> '-REDIRECT--+-------------------------+-' '-GENERATE SCRIPT--script-' >--+----------------+--+----------------+-----------------------> '-PARALLELISM--n-' '-COMPRLIB--name-' >--+-------------------+--+-------------------------+-----------> '-COMPROPTS--string-' '-WITHOUT ROLLING FORWARD-' >--+-------------------+----------------------------------------| '-WITHOUT PROMPTING-' rebuild-tablespace-clause .-,---------------. V | |--TABLESPACE--(----tablespace-name-+--)------------------------| open-sessions |--+------------------------------+-----------------------------| '-OPEN--num-sessions--SESSIONS-' options |--+-------------------------------+----------------------------| '-OPTIONS--+-"options-string"-+-' '-@--file-name-----'
Table space names are required when the TRANSPORT option is specified. This option could take as much time as a full restore operation.
Schema names are required if the TRANSPORT option is specified. The SCHEMA option is only valid when the TRANSPORT option is specified.
Also, you cannot use the SNAPSHOT parameter with any restore operation that involves a table space list, which includes the REBUILD WITH option.
The default behavior when you restore data from a snapshot backup image is a full database offline restore of all paths that make up the database, including all containers, the local volume directory, and the database path (DBPATH). The logs are excluded from a snapshot restore unless you specify the LOGTARGET INCLUDE parameter; the LOGTARGET EXCLUDE parameter is the default for all snapshot restores. If you provide a timestamp, the snapshot backup image with that timestamp is used for the restore.
If you have other storage hardware, and a DB2 ACS API driver for that storage hardware, you can use the LIBRARY parameter to specify the DB2 ACS API driver.
The value of the LIBRARY parameter is a fully-qualified library file name.
You cannot use the VENDOROPT database configuration parameter to specify vendor-specific options for snapshot restore operations. You must use the OPTIONS parameter of the restore utilities instead.
One or more paths can be specified, each separated by a comma. Each path must have an absolute path name and it must exist locally. If the database does not already exist on disk and the DBPATH ON parameter is not specified then the first path is used as the target database directory.
For a multi-partition database the ON path-list option can only be specified on the catalog partition. The catalog partition must be restored before any other partitions are restored when the ON option is used. The restore of the catalog-partition with new storage paths will place all non-catalog nodes in a RESTORE_PENDING state. The non-catalog nodes can then be restored in parallel without specifying the ON clause in the restore command.
In general, the same storage paths must be used for each partition in a multi-partition database and they must all exist prior to executing the RESTORE DATABASE command. One exception to this is where database partition expressions are used within the storage path. Doing this allows the database partition number to be reflected in the storage path such that the resulting path name is different on each partition.
You cannot use the ON parameter to re-define storage paths for schema transport. Schema transport will use existing storage paths on the target database.
When you restore a database backup to an existing database, the restored database inherits the alias and database name of the existing database. When you restore a database backup to a nonexistent database, the new database is created with the alias and database name that you specify. This new database name must be unique on the system where you restore it.
The TABLESPACE and SCHEMA options must specify table space names and schema names that define a valid transportable set or the transport operation fails. SQLCODE=SQL2590N rc=1
The system created tablespaces cannot be transported. SQLCODE=SQL2590N rc=4.
After the schemas have been validated by the RESTORE command, the system catalog entries describing the objects in the table spaces being transported are created in the target database. After completion of the schema recreation, the target database takes ownership of the physical table space containers.
The physical and logical objects contained in the table spaces being restored are re-created in the target database and the table space definitions and containers are added to the target database. Failure during the creation of an object, or the replay of the DDL returns an error.
The absolute path name of an existing directory on the database server, to be used as the target directory for extracting log files from a backup image. If this option is specified, any log files contained within the backup image will be extracted into the target directory. If this option is not specified, log files contained within a backup image will not be extracted. To extract only the log files from the backup image, specify the LOGS option.
Snapshot restores:
The restore buffer size must be a positive integer multiple of the backup buffer size specified during the backup operation. If an incorrect buffer size is specified, the buffers are allocated to be of the smallest acceptable size.
If, following a successful restore operation, the database is in rollforward pending state, the ROLLFORWARD command must be invoked before the database can be used again.
If this option is specified when restoring from an online backup image, error SQL2537N will be returned.
If backup image is of a recoverable database then WITHOUT ROLLING FORWARD cannot be specified with REBUILD option.
wsdb.0.db2inst1.NODE0000.CATN0000.20020331234149.001
wsdb.0.db2inst1.NODE0001.CATN0000.20020331234427.001
wsdb.0.db2inst1.NODE0002.CATN0000.20020331234828.001
wsdb.0.db2inst1.NODE0003.CATN0000.20020331235235.001
To
restore the catalog partition first, then all other database partitions
of the WSDB database from the /dev3/backup directory, issue the following
commands from one of the database partitions: db2_all '<<+0< db2 RESTORE DATABASE wsdb FROM /dev3/backup
TAKEN AT 20020331234149
INTO wsdb REPLACE EXISTING'
db2_all '<<+1< db2 RESTORE DATABASE wsdb FROM /dev3/backup
TAKEN AT 20020331234427
INTO wsdb REPLACE EXISTING'
db2_all '<<+2< db2 RESTORE DATABASE wsdb FROM /dev3/backup
TAKEN AT 20020331234828
INTO wsdb REPLACE EXISTING'
db2_all '<<+3< db2 RESTORE DATABASE wsdb FROM /dev3/backup
TAKEN AT 20020331235235
INTO wsdb REPLACE EXISTING'
The db2_all utility
issues the restore command to each specified database partition. When
performing a restore using db2_all, you should always
specify REPLACE EXISTING and/or WITHOUT
PROMPTING. Otherwise, if there is prompting, the operation
will look like it is hanging. This is because db2_all does
not support user prompting. restore db mydb replace existing redirect
After
successful completion of step 1, and before completing step 3, the
restore operation can be aborted by issuing: restore db mydb abort
set tablespace containers for 5 using
(file 'f:\ts3con1' 20000, file 'f:\ts3con2' 20000)
To
verify that the containers of the restored database are the ones specified
in this step, issue the LIST TABLESPACE CONTAINERS command. restore db mydb continue
This
is the final step of the redirected restore operation. (Sun) backup db mydb use tsm
(Mon) backup db mydb online incremental delta use tsm
(Tue) backup db mydb online incremental delta use tsm
(Wed) backup db mydb online incremental use tsm
(Thu) backup db mydb online incremental delta use tsm
(Fri) backup db mydb online incremental delta use tsm
(Sat) backup db mydb online incremental use tsm
For
an automatic database restore of the images created on Friday morning,
issue: restore db mydb incremental automatic taken at (Fri)
For
a manual database restore of the images created on Friday morning,
issue: restore db mydb incremental taken at (Fri)
restore db mydb incremental taken at (Sun)
restore db mydb incremental taken at (Wed)
restore db mydb incremental taken at (Thu)
restore db mydb incremental taken at (Fri)
backup db sample online to /dev3/backup include logs
To
restore that backup image, supply a LOGTARGET path and specify this
path during ROLLFORWARD: restore db sample from /dev3/backup logtarget /dev3/logs
rollforward db sample to end of logs and stop overflow log path (/dev3/logs)
restore db sample logs from /dev3/backup logtarget /dev3/logs
backup db sample to /dev3/backup, /dev3/backup, /dev3/backup
To
restore the backup image from the target directories, issue:restore db sample from /dev3/backup, /dev3/backup, /dev3/backup
restore db sample use TSM options '"-fromnode=bar -fromowner=dmcinnis"'
restore db sample use TSM options @/u/dmcinnis/myoptions.txt
The
file myoptions.txt contains the following information:
-fromnode=bar -fromowner=dmcinnisrestore db sample from /home/jseifert/backups taken at 20050301100417 redirect
generate script SAMPLE_NODE0000.clp
would look like this:
-- ****************************************************************************
-- ** automatically created redirect restore script
-- ****************************************************************************
UPDATE COMMAND OPTIONS USING S ON Z ON SAMPLE_NODE0000.out V ON;
SET CLIENT ATTACH_DBPARTITIONNUM 0;
SET CLIENT CONNECT_DBPARTITIONNUM 0;
-- ****************************************************************************
-- ** initialize redirected restore
-- ****************************************************************************
RESTORE DATABASE SAMPLE
-- USER '<username>'
-- USING '<password>'
FROM '/home/jseifert/backups'
TAKEN AT 20050301100417
-- DBPATH ON '<target-directory>'
INTO SAMPLE
-- NEWLOGPATH '/home/jseifert/jseifert/NODE0000/SQL00001/SQLOGDIR/'
-- WITH <num-buff> BUFFERS
-- BUFFER <buffer-size>
-- REPLACE HISTORY FILE
-- REPLACE EXISTING
REDIRECT
-- PARALLELISM <n>
-- WITHOUT ROLLING FORWARD
-- WITHOUT PROMPTING
;
-- ****************************************************************************
-- ** tablespace definition
-- ****************************************************************************
-- ****************************************************************************
-- ** Tablespace name = SYSCATSPACE
-- ** Tablespace ID = 0
-- ** Tablespace Type = System managed space
-- ** Tablespace Content Type = Any data
-- ** Tablespace Page size (bytes) = 4096
-- ** Tablespace Extent size (pages) = 32
-- ** Using automatic storage = No
-- ** Total number of pages = 5572
-- ****************************************************************************
SET TABLESPACE CONTAINERS FOR 0
-- IGNORE ROLLFORWARD CONTAINER OPERATIONS
USING (
PATH 'SQLT0000.0'
);
-- ****************************************************************************
-- ** Tablespace name = TEMPSPACE1
-- ** Tablespace ID = 1
-- ** Tablespace Type = System managed space
-- ** Tablespace Content Type = System Temporary data
-- ** Tablespace Page size (bytes) = 4096
-- ** Tablespace Extent size (pages) = 32
-- ** Using automatic storage = No
-- ** Total number of pages = 0
-- ****************************************************************************
SET TABLESPACE CONTAINERS FOR 1
-- IGNORE ROLLFORWARD CONTAINER OPERATIONS
USING (
PATH 'SQLT0001.0'
);
-- ****************************************************************************
-- ** Tablespace name = USERSPACE1
-- ** Tablespace ID = 2
-- ** Tablespace Type = System managed space
-- ** Tablespace Content Type = Any data
-- ** Tablespace Page size (bytes) = 4096
-- ** Tablespace Extent size (pages) = 32
-- ** Using automatic storage = No
-- ** Total number of pages = 1
-- ****************************************************************************
SET TABLESPACE CONTAINERS FOR 2
-- IGNORE ROLLFORWARD CONTAINER OPERATIONS
USING (
PATH 'SQLT0002.0'
);
-- ****************************************************************************
-- ** Tablespace name = DMS
-- ** Tablespace ID = 3
-- ** Tablespace Type = Database managed space
-- ** Tablespace Content Type = Any data
-- ** Tablespace Page size (bytes) = 4096
-- ** Tablespace Extent size (pages) = 32
-- ** Using automatic storage = No
-- ** Auto-resize enabled = No
-- ** Total number of pages = 2000
-- ** Number of usable pages = 1960
-- ** High water mark (pages) = 96
-- ****************************************************************************
SET TABLESPACE CONTAINERS FOR 3
-- IGNORE ROLLFORWARD CONTAINER OPERATIONS
USING (
FILE /tmp/dms1 1000
, FILE /tmp/dms2 1000
);
-- ****************************************************************************
-- ** Tablespace name = RAW
-- ** Tablespace ID = 4
-- ** Tablespace Type = Database managed space
-- ** Tablespace Content Type = Any data
-- ** Tablespace Page size (bytes) = 4096
-- ** Tablespace Extent size (pages) = 32
-- ** Using automatic storage = No
-- ** Auto-resize enabled = No
-- ** Total number of pages = 2000
-- ** Number of usable pages = 1960
-- ** High water mark (pages) = 96
-- ****************************************************************************
SET TABLESPACE CONTAINERS FOR 4
-- IGNORE ROLLFORWARD CONTAINER OPERATIONS
USING (
DEVICE '/dev/hdb1' 1000
, DEVICE '/dev/hdb2' 1000
);
-- ****************************************************************************
-- ** start redirect restore
-- ****************************************************************************
RESTORE DATABASE SAMPLE CONTINUE;
-- ****************************************************************************
-- ** end of file
-- ****************************************************************************
restore db test from /home/jseifert/backups taken at 20050304090733 redirect
generate script TEST_NODE0000.clp
would look like this:
-- ****************************************************************************
-- ** automatically created redirect restore script
-- ****************************************************************************
UPDATE COMMAND OPTIONS USING S ON Z ON TEST_NODE0000.out V ON;
SET CLIENT ATTACH_DBPARTITIONNUM 0;
SET CLIENT CONNECT_DBPARTITIONNUM 0;
-- ****************************************************************************
-- ** initialize redirected restore
-- ****************************************************************************
RESTORE DATABASE TEST
-- USER '<username>'
-- USING '<password>'
FROM '/home/jseifert/backups'
TAKEN AT 20050304090733
ON '/home/jseifert'
-- DBPATH ON <target-directory>
INTO TEST
-- NEWLOGPATH '/home/jseifert/jseifert/NODE0000/SQL00002/SQLOGDIR/'
-- WITH <num-buff> BUFFERS
-- BUFFER <buffer-size>
-- REPLACE HISTORY FILE
-- REPLACE EXISTING
REDIRECT
-- PARALLELISM <n>
-- WITHOUT ROLLING FORWARD
-- WITHOUT PROMPTING
;
-- ****************************************************************************
-- ** tablespace definition
-- ****************************************************************************
-- ****************************************************************************
-- ** Tablespace name = SYSCATSPACE
-- ** Tablespace ID = 0
-- ** Tablespace Type = Database managed space
-- ** Tablespace Content Type = Any data
-- ** Tablespace Page size (bytes) = 4096
-- ** Tablespace Extent size (pages) = 4
-- ** Using automatic storage = Yes
-- ** Auto-resize enabled = Yes
-- ** Total number of pages = 6144
-- ** Number of usable pages = 6140
-- ** High water mark (pages) = 5968
-- ****************************************************************************
-- ****************************************************************************
-- ** Tablespace name = TEMPSPACE1
-- ** Tablespace ID = 1
-- ** Tablespace Type = System managed space
-- ** Tablespace Content Type = System Temporary data
-- ** Tablespace Page size (bytes) = 4096
-- ** Tablespace Extent size (pages) = 32
-- ** Using automatic storage = Yes
-- ** Total number of pages = 0
-- ****************************************************************************
-- ****************************************************************************
-- ** Tablespace name = USERSPACE1
-- ** Tablespace ID = 2
-- ** Tablespace Type = Database managed space
-- ** Tablespace Content Type = Any data
-- ** Tablespace Page size (bytes) = 4096
-- ** Tablespace Extent size (pages) = 32
-- ** Using automatic storage = Yes
-- ** Auto-resize enabled = Yes
-- ** Total number of pages = 256
-- ** Number of usable pages = 224
-- ** High water mark (pages) = 96
-- ****************************************************************************
-- ****************************************************************************
-- ** Tablespace name = DMS
-- ** Tablespace ID = 3
-- ** Tablespace Type = Database managed space
-- ** Tablespace Content Type = Any data
-- ** Tablespace Page size (bytes) = 4096
-- ** Tablespace Extent size (pages) = 32
-- ** Using automatic storage = No
-- ** Auto-resize enabled = No
-- ** Total number of pages = 2000
-- ** Number of usable pages = 1960
-- ** High water mark (pages) = 96
-- ****************************************************************************
SET TABLESPACE CONTAINERS FOR 3
-- IGNORE ROLLFORWARD CONTAINER OPERATIONS
USING (
FILE '/tmp/dms1' 1000
, FILE '/tmp/dms2' 1000
);
-- ****************************************************************************
-- ** Tablespace name = RAW
-- ** Tablespace ID = 4
-- ** Tablespace Type = Database managed space
-- ** Tablespace Content Type = Any data
-- ** Tablespace Page size (bytes) = 4096
-- ** Tablespace Extent size (pages) = 32
-- ** Using automatic storage = No
-- ** Auto-resize enabled = No
-- ** Total number of pages = 2000
-- ** Number of usable pages = 1960
-- ** High water mark (pages) = 96
-- ****************************************************************************
SET TABLESPACE CONTAINERS FOR 4
-- IGNORE ROLLFORWARD CONTAINER OPERATIONS
USING (
DEVICE '/dev/hdb1' 1000
, DEVICE '/dev/hdb2' 1000
);
-- ****************************************************************************
-- ** start redirect restore
-- ****************************************************************************
RESTORE DATABASE TEST CONTINUE;
-- ****************************************************************************
-- ** end of file
-- ****************************************************************************
db2 restore db sample use snapshot LOGTARGET INCLUDE without prompting
db2 restore db sample use snapshot LOGTARGET EXCLUDE without prompting
db2 restore db sample use snapshot without prompting
db2 restore db sample use snapshot LOGTARGET EXCLUDE FORCE without prompting
db2 restore db sample use snapshot LOGTARGET INCLUDE FORCE without prompting
If the parameter AT DBPARTITIONNUM is used to recreate a database partition that was dropped (because it was damaged), the database at this database partition will be in the restore-pending state. After recreating the database partition, the database must immediately be restored on this database partition.
Snapshot restore
Like a traditional (non-snapshot) restore, the default behavior when restoring a snapshot backup image will be to NOT restore the log directories -LOGTARGET EXCLUDE.
If the DB2 database manager detects that any log directory's group ID is shared among any of the other paths to be restored, then an error is returned. In this case, LOGTARGET INCLUDE or LOGTARGET INCLUDE FORCE must be specified, as the log directories must be part of the restore.
The DB2 database manager will make all efforts to save existing log directories (primary, mirror and overflow) before the restore of the paths from the backup image takes place.
If you wish the log directories to be restored and the DB2 database manager detects that the pre-existing log directories on disk conflict with the log directories in the backup image, then the DB2 database manager will report an error. In such a case, if you have specified LOGTARGET INCLUDE FORCE, then this error will be suppressed and the log directories from the image will be restored, deleting whatever existed beforehand.
There is a special case in which the LOGTARGET EXCLUDE option is specified and a log directory path resides under the database directory (for example, /NODExxxx/SQLxxxxx/SQLOGDIR/). In this case, a restore would still overwrite the log directory as the database path, and all of the contents beneath it, would be restored. If the DB2 database manager detects this scenario and log files exist in this log directory, then an error will be reported. If you specify LOGTARGET EXCLUDE FORCE, then this error will be suppressed and those log directories from the backup image will overwrite the conflicting log directories on disk.
Transporting table spaces and schemas
The complete list of table spaces and schemas must be specified.
The target database must be active at the time of transport.
If an online backup image is used, then the staging database is rolled forward to the end of the backup. If an offline backup image is used, then no rollforward processing is performed.
A staging database consisting of the system catalog table space from the backup image is created under the path specified by the dftdbpath database parameter. This database is dropped when the RESTORE DATABASE command completes. The staging database is required to extract the DDL used to regenerate the objects in the table spaces being transported.
When transporting table spaces, the DB2 database manager attempts to assign the first available buffer pool of matching page size to the table space that is transported. If the target database does not have buffer pools that are of matching page size of table spaces transported, then a hidden buffer pool might be assigned. Hidden buffer pools are temporary place holders for transported table spaces. You can check buffer pools assigned to transported table spaces after transport completes. You can issue the ALTER TABLESPACE command to update buffer pools.
If database rollforward detects a table space schema transport log record, the corresponding transported table space will be taken offline and moved into drop pending state. This is because database does not have complete logs of transported table spaces to rebuild transported table spaces and their contents. You can take a full backup of the target database after transport completes, so subsequent rollforward does not pass the point of schema transport in the log stream.