DB2 10.5 for Linux, UNIX, and Windows

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. The Restore Database command can also be used to encrypt an already existing database.

This utility can also perform the following services:
  • Overwrite a database with a different image or restore the backup copy to a new database.
  • The restore utility in DB2® Version 9.8 cannot be used to restore backup images that are backed up on any other DB2 software version.
  • Restore backup images in DB2 Version 10.5 that were created in DB2 Versions 9.7, 9.8, or 10.1.
    • If a database upgrade is required, it will be invoked automatically at the end of the restore operation.
  • If, at the time of the backup operation, the database was enabled for rollforward recovery, the database can be brought to its previous state by invoking the rollforward utility after successful completion of a restore operation.
  • Restore a table space level backup.
  • Transport a set of table spaces, storage groups and SQL schemas from database backup image to a database using the TRANSPORT option (in DB2 Version 9.7 Fix Pack 2 and later fix packs). The TRANSPORT option is not supported in the DB2 pureScale® environment, or in partitioned database environments.
  • If the database name exists when this command is issued, it will replace and redefine all storage groups as they were at the time the backup image was produced, unless otherwise redirected by the user.

For information about 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 re-created; 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.

Scope

This command only affects the node on which it is executed.

You cannot restore SYSCATSPACE online.

Authorization

To restore to an existing database, one of the following authorities:
  • SYSADM
  • SYSCTRL
  • SYSMAINT
To restore to a new database, one of the following authorities:
  • SYSADM
  • SYSCTRL
If a username is specified, this user requires CONNECT authority on the database.

Required connection

The required connection will vary based on the type of restore action:

  • You require a database connection, to restore to an existing database. This command automatically establishes an exclusive connection to the specified database.
  • You require an instance and a database connection, to restore to a new database. The instance attachment is required to create the database.

    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.

  • For snapshot restore, instance and database connections are required.

Command syntax

Read syntax diagramSkip visual syntax diagram
>>-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-'  '-USING STOGROUP--storagegroup-name-'     

>--+-----------------------------+------------------------------>
   '-INTO--target-database-alias-'   

>--+---------------------------------------+-------------------->
   '-LOGTARGET--+-directory--------------+-'   
                +-DEFAULT----------------+     
                '-+-EXCLUDE-+--+-------+-'     
                  '-INCLUDE-'  '-FORCE-'       

>--+---------------------------+-------------------------------->
   '-NEWLOGPATH--+-directory-+-'   
                 '-DEFAULT---'     

>--+----------------------------+--+---------------------+------>
   '-WITH--num-buffers--BUFFERS-'  '-BUFFER--buffer-size-'   

>--+----------------------+--+------------------+--------------->
   '-REPLACE HISTORY FILE-'  '-REPLACE EXISTING-'   

>--+---------------------------------------+-------------------->
   '-REDIRECT--+-------------------------+-'   
               '-GENERATE SCRIPT--script-'     

>--+----------------+--+----------------+----------------------->
   '-PARALLELISM--n-'  +-COMPRLIB--name-+   
                       '-ENCRLIB--name--'   

>--+-------------------+---------------------------------------->
   +-COMPROPTS--string-+   
   '-ENCROPTS--string--'   

>--+-----------------------------------------------------------------+-->
   +-NO ENCRYPT------------------------------------------------------+   
   '-ENCRYPT--+------------------------+--+------------------------+-'   
              '-| Encryption Options |-'  '-| Master Key Options |-'     

>--+-------------------------+--+-------------------+-----------|
   '-WITHOUT ROLLING FORWARD-'  '-WITHOUT PROMPTING-'   

rebuild-tablespace-clause

                  .-,---------------.      
                  V                 |      
|--TABLESPACE--(----tablespace-name-+--)------------------------|

open-sessions

|--+------------------------------+-----------------------------|
   '-OPEN--num-sessions--SESSIONS-'   

options

|--+-------------------------------+----------------------------|
   '-OPTIONS--+-"options-string"-+-'   
              '-@--file-name-----'     

Encryption Options

                     .-MODE--CBC-.                           
|--CIPHER--+-AES--+--+-----------+--KEY LENGTH--key-length------|
           '-3DES-'                                          

Master Key Options

|--MASTER KEY LABEL--label-name---------------------------------|

Command parameters

