DB2 Version 9.7 for Linux, UNIX, and Windows

RESTORE DATABASE command

The RESTORE DATABASE command restores a database that has been backed up using the DB2® backup utility. The restored database is in the same state that it was in when the backup copy was made.

This utility can also perform the following:

Overwrite a database with a different image or restore the backup copy to a new database.
Restore backup images in DB2 Version 9.7 that were backed up on DB2 Universal Database™ Version 8, DB2 Version 9.1, or DB2 Version 9.5.
- 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 and SQL schemas from database backup image to a database using the TRANSPORT option (starting in DB2 Version 9.7 Fix Pack 2).

For information on the restore operations supported by DB2 database systems between different operating systems and hardware platforms, see "Backup and restore operations between different operating systems and hardware platforms" in the Related concepts section.

Incremental images and images only capturing differences from the time of the previous capture (called a "delta image") cannot be restored when there is a difference in operating systems or word size (32-bit or 64-bit).

Following a successful restore operation from one environment to a different environment, no incremental or delta backups are allowed until a non-incremental backup is taken. (This is not a limitation following a restore operation within the same environment.)

Even with a successful restore operation from one environment to a different environment, there are some considerations: packages must be rebound before use (using the BIND command, the REBIND command, or the db2rbind utility); SQL procedures must be dropped and recreated; and all external libraries must be rebuilt on the new platform. (These are not considerations when restoring to the same environment.)

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

A restore operation run against a new database reacquires all containers and rebuilds an optimized table space map. A restore operation run over an existing database with one or more missing containers also reacquires all containers and rebuilds an optimized table space map.

Scope

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

Authorization

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

SYSADM
SYSCTRL
SYSMAINT

To restore to a new database, one of the following:

SYSADM
SYSCTRL

If you specify a user name, you require 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


>>-RESTORE--+-DATABASE-+--source-database-alias----------------->
            '-DB-------'                          

>--+-| restore-options |-+-------------------------------------><
   +-CONTINUE------------+   
   '-ABORT---------------'   

restore-options

|--+-------------------------------------+---------------------->
   '-USER--username--+-----------------+-'   
                     '-USING--password-'     

>--+--------------------------------------------------------------------------------------------------+-->
   +-REBUILD WITH--+-+-ALL TABLESPACES IN DATABASE-+--+---------------------------------------+-+-----+   
   |               | '-ALL TABLESPACES IN IMAGE----'  '-EXCEPT--| rebuild-tablespace-clause |-' |     |   
   |               '-| rebuild-tablespace-clause |----------------------------------------------'     |   
   '-+-TABLESPACE--+------------------------------------------------------------------+-+--+--------+-'   
     |             |    .-,---------------.                                           | |  '-ONLINE-'     
     |             |    V                 |                                           | |                 
     |             '-(----tablespace-name-+--)--+-----------------------------------+-' |                 
     |                                          '-SCHEMA--+-----------------------+-'   |                 
     |                                                    |    .-,-----------.    |     |                 
     |                                                    |    V             |    |     |                 
     |                                                    '-(----schema-name-+--)-'     |                 
     +-HISTORY FILE---------------------------------------------------------------------+                 
     +-COMPRESSION LIBRARY--------------------------------------------------------------+                 
     '-LOGS-----------------------------------------------------------------------------'                 

>--+----------------------------+------------------------------->
   '-INCREMENTAL--+-----------+-'   
                  +-AUTO------+     
                  +-AUTOMATIC-+     
                  '-ABORT-----'     

>--+-----------------------------------------------------------+-->
   +-USE--+-+-TSM--+-| open-sessions |----------+--| options |-+   
   |      | '-XBSA-'                            |              |   
   |      '-SNAPSHOT--+-----------------------+-'              |   
   |                  +-LIBRARY--library-name-+                |   
   |                  '-SCRIPT--script-name---'                |   
   +-LOAD--shared-library--| open-sessions |--| options |------+   
   |       .-,-------------.                                   |   
   |       V               |                                   |   
   '-FROM----+-directory-+-+-----------------------------------'   
             '-device----'                                         

>--+---------------------+-------------------------------------->
   '-TAKEN AT--date-time-'   

