RESTORE DATABASE command

The RESTORE DATABASE command restores a database that is 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 existing database.

Important: The Triple Data Encryption Standard (3DES) native encryption option is deprecated and might be removed in a future release. As a replacement, use the Advanced Encryption Standard (AES) native encryption option.

This utility can also perform the following services:

Overwrite a database with a different image or restore the backup copy to a new database.
Restore backup images in Db2® Version 11.5 that were created in Db2 Versions 10.5 or 11.1.
- If a database upgrade is needed, it is started 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. This operation is done by starting the rollforward utility after a 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 by 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 replaces and redefines all storage groups as they were at the time the backup image was produced, unless otherwise redirected by the user.

For more information about different restore operations, see Backup and restore operations between different operating systems and hardware platforms.

Incremental images and images that captured differences from the time of the previous capture (called a delta image) cannot be restored when a difference exists 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 limitation does not exist following a restore operation within the same environment).

Even with a successful restore operation from one environment to a different environment, some considerations exist: packages must be rebound before use (by 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 considerations are not present during restore operations on the same environment).

A restore operation that is run over an existing database and existing containers reuses the same containers and table space map.

A restore operation that is run against a new database reacquires all containers and rebuilds an optimized table space map. A restore operation that is 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 affects the node on which it is run.

You cannot restore SYSCATSPACE online.

Authorization

To restore to an existing database, use one of the following authorities:

SYSADM
SYSCTRL
SYSMAINT

To restore to a new database, use 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 varies 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 needed 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 resides. 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

Restore-options

Rebuild-tablespace-clause

Open-sessions

Options

Encryption Options

Master Key Options

Command parameters

DATABASE source-database-alias

Alias of the source database from which the backup was taken.

CONTINUE

Specifies that the containers are redefined, and that the final step in a redirected restore operation can be performed.

ABORT

The ABORT parameter:

Stops a redirected restore operation. This operation is useful when an error occurs that requires one or more steps to be repeated. After RESTORE DATABASE with the ABORT option is 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 username to use to connect to the database.

USING password

The password that is used to authenticate the username. If the password is omitted, the user is prompted to enter it.

REBUILD WITH ALL TABLE SPACES IN DATABASE

Restores the database with all the table spaces that are known to the database at the time of the image. This restore overwrites a database if it exists.

REBUILD WITH ALL TABLE SPACES IN DATABASE EXCEPT rebuild-tablespace-clause

Restores the database with all the table spaces that are known to the database at the time of the image except for the table spaces specified in the list. This restore overwrites a database if it exists.

REBUILD WITH ALL TABLE SPACES IN IMAGE

Restores the database with only the table spaces in the image. This restore overwrites a database if it exists.

REBUILD WITH ALL TABLE SPACES IN IMAGE EXCEPT rebuild-tablespace-clause

Restores the database with only the table spaces in the image except for the table spaces specified in the list. This restore overwrites a database if it exists.

REBUILD WITH rebuild-tablespace-clause

Restores the database with only the list of table spaces specified. This restore overwrites a database if it exists.

TABLE SPACE tablespace-name

A list of names that are used to specify the table spaces that are to be restored.

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

SCHEMA schema-name

A list of names that are 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 is 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 is restored into the database directory. If the object does not exist in the backup image, the restore operation fails.

LOGS

This keyword is specified to restore only the set of log files that are contained in the backup image. If the backup image does not contain any log files, the restore operation fails. If this option is specified, the LOGTARGET option must also be specified. This option might take as much time as a full restore operation.

INCREMENTAL

Without extra parameters, the INCREMENTAL parameter specifies a manual cumulative restore operation. During a manual restore, you must issue each restore command manually for each image that is involved in the restore. You must first restore your last image to extract the container information, then proceed in the following order: first, second, third, and so on, up to and including the last image. For more information, see Restoring from an incremental backup.

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 that need 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. This offline restore includes 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 is 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 that is on the Db2 server. The string is 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/pipename/device/remote-storage