DATABASE source-database-alias
Alias of the source database from which the backup was taken.
CONTINUE
Specifies that the containers have been redefined, and that the final step in a redirected restore operation should be performed.
ABORT
This parameter:
  • Stops a redirected restore operation. This is useful when an error has occurred that requires one or more steps to be repeated. After RESTORE DATABASE with the ABORT option has been issued, each step of a redirected restore operation must be repeated, including RESTORE DATABASE with the REDIRECT option.
  • Terminates an incremental restore operation before completion.
USER username
Specifies the user name to be used when attempting a connection to the database.
USING password
The password used to authenticate the user name. If the password is omitted, the user is prompted to enter it.
REBUILD WITH ALL TABLESPACES IN DATABASE
Restores the database with all the table spaces known to the database at the time of the image being restored. This restore overwrites a database if it already exists.
REBUILD WITH ALL TABLESPACES IN DATABASE EXCEPT rebuild-tablespace-clause
Restores the database with all the table spaces known to the database at the time of the image being restored except for those specified in the list. This restore overwrites a database if it already exists.
REBUILD WITH ALL TABLESPACES IN IMAGE
Restores the database with only the table spaces in the image being restored. This restore overwrites a database if it already exists.
REBUILD WITH ALL TABLESPACES IN IMAGE EXCEPT rebuild-tablespace-clause
Restores the database with only the table spaces in the image being restored except for those specified in the list. This restore overwrites a database if it already exists.
REBUILD WITH rebuild-tablespace-clause
Restores the database with only the list of table spaces specified. This restore overwrites a database if it already exists.
TABLESPACE tablespace-name
A list of names used to specify the table spaces that are to be restored.

Table space names are required when the TRANSPORT option is specified. This option could take as much time as a full restore operation.

SCHEMA schema-name
A list of names used to specify the schemas that are to be restored.

Schema names are required if the TRANSPORT option is specified. The SCHEMA option is only valid when the TRANSPORT option is specified.

ONLINE
This keyword, applicable only when performing a table space-level restore operation, is specified to allow a backup image to be restored online. This means that other agents can connect to the database while the backup image is being restored, and that the data in other table spaces will be available while the specified table spaces are being restored.
HISTORY FILE
This keyword is specified to restore only the history file from the backup image.
COMPRESSION LIBRARY
This keyword is specified to restore only the compression library from the backup image. If the object exists in the backup image, it will be restored into the database directory. If the object does not exist in the backup image, the restore operation will fail.
LOGS
This keyword is specified to restore only the set of log files contained in the backup image. If the backup image does not contain any log files, the restore operation will fail. If this option is specified, the LOGTARGET option must also be specified. This option could take as much time as a full restore operation.
INCREMENTAL
Without additional parameters, INCREMENTAL specifies a manual cumulative restore operation. During manual restore the user must issue each restore command manually for each image involved in the restore. Do so according to the following order: last, first, second, third and so on up to and including the last image.
INCREMENTAL AUTOMATIC/AUTO
Specifies an automatic cumulative restore operation.
INCREMENTAL ABORT
Specifies abortion of an in-progress manual cumulative restore operation.
USE
TSM
Specifies that the database is to be restored by using Tivoli® Storage Manager (TSM) as the target device.
XBSA
Specifies that the XBSA interface is to be used. Backup Services APIs (XBSA) are an open application programming interface for applications or facilities needing data storage management for backup or archiving purposes.
SNAPSHOT
Specifies that the data is to be restored from a snapshot backup.
You cannot use the SNAPSHOT parameter with any of the following parameters:
  • TABLESPACE
  • INCREMENTAL
  • TO
  • ON
  • DBPATH ON
  • INTO
  • NEWLOGPATH
  • WITH num-buffers BUFFERS
  • BUFFER
  • REDIRECT
  • REPLACE HISTORY FILE
  • COMPRESSION LIBRARY
  • PARALLELISM
  • COMPRLIB
  • OPEN num-sessions SESSIONS
  • HISTORY FILE
  • LOGS

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.

LIBRARY library-name
Integrated into IBM® Data Server is a DB2 ACS API driver for the following storage hardware:
  • IBM TotalStorage SAN Volume Controller
  • IBM Enterprise Storage Server® Model 800
  • IBM Storwize® V7000
  • IBM System Storage® DS6000™
  • IBM System Storage DS8000®
  • IBM System Storage N Series
  • IBM XIV®

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.

SCRIPT script-name
The name of the executable script capable of performing a snapshot restore operation. The script name must be a fully qualified file name.
OPTIONS
"options-string"
Specifies options to be used for the restore operation. The string will be passed exactly as it was entered, without the double quotation marks.
@file-name
Specifies that the options to be used for the restore operation are contained in a file located on the DB2 server. The string will be passed to the vendor support library. The file must be a fully qualified 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.