>--+------------------------------------------------+----------->
   +-TO--target-directory---------------------------+   
   +-DBPATH ON--target-directory--------------------+   
   '-ON--path-list--+-----------------------------+-'   
                    '-DBPATH ON--target-directory-'     

>--+-------------------------------------------+---------------->
   '-TRANSPORT--+----------------------------+-'   
                '-STAGE IN--staging-database-'     

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

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

>--+-----------------------+--+----------------------------+---->
   '-NEWLOGPATH--directory-'  '-WITH--num-buffers--BUFFERS-'   

>--+---------------------+--+----------------------+------------>
   '-BUFFER--buffer-size-'  '-REPLACE HISTORY FILE-'   

>--+------------------+----------------------------------------->
   '-REPLACE EXISTING-'   

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

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

>--+-------------------+--+-------------------------+----------->
   '-COMPROPTS--string-'  '-WITHOUT ROLLING FORWARD-'   

>--+-------------------+----------------------------------------|
   '-WITHOUT PROMPTING-'   

rebuild-tablespace-clause

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

open-sessions

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

options

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

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 from output managed by Tivoli® Storage Manager.

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:

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 System Storage® DS6000™
IBM System Storage DS8000®
IBM System Storage N Series
IBM XIV®
NetApp V-series
NetApp FAS

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 an automatic storage database. Using this parameter with a database that 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.

One or more paths can be specified, each separated by a comma. Each path must have an absolute path name and it must exist locally. If the database does not already exist on disk and the DBPATH ON parameter is not specified then the first path is used as the target database directory.

For a multi-partition database the ON path-list option can only be specified on the catalog partition. The catalog partition must be restored before any other partitions are restored when the ON option is used. The restore of the catalog-partition with new storage paths will place all non-catalog nodes in a RESTORE_PENDING state. The non-catalog nodes can then be restored in parallel without specifying the ON clause in the restore command.

In general, the same storage paths must be used for each partition in a multi-partition database and they must all exist prior to executing the RESTORE DATABASE command. One exception to this is where database partition expressions are used within the storage path. Doing this allows the database partition number to be reflected in the storage path such that the resulting path name is different on each partition.

You cannot use the ON parameter to re-define storage paths for schema transport. Schema transport will use existing storage paths on the target database.

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 created tablespaces cannot be transported. SQLCODE=SQL2590N rc=4.

After the schemas have been validated by the RESTORE command, the system catalog entries describing the objects in the table spaces being transported are created in the target database. After completion of the schema recreation, the target database takes ownership of the physical table space containers.

The physical and logical objects contained in the table spaces being restored are re-created in the target database and the table space definitions and containers are added to the target database. Failure during the creation of an object, or the replay of the DDL returns an error.

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.

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.

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. If you specify the TRANSPORT parameter and use an online backup image for the transporting schema feature, the LOGTARGET parameter is mandatory.

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, except that its effect is limited to the restore operation in which it is specified. 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: Once the newlogpath command parameter is set, the node number will not be automatically appended to the value of logpath as is the case when updating the newlogpath database configuration parameter. See newlogpath - Change the database log path for more information.

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, and then by a RESTORE DATABASE command with the CONTINUE option. 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 examples below 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 name

Indicates the name of the library to be used to perform the decompression (e.g., db2compr.dll for Windows; libdb2compr.so for Linux/UNIX systems). The name must be a fully qualified path referring to a file on the server. If this parameter is not specified, the DB2 database system will attempt to use the library stored in the image. If the backup was not compressed, the value of this parameter will be ignored. If the specified library cannot be loaded, the restore operation will fail.

COMPROPTS string

Describes a block of binary data that is passed to the initialization routine in the decompression library. The DB2 database system passes this string directly from the client to the server, so any issues of byte reversal or code page conversion are handled by the decompression 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 residing on the server. The DB2 database system will then replace the contents of string with the contents of this file and pass the new value to the initialization routine instead. The maximum length for the string is 1 024 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 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.NODE0000.CATN0000.20020331234149.001
    wsdb.0.db2inst1.NODE0001.CATN0000.20020331234427.001 
    wsdb.0.db2inst1.NODE0002.CATN0000.20020331234828.001
    wsdb.0.db2inst1.NODE0003.CATN0000.20020331235235.001