The fully qualified path name of the directory, named pipe, or device on which the backup image resides. Restoring a backup image from a named pipe is supported on only Unix and Linux platforms.

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 or instance.

To restore from remote storage, such as IBM Cloud Object Storage or Amazon Simple Storage Service (S3), specify a remote storage location by using a storage access alias. The syntax for specifying a remote storage location is DB2REMOTE://<alias>/<container>/<object>. For more information, see Remote storage requirements.

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 is mounted).
d: Device terminate. Stop using 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 fails 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 timestamp of the database backup image. The timestamp 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 timestamp can also be specified. For example, if two different backup images with timestamps 20021001010101 and 20021002010101 exist, specifying 20021002 causes the image with timestamp 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

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 parameter is not specified ON, then this parameter is synonymous with the TO parameter and only the database directory changes. The storage paths that are associated with the database do not change. Do not include the instance name, database partition number, or log stream ID on the specified path. Db2 will add these automatically to the path that you give. For example, if the path you give is "/home/dbuser", the final path after Db2 adds the necessary subdirectories will be "/home/dbuser/prod/NODE0000/LOGSTREAM0000/".

ON path-list

This parameter redefines the storage paths that are 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 uses path-list as its new storage group paths. Using this parameter with a database that does not have storage groups that are 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. After, if any storage group paths are redirected, those modifications override any paths that are 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 that are replayed during a subsequent rollforward operation.

If the database does not exist on disk and the DBPATH ON parameter is not specified, then the first path is used as the target database directory. Do not include the instance name, database partition number, or log stream ID on the specified path. Db2 will add these automatically to the path that you give. For example, if the path you give is "/home/dbuser", the final path after Db2 adds the necessary subdirectories will be "/home/dbuser/prod/NODE0000/LOGSTREAM0000/".

For a multi-partition database, the ON path-list option can only be specified on the catalog partition. The catalog partition must be stored before any other partitions are restored when the ON option is used. The restore of the catalog-partition with new storage paths places 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 running 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.

In an HADR environment, if the primary database is defined over multiple storage paths with automatic storage enabled, it is important to prevent rebalance during the restore. This can be achieved by using the ON path-list option of the RESTORE DATABASE command, specifying the same set of storage paths in the same order as the primary database (the order can be found via db2pd -db dbname -storagepaths command). The purpose of the ON path-list option is to prevent rebalance, not to let the standby database use a different set of storage paths.

sfdjkfndfgg

You cannot use the ON parameter to redefine storage paths for schema transport. Schema transport uses 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 that are 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 are validated by the RESTORE command, the system catalog entries that describe the objects in the table spaces that are 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 that are contained in the table spaces that are 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 is complete and can be dropped by the DBA.

The statements are true if the STAGE IN option is not specified:

The database name is of the form SYSTG xxx 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 variable specifies the target storage group that is 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 copying active log files from a backup image. If this option is specified, any active log files that are contained within the backup image will be copied into the target directory. If this option is not specified, active log files that are contained within a backup image will not be copied. To restore only the active 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 active 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 are restored. Existing log directories and log files on disk are 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 is returned.
EXCLUDE: Do not restore log directory volumes. If this option is specified, then no log directories are restored from the backup image. Existing log directories and log files on disk is left intact if they do not conflict with the log directories in the backup image. If a path that belongs to the database is restored and a log directory is implicitly restored because of this, thus causing a log directory to overwritten, an error is 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 that conflict with log directories in the snapshot image causes 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 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 path name of a directory that will be used for active and extraction log files and persist 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.

If extraction log files exist in the backup image, they are restored to this log path.

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 uses 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 automatically chooses 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 increases.

BUFFER buffer-size

The size, in pages, of the buffer used for the restore operation. The Db2 database system automatically chooses an optimal value for this parameter unless you explicitly enter a value. The minimum value for this parameter is eight pages.