OPEN num-sessions SESSIONS
Specifies the number of I/O sessions that are to be used with TSM or the vendor product.
FROM directory/device
The fully qualified path name of the directory or device on which the backup image resides. If USE TSM, FROM, and LOAD are omitted, the default value is the current working directory of the client machine. This target directory or device must exist on the target server/instance.
If several items are specified, and the last item is a tape device, the user is prompted for another tape. Valid response options are:
c
Continue. Continue using the device that generated the warning message (for example, continue when a new tape has been mounted).
d
Device terminate. Stop using only the device that generated the warning message (for example, terminate when there are no more tapes).
t
Terminate. Abort the restore operation after the user has failed to perform some action requested by the utility.
LOAD shared-library
The name of the shared library (DLL on Windows operating systems) containing the vendor backup and restore I/O functions to be used. The name can contain a full path. If the full path is not given, the value defaults to the path on which the user exit program resides.
TAKEN AT date-time
The time stamp of the database backup image. The time stamp is displayed after successful completion of a backup operation, and is part of the path name for the backup image. It is specified in the form yyyymmddhhmmss. A partial time stamp can also be specified. For example, if two different backup images with time stamps 20021001010101 and 20021002010101 exist, specifying 20021002 causes the image with time stamp 20021002010101 to be used. If a value for this parameter is not specified, there must be only one backup image on the source media.
TO target-directory
This parameter states the target database directory. This parameter is ignored if the utility is restoring to an existing database. The drive and directory that you specify must be local. If the backup image contains a database that is enabled for automatic storage then only the database directory changes, the storage paths associated with the database do not change.
DBPATH ON target-directory
This parameter states the target database directory. This parameter is ignored if the utility is restoring to an existing database. The drive and directory that you specify must be local. If the backup image contains a database that is enabled for automatic storage and the ON parameter is not specified then this parameter is synonymous with the TO parameter and only the database directory changes, the storage paths associated with the database do not change.
ON path-list

This parameter redefines the storage paths associated with a database. If the database contains multiple storage groups this option will redirect all storage groups to the specified paths, such that every defined storage group will use path-list as its new storage group paths. Using this parameter with a database that has no storage groups defined or is not enabled for automatic storage results in an error (SQL20321N). The existing storage paths as defined within the backup image are no longer used and automatic storage table spaces are automatically redirected to the new paths. If this parameter is not specified for an automatic storage database then the storage paths remain as they are defined within the backup image. Without this parameter, while the path might not change, it is possible for the data and containers on the paths to be rebalanced during the restore. For rebalancing conditions, see Rebalancing during RESTORE of automatic storage database.

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 this option is specified with the REDIRECT option, then this option takes effect before the initial RESTORE ... REDIRECT command returns to the caller, and before any SET STOGROUP PATHS or SET TABLESPACE CONTAINERS statements are issued. If any storage group paths are subsequently redirected, those modifications override any paths specified in the initial RESTORE ... ON path-list command.

Any storage groups that have their paths redefined during a restore operation do not have any storage path related operations replayed during a subsequent roll forward operation.

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 database partitions in a RESTORE_PENDING state. The non-catalog database partitions 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 before 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.

Using the RESTORE command with the ON clause has the same implications as a redirected restore operation.

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.

INTO target-database-alias
The target database alias. If the target database does not exist, it is created.

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.

TRANSPORT INTO target-database-alias
Specifies the existing target database alias for a transport operation. The table spaces and schemas being transported are added to the database.

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 catalogs 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.

STAGE IN staging-database
Specifies the name of a temporary staging database for the backup image that is the source for the transport operation. If the STAGE IN option is specified, the temporary database is not dropped after the transport operation completes. The database is no longer required after the transport has completed and can be dropped by the DBA.
The following is true if the STAGE IN option is not specified:
  • The database name is of the form SYSTGxxx where xxx is an integer value.
  • The temporary staging database is dropped after the transport operation completes.
USING STOGROUP storagegroup-name
For automatic storage table spaces, this specifies the target storage group that will be associated with all table spaces being transported. If the storage group is not specified, then the currently designated default storage group of the target database is used. This clause only applies to automatic storage table spaces and is only valid during a schema transport operation.

Identifies the storage group in which table space data will be stored. storagegroup-name must identify a storage group that exists at the target-database-alias of the TRANSPORT operation. (SQLSTATE 42704). This is a one-part name.