```
To restore the catalog partition first, then all other database partitions of the WSDB database from the /dev3/backup directory, issue the following commands from one of the database partitions:
```
    db2_all '<<+0< db2 RESTORE DATABASE wsdb FROM /dev3/backup 
    TAKEN AT 20020331234149 
      INTO wsdb REPLACE EXISTING'
    db2_all '<<+1< db2 RESTORE DATABASE wsdb FROM /dev3/backup 
    TAKEN AT 20020331234427 
      INTO wsdb REPLACE EXISTING'
    db2_all '<<+2< db2 RESTORE DATABASE wsdb FROM /dev3/backup 
    TAKEN AT 20020331234828 
      INTO wsdb REPLACE EXISTING'
    db2_all '<<+3< db2 RESTORE DATABASE wsdb FROM /dev3/backup 
    TAKEN AT 20020331235235 
      INTO wsdb REPLACE EXISTING'
```
The db2_all utility issues the restore command to each specified database partition. When performing a restore using db2_all, you should always specify REPLACE EXISTING and/or WITHOUT PROMPTING. Otherwise, if there is prompting, the operation will look like it is hanging. This is because db2_all does not support user prompting.
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 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)

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

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/NODE0000/SQL00001/SQLOGDIR/'
-- WITH <num-buff> BUFFERS
-- BUFFER <buffer-size>
-- REPLACE HISTORY FILE
-- REPLACE EXISTING
REDIRECT
-- PARALLELISM <n>
-- WITHOUT ROLLING FORWARD
-- WITHOUT PROMPTING
;
-- ****************************************************************************
-- ** tablespace definition
-- ****************************************************************************
-- ****************************************************************************
-- ** Tablespace name                            = SYSCATSPACE
-- **   Tablespace ID                            = 0
-- **   Tablespace Type                          = System managed space
-- **   Tablespace Content Type                  = Any data
-- **   Tablespace Page size (bytes)             = 4096
-- **   Tablespace Extent size (pages)           = 32
-- **   Using automatic storage                  = No
-- **   Total number of pages                    = 5572
-- ****************************************************************************
SET TABLESPACE CONTAINERS FOR 0
-- IGNORE ROLLFORWARD CONTAINER OPERATIONS
USING (
  PATH   'SQLT0000.0'
);
-- ****************************************************************************
-- ** Tablespace name                            = TEMPSPACE1
-- **   Tablespace ID                            = 1
-- **   Tablespace Type                          = System managed space
-- **   Tablespace Content Type                  = System Temporary data
-- **   Tablespace Page size (bytes)             = 4096
-- **   Tablespace Extent size (pages)           = 32
-- **   Using automatic storage                  = No
-- **   Total number of pages                    = 0
-- ****************************************************************************
SET TABLESPACE CONTAINERS FOR 1
-- IGNORE ROLLFORWARD CONTAINER OPERATIONS
USING (
  PATH   'SQLT0001.0'                                                           
);
-- ****************************************************************************
-- ** Tablespace name                            = USERSPACE1
-- **   Tablespace ID                            = 2
-- **   Tablespace Type                          = System managed space
-- **   Tablespace Content Type                  = Any data
-- **   Tablespace Page size (bytes)             = 4096
-- **   Tablespace Extent size (pages)           = 32
-- **   Using automatic storage                  = No
-- **   Total number of pages                    = 1
-- ****************************************************************************
SET TABLESPACE CONTAINERS FOR 2
-- IGNORE ROLLFORWARD CONTAINER OPERATIONS
USING (
  PATH   'SQLT0002.0'                                                            
);
-- ****************************************************************************
-- ** Tablespace name                            = DMS
-- **   Tablespace ID                            = 3
-- **   Tablespace Type                          = Database managed space
-- **   Tablespace Content Type                  = Any data
-- **   Tablespace Page size (bytes)             = 4096
-- **   Tablespace Extent size (pages)           = 32
-- **   Using automatic storage                  = No
-- **   Auto-resize enabled                      = No
-- **   Total number of pages                    = 2000
-- **   Number of usable pages                   = 1960
-- **   High water mark (pages)                  = 96
-- ****************************************************************************
SET TABLESPACE CONTAINERS FOR 3
-- IGNORE ROLLFORWARD CONTAINER OPERATIONS
USING (
  FILE   /tmp/dms1                                                    1000
, FILE   /tmp/dms2                                                    1000
);
-- ****************************************************************************
-- ** Tablespace name                            = RAW
-- **   Tablespace ID                            = 4
-- **   Tablespace Type                          = Database managed space
-- **   Tablespace Content Type                  = Any data
-- **   Tablespace Page size (bytes)             = 4096
-- **   Tablespace Extent size (pages)           = 32
-- **   Using automatic storage                  = No
-- **   Auto-resize enabled                      = No
-- **   Total number of pages                    = 2000
-- **   Number of usable pages                   = 1960
-- **   High water mark (pages)                  = 96
-- ****************************************************************************
SET TABLESPACE CONTAINERS FOR 4
-- IGNORE ROLLFORWARD CONTAINER OPERATIONS
USING (
  DEVICE '/dev/hdb1'                                          1000
, DEVICE '/dev/hdb2'                                          1000
);
-- ****************************************************************************
-- ** start redirect restore
-- ****************************************************************************
RESTORE DATABASE SAMPLE CONTINUE;
-- ****************************************************************************
-- ** end of file
-- ****************************************************************************

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_DBPARTITIONNUM  0;
SET CLIENT CONNECT_DBPARTITIONNUM 0;
-- ****************************************************************************
-- ** initialize redirected restore
-- ****************************************************************************
RESTORE DATABASE TEST
-- USER  '<username>'
-- USING '<password>'
FROM '/home/jseifert/backups'
TAKEN AT 20050304090733
ON '/home/jseifert'
-- DBPATH ON <target-directory>
INTO TEST
-- NEWLOGPATH '/home/jseifert/jseifert/NODE0000/SQL00002/SQLOGDIR/'
-- WITH <num-buff> BUFFERS
-- BUFFER <buffer-size>
-- REPLACE HISTORY FILE
-- REPLACE EXISTING
REDIRECT
-- PARALLELISM <n>
-- WITHOUT ROLLING FORWARD
-- WITHOUT PROMPTING
;
-- ****************************************************************************
-- ** tablespace definition
-- ****************************************************************************
-- ****************************************************************************
-- ** Tablespace name                            = SYSCATSPACE
-- **   Tablespace ID                            = 0
-- **   Tablespace Type                          = Database managed space
-- **   Tablespace Content Type                  = Any data
-- **   Tablespace Page size (bytes)             = 4096
-- **   Tablespace Extent size (pages)           = 4
-- **   Using automatic storage                  = Yes
-- **   Auto-resize enabled                      = Yes
-- **   Total number of pages                    = 6144
-- **   Number of usable pages                   = 6140
-- **   High water mark (pages)                  = 5968
-- ****************************************************************************
-- ****************************************************************************
-- ** Tablespace name                            = TEMPSPACE1
-- **   Tablespace ID                            = 1
-- **   Tablespace Type                          = System managed space
-- **   Tablespace Content Type                  = System Temporary data
-- **   Tablespace Page size (bytes)             = 4096
-- **   Tablespace Extent size (pages)           = 32
-- **   Using automatic storage                  = Yes
-- **   Total number of pages                    = 0
-- ****************************************************************************
-- ****************************************************************************
-- ** Tablespace name                            = USERSPACE1
-- **   Tablespace ID                            = 2
-- **   Tablespace Type                          = Database managed space
-- **   Tablespace Content Type                  = Any data
-- **   Tablespace Page size (bytes)             = 4096
-- **   Tablespace Extent size (pages)           = 32
-- **   Using automatic storage                  = Yes
-- **   Auto-resize enabled                      = Yes
-- **   Total number of pages                    = 256
-- **   Number of usable pages                   = 224
-- **   High water mark (pages)                  = 96
-- ****************************************************************************
-- ****************************************************************************
-- ** Tablespace name                            = DMS
-- **   Tablespace ID                            = 3
-- **   Tablespace Type                          = Database managed space
-- **   Tablespace Content Type                  = Any data
-- **   Tablespace Page size (bytes)             = 4096
-- **   Tablespace Extent size (pages)           = 32
-- **   Using automatic storage                  = No
-- **   Auto-resize enabled                      = No
-- **   Total number of pages                    = 2000
-- **   Number of usable pages                   = 1960
-- **   High water mark (pages)                  = 96
-- ****************************************************************************
SET TABLESPACE CONTAINERS FOR 3
-- IGNORE ROLLFORWARD CONTAINER OPERATIONS
USING (
  FILE   '/tmp/dms1'                                          1000
, FILE   '/tmp/dms2'                                          1000
);
-- ****************************************************************************
-- ** Tablespace name                            = RAW
-- **   Tablespace ID                            = 4
-- **   Tablespace Type                          = Database managed space
-- **   Tablespace Content Type                  = Any data
-- **   Tablespace Page size (bytes)             = 4096
-- **   Tablespace Extent size (pages)           = 32
-- **   Using automatic storage                  = No
-- **   Auto-resize enabled                      = No
-- **   Total number of pages                    = 2000
-- **   Number of usable pages                   = 1960
-- **   High water mark (pages)                  = 96
-- ****************************************************************************
SET TABLESPACE CONTAINERS FOR 4
-- IGNORE ROLLFORWARD CONTAINER OPERATIONS
USING (
  DEVICE '/dev/hdb1'                                          1000
, DEVICE '/dev/hdb2'                                          1000
);
-- ****************************************************************************
-- ** start redirect restore
-- ****************************************************************************
RESTORE DATABASE TEST CONTINUE;
-- ****************************************************************************
-- ** end of file
-- ****************************************************************************

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
```