The restore buffer size must be a positive integer multiple of the backup buffer size that is 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 replaces 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 exists, this parameter specifies that the restore utility is to replace the existing database with the restored database. This is useful for scripts that start the restore utility because the command line processor does 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 fails if events occur that normally require user intervention.

REDIRECT

Specifies a redirected restore operation. To complete a redirected restore operation, this command is 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 is renamed since the backup image was produced, the storage group name that is specified on the SET STOGROUP PATHS command refers to the storage group name from the backup image, not the most recent name.

All commands that are associated with a single redirected restore operation must be started 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 is generated on the client side. If the file cannot created on the client side, an error message (SQL9304N) is returned. If the file exists, it is overwritten. For more information, see the following examples.

WITHOUT ROLLING FORWARD

Specifies that the database is not to be put in rollforward pending state after it is successfully restored.

If, following a successful restore operation, the database is in rollforward pending state, the ROLLFORWARD command must be started before the database can be used again.

If this option is specified when restoring from an online backup image, error SQL2537N is returned.

If the 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.a (for AIX®); and db2encr.dll (for Windows operating systems)
Compression library: libdb2compr.so (for Linux or UNIX based operating systems); libdb2compr.a (for AIX); and 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)
Encryption and NX842 compression library: libdb2nx842_encr.a (for AIX)
ZLIB-based compression library: libdb2zcompr.so (for Linux or UNIX based operating systems); libdb2zcompr.a (for AIX); and db2zcompr.dll (for Windows operating systems)
Encryption and ZLIB-based compression libraries: libdb2zcompr_encr.so (for Linux or UNIX based operating systems); libdb2zcompr_encr.a (for AIX); and db2zcompr_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 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 the data block 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, libdb2zcompr_encr.so, or libdb2nx842_encr.a (compression and encryption) or libdb2encr.so (encryption only), the format of the ENCROPTS variable is as follows:

Master Key Label=label-name

Note: The libdb2zcompr_encr.so library is available in Db2 11.5.7 and later versions.

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 variable 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, which are 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

In the following example, the database WSDB is defined on all 4 database partitions, numbered 0 - 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.
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.

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 use TSM taken at (Fri)

For a manual database restore of the images created on Friday morning, issue:

       restore db mydb incremental use TSM taken at (Fri)
       restore db mydb incremental use TSM taken at (Sun)
       restore db mydb incremental use TSM taken at (Wed)
       restore db mydb incremental use TSM taken at (Thu)
       restore db mydb incremental use TSM taken at (Fri)

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 )

To retrieve only the active log files from a backup image that includes logs:
```
   restore db sample logs from /dev3/backup logtarget /dev3/logs
```
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
```
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
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

Running a script by executing 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

The output will look like this example:

-- ****************************************************************************
-- ** 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 DEFAULT
-- 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
-- ****************************************************************************

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

The output will look like this example:

-- ****************************************************************************
-- ** 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 DEFAULT
-- 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
-- ****************************************************************************

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
```
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 cannot 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 that is 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.
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.so'
  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; its output is the equivalent of running the ADMIN_GET_ENCRYPTION_INFO table function. The database is not restored. For example:
```
RESTORE DATABASE ccards
  ENCRLIB 'libdb2encr.so'
  ENCROPTS 'show master key details'
```
The command returns the label for each master key that was used to protect the backup image. The command also returns information about the location of the master key 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
```
The following is an example of a restore from an existing database, called PROD, into a non-existing database, called TEST. In the example, PROD is configured with a non-default log path, and TEST is being restored on the same instance as PROD. The restore will configure TEST with a non-default log path.
```
db2 restore db PROD INTO TEST NEWLOGPATH /dev/db2/testdb
```
The following example shows the command syntax for running a restore operation from an existing database, called SAMPLE, into a non-existing database, called SAMPLE2, where the NEWLOGPATH is configured to use the default log path.
Note: The NEWLOGPATH DEFAULT option is available in Db2 11.5.6 and later.
```
db2 restore db SAMPLE into SAMPLE2 NEWLOGPATH DEFAULT
```
You can use a named pipe to back up one database directly into another without saving the intermediate backup image.
The following example copies a source database (srcdb) into the target database (tgtdb). You can enter the BACKUP and RESTORE commands in either order.
(in one session)
```
$ db2 backup db srcdb to /<pipename> 
```
(in another session)
```
$ db2 restore db srcdb from /<pipename> into tgtdb
```

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 re-creating the database partition, the database must immediately be restored on this database partition.

Usage notes

In a Db2 pureScale environment, both the RESTORE operation using the REBUILD option, as well as the ensuing database ROLLFORWARD operation, must be performed on a member that exists within the database member topology of every backup image involved in the operation. For example, suppose the RESTORE REBUILD operation uses two backup images: backup-image-A has database member topology {0,1}, and backup-image-B has database member topology {0, 1, 2, 3}. Then, both the RESTORE operation and the ensuing ROLLFORWARD operation must be performed on either member-0 or member-1 (which exist in all backup images).
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 that are 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 that are 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 rollforward 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 a library is not 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 fails , 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 an SMS table space. You cannot restore it into a DMS table space, or vice versa.
To restore active 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 active 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 fore 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 active log files exist in the LOGTARGET path at the time the RESTORE DATABASE command is issued, a warning prompt is returned be 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 active log file cannot be restored, the restore operation will fail and return an error. If any of the active log files being copied from the backup image have the same name as an existing file in the LOGTARGET path, the restore operation fails and an error will be returned. The restore database utility will not overwrite existing active log files in the LOGTARGET directory.
You can also restore only the saved active log set from a backup image. To indicate that only the active 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 active 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 active and extraction log files included in the target image of the restore operation will be copied from the backup image. Any log files that are included in intermediate images referenced during the incremental restore process will not be copied 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.
For a database-level restore operation, if the backup image contains extraction log files, the extraction logs will be restored to the active log directory, regardless of the setting of the LOGTARGET parameter. No extraction log files will be restored for tablespace-level restore operations, or for restore types that do not restore data. This even includes restore operations of the form RESTORE DB ... LOGS.
If an extraction log file cannot be restored, the restore operation will continue. However, Db2 will not be able to validate that the correct range of extraction log files are present at the start of the subsequent ROLLFORWARD operation so it will delete all extraction log files on the log stream in question. You must then ensure that the database's log archive is available to the Rollforward utility so that active log files can be used. If the log archive is not available the recovery logs will need to be retrieved manually.
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 that is 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.
If you enable Health Monitor while running the RESTORE DATABASE command, it may try to connect during the implicit database upgrade, resulting in the SQL1035N message. To avoid this problem, disable Health Monitor before you run the command.
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.
For the Db2 Developer-C Edition, restoring a backup database that has a total size of all table spaces greater than the defined storage size, or restoring on an SMS table space will result in a fail.
During a database restore, validation of the target primary active log path takes place early. Validation ensures that the target primary active log path exists and is not currently being used by another database. If the target primary active log path cannot be successfully validated at the start of the operation, the RESTORE command will fail (SQL5099N). Therefore, it is recommended, especially when restoring into another database or location, that users validate all active log paths before issuing the RESTORE command and make use of the NEWLOGPATH option if any changes need to be made.

Snapshot restore

Like a traditional (non-snapshot) restore, the default havior when restoring a snapshot backup image isbe 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 match the page size of the table spaces being transported, then a hidden buffer pool might be assigned. Hidden buffer pools are temporary place holders for transported table spaces. You can check the buffer pools assigned to the 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 table spaces 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 cannot modify the currently defined storage groups of the target database, and new storage groups cannot 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 that is 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 optionsbecause the database 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.