LOGTARGET directory
Non-snapshot restores:

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. This option automatically appends the database partition number and a log stream ID to the path.

DEFAULT
Restore log files from the backup image into the database's default log directory, for example /home/db2user/db2inst/NODE0000/SQL00001/LOGSTREAM0000.

Snapshot restores:

INCLUDE
Restore log directory volumes from the snapshot image. If this option is specified and the backup image contains log directories, then they will be restored. Existing log directories and log files on disk will be left intact if they do not conflict with the log directories in the backup image. If existing log directories on disk conflict with the log directories in the backup image, then an error will be returned.
EXCLUDE
Do not restore log directory volumes. If this option is specified, then no log directories will be restored from the backup image. Existing log directories and log files on disk will be left intact if they do not conflict with the log directories in the backup image. If a path belonging to the database is restored and a log directory will implicitly be restored because of this, thus causing a log directory to be overwritten, an error will be returned.
FORCE
Allow existing log directories in the current database to be overwritten and replaced when restoring the snapshot image. Without this option, existing log directories and log files on disk which conflict with log directories in the snapshot image will cause the restore to fail. Use this option to indicate that the restore can overwrite and replace those existing log directories.
Note: Use this option with caution, and always ensure that you have backed up and archived all logs that might be required for recovery.
For snapshot restores, the default value of the directory option is LOGTARGET EXCLUDE.
NEWLOGPATH directory
The absolute pathname of a directory that will be used for active log files after the restore operation. This parameter has the same function as the newlogpath database configuration parameter. The parameter can be used when the log path in the backup image is not suitable for use after the restore operation; for example, when the path is no longer valid, or is being used by a different database.
Note: When the newlogpath command parameter is set, the node number is automatically appended to the value of logpath parameter. The node number is also automatically appended to the value of the logpath parameter when the newlogpath database configuration parameter is updated. For more information, see newlogpath - Change the database log path
DEFAULT
After the restore completes, the database should use the default log directory: /home/db2user/db2inst/NODE0000/SQL00001/LOGSTREAM0000 for logging.
WITH num-buffers BUFFERS
The number of buffers to be used. The DB2 database system will automatically choose an optimal value for this parameter unless you explicitly enter a value. A larger number of buffers can be used to improve performance when multiple sources are being read from, or if the value of PARALLELISM has been increased.
BUFFER buffer-size
The size, in pages, of the buffer used for the restore operation. The DB2 database system will automatically choose an optimal value for this parameter unless you explicitly enter a value. The minimum value for this parameter is 8 pages.

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.

REPLACE HISTORY FILE
Specifies that the restore operation should replace the history file on disk with the history file from the backup image.
REPLACE EXISTING
If a database with the same alias as the target database alias already exists, this parameter specifies that the restore utility is to replace the existing database with the restored database. This is useful for scripts that invoke the restore utility, because the command line processor will not prompt the user to verify deletion of an existing database. If the WITHOUT PROMPTING parameter is specified, it is not necessary to specify REPLACE EXISTING, but in this case, the operation will fail if events occur that normally require user intervention.
REDIRECT
Specifies a redirected restore operation. To complete a redirected restore operation, this command should be followed by one or more SET TABLESPACE CONTAINERS commands or SET STOGROUP PATHS commands, and then by a RESTORE DATABASE command with the CONTINUE option. For example:
RESTORE DB SAMPLE REDIRECT

SET STOGROUP PATHS FOR sg_hot ON '/ssd/fs1', '/ssd/fs2'
SET STOGROUP PATHS FOR sg_cold ON '/hdd/path1', '/hdd/path2'

RESTORE DB SAMPLE CONTINUE

If a storage group has been renamed since the backup image was produced, the storage group name specified on the SET STOGROUP PATHS command refers to the storage group name from the backup image, not the most recent name.

All commands associated with a single redirected restore operation must be invoked from the same window or CLP session.

GENERATE SCRIPT script
Creates a redirect restore script with the specified file name. The script name can be relative or absolute and the script will be generated on the client side. If the file cannot be created on the client side, an error message (SQL9304N) will be returned. If the file already exists, it will be overwritten. Please see the following examples for further usage information.
WITHOUT ROLLING FORWARD
Specifies that the database is not to be put in rollforward pending state after it has been successfully restored.

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.