If the parameter AT DBPARTITIONNUM is used to recreate a database partition that was dropped (because it was damaged), the database at this database partition will be in the restore-pending state. After recreating the database partition, the database must immediately be restored on this database partition.

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.
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.
To restore an automatic storage table space, you need to use the absolute path of the table space that is used in the backup image.

Snapshot restore

Like a traditional (non-snapshot) restore, the default behavior when restoring a snapshot backup image will be to NOT restore the log directories -LOGTARGET EXCLUDE.

If the DB2 database manager detects that any log directory's group ID is shared among any of the other paths to be restored, then an error is returned. In this case, LOGTARGET INCLUDE or LOGTARGET INCLUDE FORCE must be specified, as the log directories must be part of the restore.

The DB2 database manager will make all efforts to save existing log directories (primary, mirror and overflow) before the restore of the paths from the backup image takes place.

If you wish the log directories to be restored and the DB2 database manager detects that the pre-existing log directories on disk conflict with the log directories in the backup image, then the DB2 database manager will report an error. In such a case, if you have specified LOGTARGET INCLUDE FORCE, then this error will be suppressed and the log directories from the image will be restored, deleting whatever existed beforehand.

There is a special case in which the LOGTARGET EXCLUDE option is specified and a log directory path resides under the database directory (for example, /NODExxxx/SQLxxxxx/SQLOGDIR/). In this case, a restore would still overwrite the log directory as the database path, and all of the contents beneath it, would be restored. If the DB2 database manager detects this scenario and log files exist in this log directory, then an error will be reported. If you specify LOGTARGET EXCLUDE FORCE, then this error will be suppressed and those log directories from the backup image will overwrite the conflicting log directories on disk.

Transporting table spaces and schemas

The complete list of table spaces and schemas must be specified.

The target database must be active at the time of transport.

If an online backup image is used, then the staging database is rolled forward to the end of the backup. If an offline backup image is used, then no rollforward processing is performed.

A staging database consisting of the system catalog table space from the backup image is created under the path specified by the dftdbpath database parameter. This database is dropped when the RESTORE DATABASE command completes. The staging database is required to extract the DDL used to regenerate the objects in the table spaces being transported.

When transporting table spaces, the DB2 database manager attempts to assign the first available buffer pool of matching page size to the table space that is transported. If the target database does not have buffer pools that are of matching page size of table spaces transported, then a hidden buffer pool might be assigned. Hidden buffer pools are temporary place holders for transported table spaces. You can check buffer pools assigned to transported table spaces after transport completes. You can issue the ALTER TABLESPACE command to update buffer pools.

If database rollforward detects a table space schema transport log record, the corresponding transported table space will be taken offline and moved into drop pending state. This is because database does not have complete logs of transported table spaces to rebuild transported table spaces and their contents. You can take a full backup of the target database after transport completes, so subsequent rollforward does not pass the point of schema transport in the log stream.