PARALLELISM n
Specifies the number of buffer manipulators that are to be created during the restore operation. The DB2 database system will automatically choose an optimal value for this parameter unless you explicitly enter a value.
COMPRLIB | ENCRLIB name
Indicates the name of the library that is used to decompress or decrypt a backup image. The path to the following libraries is $HOME/sqllib/lib.
  • Encryption libraries: libdb2encr.so (for Linux or UNIX based operating systems); libdb2encr.so.a (for AIX®); and db2encr.dll (for Windows operating systems).
  • Compression library: db2compr.dll (for Windows operating systems)
  • Encryption and compression libraries: libdb2compr_encr.so (for Linux or UNIX based operating systems); libdb2compr_encr.a (for AIX); and db2compr_encr.dll (for Windows operating systems).
The name must be a fully qualified path that refers to a file on the server. If this parameter is not specified, the DB2 database system attempts to use the library that is stored in the image. If the backup image is not compressed or encrypted, the value of this parameter is ignored. If the specified library cannot be loaded, the restore operation fails.
COMPROPTS | ENCROPTS string
Describes a block of binary data that is passed to the initialization routine in the decompression or decryption library. The DB2 database system passes this string directly from the client to the server. Any byte reversal or code page conversion issues are handled by the library. If the first character of the data block is "@", the remainder of the data is interpreted by the DB2 database system as the name of a file that is found on the server. The DB2 database system then replaces the contents of string with the contents of this file and passes the new value to the initialization routine instead. The maximum length for the string is 1024 bytes.
For the default DB2 libraries libdb2compr_encr.so (compression and encryption) or libdb2encr.so (encryption only), the format of the ENCROPTS string is as follows:
Master Key Label=label-name
The master key label is optional. If no master key label is specified, the database manager looks in the keystore for a master key label that was used to create the backup image. If you are using other libraries, the format of the ENCROPTS string depends on those libraries.
NO ENCRYPT
Specifies that an encrypted database is to be restored into a non-encrypted new or existing database. This option does not work on table space restore unless schema transport is specified with table space restore and the target database is not encrypted.
ENCRYPT
Specifies that the restored database is to be encrypted. Encryption includes all system, user, and temporary table spaces, indexes, and all transaction log data. All data types within those table spaces are encrypted, including long field data, LOBs, and XML data. You cannot specify this option when restoring into an existing database; for table space-level restore operations; when the TRANSPORT option is specified; or when the USE SNAPSHOT option is specified.
CIPHER
Specifies the encryption algorithm that is to be used for encrypting the database. You can choose one of the following FIPS 140-2 approved options:
AES
Advanced Encryption Standard (AES) algorithm. This is the default.
3DES
Triple Data Encryption Standard (3DES) algorithm
MODE CBC
Specifies the encryption algorithm mode that is to be used for encrypting the database. CBC (Cipher Block Chaining) is the default mode.
KEY LENGTH key-length
Specifies the length of the key that is to be used for encrypting the database. The length can be one of the following values, specified in bits:
128
Available with AES only
168
Available with 3DES only
192
Available with AES only
256
Available with AES only
MASTER KEY LABEL
Specifies a label for the master key that is used to protect the key that is used to encrypt the database. The encryption algorithm that is used for encrypting with the master key is always AES. If the master key is automatically generated by the DB2 data server, it is always a 256-bit key.
label-name
Uniquely identifies the master key within the keystore that is identified by the value of the keystore_type database manager configuration parameter. The maximum length of label-name is 255 bytes.
WITHOUT PROMPTING
Specifies that the restore operation is to run unattended. Actions that normally require user intervention will return an error message. When using a removable media device, such as tape or diskette, the user is prompted when the device ends, even if this option is specified.

Examples

  1. In the following example, the database WSDB is defined on all 4 database partitions, numbered 0 through 3. The path /dev3/backup is accessible from all database partitions. The following offline backup images are available from /dev3/backup:
        wsdb.0.db2inst1.DBPART000.200802241234.001
        wsdb.0.db2inst1.DBPART001.200802241234.001
        wsdb.0.db2inst1.DBPART002.200802241234.001
        wsdb.0.db2inst1.DBPART003.200802241234.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 200802241234 
          INTO wsdb REPLACE EXISTING'
        db2_all '<<+1< db2 RESTORE DATABASE wsdb FROM /dev3/backup 
        TAKEN AT 200802241234 
          INTO wsdb REPLACE EXISTING'
        db2_all '<<+2< db2 RESTORE DATABASE wsdb FROM /dev3/backup 
        TAKEN AT 200802241234 
          INTO wsdb REPLACE EXISTING'
        db2_all '<<+3< db2 RESTORE DATABASE wsdb FROM /dev3/backup 
        TAKEN AT 200802241234 
          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.
  2. Following is a typical redirected restore scenario for a database whose alias is MYDB:
    1. Issue a RESTORE DATABASE command with the REDIRECT option.
         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
    2. Issue a SET TABLESPACE CONTAINERS command for each table space whose containers must be redefined. For example:
         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.
    3. After successful completion of steps 1 and 2, issue:
         restore db mydb continue
      This is the final step of the redirected restore operation.
    4. If step 3 fails, or if the restore operation has been aborted, the redirected restore can be restarted, beginning at step 1.
  3. following example is a sample weekly incremental backup strategy for a recoverable database. It includes a weekly full database backup operation, a daily non-cumulative (delta) backup operation, and a mid-week cumulative (incremental) backup 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)
  4. To produce a backup image, which includes logs, for transportation to a remote site:
       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
  5. To retrieve only the log files from a backup image that includes logs:
       restore db sample logs from /dev3/backup logtarget /dev3/logs
  6. In the following example, three identical target directories are specified for a backup operation on database SAMPLE. The data will be concurrently backed up to the three target directories, and three backup images will be generated with extensions .001, .002, and .003.
    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
  7. The USE TSM OPTIONS keywords can be used to specify the TSM information to use for the restore operation. On Windows platforms, omit the -fromowner option.
    • Specifying a delimited string:
      restore db sample use TSM options '"-fromnode=bar -fromowner=dmcinnis"'
    • Specifying a fully qualified file:
      restore db sample use TSM options @/u/dmcinnis/myoptions.txt
      The file myoptions.txt contains the following information: -fromnode=bar -fromowner=dmcinnis
  8. The following is a simple restore of a multi-partition automatic-storage-enabled database with new storage paths. The database was originally created with one storage path, /myPath0:
    • On the catalog partition issue: restore db mydb on /myPath1,/myPath2
    • On all non-catalog partitions issue: restore db mydb
  9. A script output of the following command on a non-auto storage database:
    restore 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/SAMPLE/NODE0000/LOGSTREAM0000/'
    -- 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
    -- ****************************************************************************
  10. A script output of the following command on an automatic storage database:
    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_MEMBER  0;
    SET CLIENT CONNECT_MEMBER 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/TEST/NODE0000/LOGSTREAM0000/'
    -- WITH <num-buff> BUFFERS
    -- BUFFER <buffer-size>
    -- REPLACE HISTORY FILE
    -- REPLACE EXISTING
    REDIRECT
    -- PARALLELISM <n>
    -- WITHOUT ROLLING FORWARD
    -- WITHOUT PROMPTING
    ;
    -- *****************************************************************************
    -- ** storage group definition
    -- **   Default storage group ID                 = 0
    -- **   Number of storage groups                 = 3
    -- *****************************************************************************
    -- *****************************************************************************
    -- ** Storage group name                         = SG_DEFAULT
    -- **   Storage group ID                         = 0
    -- **   Data tag                                 = None
    -- *****************************************************************************
    -- SET STOGROUP PATHS FOR SG_DEFAULT
    -- ON '/hdd/path1'
    -- ,  '/hdd/path2'
    -- ;
    -- *****************************************************************************
    -- ** Storage group name                         = SG_HOT
    -- **   Storage group ID                         = 1
    -- **   Data tag                                 = 1
    -- *****************************************************************************
    -- SET STOGROUP PATHS FOR SG_HOT
    -- ON '/ssd/fs1'
    -- ,  '/ssd/fs2'
    -- ;
    -- *****************************************************************************
    -- ** Storage group name                         = SG_COLD
    -- **   Storage group ID                         = 2
    -- **   Data tag                                 = 9
    -- *****************************************************************************
    -- SET STOGROUP PATHS FOR SG_COLD
    -- ON '/hdd/slowpath1'
    -- ;
    -- ****************************************************************************
    -- ** 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
    -- **   Storage group ID                         = 0
    -- **   Source storage group ID                  = -1
    -- **   Data tag                                 = None
    -- **   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
    -- **   Storage group ID                         = 1
    -- **   Source storage group ID                  = -1
    -- **   Data tag                                 = 1
    -- **   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
    -- **   Storage group ID                         = 2
    -- **   Source storage group ID                  = -1
    -- **   Data tag                                 = 9
    -- **   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
    -- ****************************************************************************
  11. The following are examples of the RESTORE DB command using the SNAPSHOT option:
    Restore log directory volumes from the snapshot image and do not prompt.
    db2 restore db sample use snapshot LOGTARGET INCLUDE without prompting
    Do not restore log directory volumes and do not prompt.
    db2 restore db sample use snapshot LOGTARGET EXCLUDE without prompting
    Do not restore log directory volumes and do not prompt. When LOGTARGET is not specified, then the default is LOGTARGET EXCLUDE.
    db2 restore db sample use snapshot without prompting
    Allow existing log directories in the current database to be overwritten and replaced when restoring the snapshot image containing conflicting log directories, without prompting.
    db2 restore db sample use snapshot LOGTARGET EXCLUDE FORCE without prompting
    Allow existing log directories in the current database to be overwritten and replaced when restoring the snapshot image containing conflicting log directories, without prompting.
    db2 restore db sample use snapshot LOGTARGET INCLUDE FORCE without prompting
  12. The following are examples of a transport operation using the RESTORE command with the TRANSPORT REDIRECT option:
    Given a source database (TT_SRC) backup image, with storage paths on /src , and a target database (TT_TGT) with storage paths on /tgt :
    > RESTORE DB TT_SRC TABLESPACE (AS1) SCHEMA (KRODGER) TRANSPORT INTO TT_TGT REDIRECT
    
    SQL1277W A redirected restore operation is being performed.  Table space
    configuration can now be viewed and table spaces that do not use automatic
    storage can have their containers reconfigured.
    DB20000I The RESTORE DATABASE command completed successfully.
    Table space 'AS1' is transported into a container path, similar to: /tgt/krodger/NODE0000/TT_TGT/T0000003/C0000000.LRG

    To specify a target storage group for the transported table spaces, the USING STOGROUP option of the RESTORE command can be used. In the following example both table spaces TS1 and TS2 will be restored into the SG_COLD storage group:

    RESTORE DB TT_SRC TABLESPACE (TS1, TS2) SCHEMA (KRODGER) TRANSPORT INTO TT_TGT USING STOGROUP SG_COLD
    Note: The USING STOGROUP option of the RESTORE command is only valid during a transport operation, and can not be used to specify a target storage group during any other restore operation.
    To perform a transport into the default storage group of the target database, the USING STOGROUP option does not need to be specified:
    RESTORE  DB TT_SRC TABLESPACE (TS3) SCHEMA (KRODGER) TRANSPORT INTO TT_TGT
    The storage group name specified on the RESTORE command during the TRANSPORT operation must currently be defined in the target database. It does not need to be defined within the backup image or source database.
  13. The following examples show how to specify encryption options.
    Restore into a new encrypted database named CCARDS by using the default encryption options:
    RESTORE DATABASE ccards ENCRYPT;
    Restore into the same database by using explicitly provided encryption options to decrypt the backup image:
    RESTORE DATABASE ccards
      ENCRLIB 'libdb2encr.dll'
      ENCROPTS 'Master key Label=mylabel.mydb.myinstance.myserver';
    If you cannot remember what master key label was used to protect a backup image, run the RESTORE DATABASE command with the SHOW MASTER KEY DETAILS encryption option. The database is not restored. For example:
    RESTORE DATABASE ccards
      ENCRLIB 'libdb2encr.dll'
      ENCROPTS 'show master key details'
    The command returns the label for each master key that was used to protect the backup image, as well as the path to the keystore and the server name and server IP address where the master key was located at the time that the backup was taken. This information is available in the sqllib/db2dump directory in a file whose name has the following format:
    db-name.inst-type.inst-name.
      db-partition.timestamp.masterKeyDetails

If the parameter AT DBPARTITIONNUM is used to re-create 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.

Usage notes

  • A RESTORE DATABASE command of the form db2 restore db name will perform a full database restore with a database image and will perform a table space restore operation of the table spaces found in a table space image. A RESTORE DATABASE command of the form db2 restore db name tablespace performs a table space restore of the table spaces found in the image. In addition, if a list of table spaces is provided with such a command, the explicitly listed table spaces are restored.
  • Following the restore operation of an online backup, you must perform a roll forward recovery.
  • You can use the OPTIONS parameter to enable restore operations in TSM environments supporting proxy nodes. For more information, see the "Configuring a Tivoli Storage Manager client" topic.
  • If a backup image is compressed, the DB2 database system detects this and automatically decompresses the data before restoring it. If a library is specified on the db2Restore API, it is used for decompressing the data. Otherwise, a check is made to see if a library is stored in the backup image and if the library exists, it is used. Finally, if there is not library stored in the backup image, the data cannot be decompressed and the restore operation fails.
  • If the compression library is to be restored from a backup image (either explicitly by specifying the COMPRESSION LIBRARY option or implicitly by performing a normal restore of a compressed backup), the restore operation must be done on the same platform and operating system that the backup was taken on. If the platform the backup was taken on is not the same as the platform that the restore is being done on, the restore operation will fail, even if the DB2 database system normally supports cross-platform restores involving the two systems.
  • A backed up SMS table space can only be restored into a SMS table space. You cannot restore it into a DMS table space, or vice versa.
  • To restore log files from the backup image that contains them, the LOGTARGET option must be specified, providing the fully qualified and valid path that exists on the DB2 server. If those conditions are satisfied, the restore utility will write the log files from the image to the target path. If a LOGTARGET is specified during a restore of a backup image that does not include logs, the restore operation will return an error before attempting to restore any table space data. A restore operation will also fail with an error if an invalid, or read-only, LOGTARGET path is specified.
  • If any log files exist in the LOGTARGET path at the time the RESTORE DATABASE command is issued, a warning prompt will be returned to the user. This warning will not be returned if WITHOUT PROMPTING is specified.
  • During a restore operation where a LOGTARGET is specified, if any log file cannot be extracted, the restore operation will fail and return an error. If any of the log files being extracted from the backup image have the same name as an existing file in the LOGTARGET path, the restore operation will fail and an error will be returned. The restore database utility will not overwrite existing log files in the LOGTARGET directory.
  • You can also restore only the saved log set from a backup image. To indicate that only the log files are to be restored, specify the LOGS option in addition to the LOGTARGET path. Specifying the LOGS option without a LOGTARGET path will result in an error. If any problem occurs while restoring log files in this mode of operation, the restore operation will terminate immediately and an error will be returned.
  • During an automatic incremental restore operation, only the log files included in the target image of the restore operation will be retrieved from the backup image. Any log files included in intermediate images referenced during the incremental restore process will not be extracted from those intermediate backup images. During a manual incremental restore operation, the LOGTARGET path should only be specified with the final restore command to be issued.
  • Offline full database backups as well as offline incremental database backups can be restored to a later database version, whereas online backups cannot. For multi-partition databases, the catalog partition must first be restored individually, followed by the remaining database partitions (in parallel or serial). However, the implicit database upgrade done by the restore operation can fail. In a multi-partition database it can fail on one or more database partitions. In this case, you can follow the RESTORE DATABASE command with a single UPGRADE DATABASE command issued from the catalog partition to upgrade the database successfully.
  • In a partitioned database environment, a table space can have a different storage group association on different database partitions. When a redirected restore modifies table space containers from DMS to automatic storage, the table space is associated with the default storage group. If a new default storage group is selected in between redirected restores of different database partitions, then the table space will have an inconsistent storage group association across the partitioned database environment. If this occurs then use the ALTER TABLESPACE statement to alter the table space to use automatic storage on all database partitions, and rebalance if necessary.
  • The TRANSPORT option is supported only when the client and database code page are equal.
  • The first path that is passed in must contain the first image sequence. If a specified path contains more than one backup image sequence, they must be listed sequentially and continuously.
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 want 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/LOGSTREAMxxxxx/). 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.

The TRANSPORT option to transport tablespace and schemas from the database backup image to the target database is not supported if a schema being transported includes an index with an expression-based key.

Transporting storage groups

A transport operation can not modify the currently defined storage groups of the target database, and new storage groups can not be explicitly created during a transport.

The default target storage group of the transport is the default storage group of the target database of the operation. It is also possible to explicitly redirect all table spaces being restored during a transport operation into a specific storage group on the target database.

During a transport operation, when a RESTORE command using the TRANSPORT REDIRECT option is issued, the default storage group configuration for automatic storage table spaces is not the configuration contained in the backup image, but instead the storage groups and storage group paths of the target database. This is because automatic storage table spaces must be restored and redirected directly into existing storage group paths, as defined on the target database.

DB2 native encryption
When you restore a database backup image to an existing database, the encryption settings of the existing database are always preserved. If you specify the ENCRYPT option, an error is returned because the settings on theRESTORE command will not be used.

When you restore into a new database in a partitioned database environment, restore the catalog partition first, specifying the encryption options. You can then restore the other partitions without specifying the encryption options, because the database already exists. When you use the db2_all command, target the catalog partitions first.

A backup image that was encrypted with DB2 native encryption must be restored into a database server that has DB2 native encryption available. If you want to restore into a server that is using a DB2 version that does not include DB2 native encryption, you must use an unencrypted backup image.