Command Reference

RESTORE DATABASE

Rebuilds a damaged or corrupted database that has been backed up using BACKUP DATABASE. The restored database is in the same state it was in when the backup copy was made. This utility can also restore to a database with a name different from the database name in the backup image (in addition to being able to restore to a new database).

The utility can also be used to restore previous versions of DB2 databases.

If, at the time of the backup operation, the database was enabled for roll-forward recovery, the database can be brought to the state it was in prior to the occurrence of the damage or corruption by issuing ROLLFORWARD DATABASE after successful execution of RESTORE DATABASE.

This utility can also restore from a table space level backup.

To restore a database that was backed up on a different workstation platform, use db2move - Database Movement Tool.

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

Required Connection

Database, to restore to an existing database.

Instance and database, to restore to a new database. The instance attachment is required to create the database.

To restore to a new remote database, it is necessary to first attach to the instance where the new database will reside.

Command Syntax

>>-RESTORE----+-DATABASE-+--source-database-alias--------------->
              '-DB-------'
 
>-----+-| restore-options |-+----------------------------------><
      +-CONTINUE------------+
      '-ABORT---------------'
 
restore-options
 
|---+---------------------------------------+------------------->
    '-USER--username--+------------------+--'
                      '-USING--password--'
 
>-----+--------------------------------------------------------+>
      +-TABLESPACE ONLINE--------------------------------------+
      |                .-,------------------.                  |
      |                V                    |                  |
      +-TABLESPACE--(-----tablespace-name---+---)--+---------+-+
      |                                            '-ONLINE--' |
      '-HISTORY FILE--+---------+------------------------------'
                      '-ONLINE--'
 
>-----+---------------------------------------------------------+>
      +-USE ADSM--+-------------------------------+-------------+
      |           '-OPEN--num-sessions--SESSIONS--'             |
      |       .-,----------------.                              |
      |       V                  |                              |
      +-FROM------+-directory-+--+------------------------------+
      |           '-device----'                                 |
      '-LOAD--shared-library--+-------------------------------+-'
                              '-OPEN--num-sessions--SESSIONS--'
 
>-----+----------------------+---+-----------------------+------>
      '-TAKEN AT--date-time--'   '-TO--target-directory--'
 
>-----+------------------------------+-------------------------->
      '-INTO--target-database-alias--'
 
>-----+------------------------+-------------------------------->
      '-NEWLOGPATH--directory--'
 
>-----+-----------------------------+--------------------------->
      '-WITH--num-buffers--BUFFERS--'
 
>-----+----------------------+---+---------------------+-------->
      '-BUFFER--buffer-size--'   '-DLREPORT--filename--'
 
>----+------------------+---+----------+----+-----------------+->
     '-REPLACE EXISTING-'   '-REDIRECT-'    '-PARALLELISM--n--'
 
>----+-------------------------+---+------------------+--------->
     '-WITHOUT ROLLING FORWARD-'   '-WITHOUT DATALINK-'
 
>----+-------------------+--------------------------------------|
     '-WITHOUT PROMPTING-'

Command Parameters

DATABASE source-database-alias

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

CONTINUE

Indicates that the containers have been redefined, and that the final step in the redirected restore should be performed.

ABORT

Stops the redirected restore. Useful when an error has occurred that would require one or more steps to be repeated. After RESTORE DATABASE with the ABORT option has been issued, each step of a redirected restore must be repeated, including RESTORE DATABASE with the REDIRECT option.

USER username

Identifies the user name under which the database is to be restored.

USING password

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

TABLESPACE tablespace-name

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

ONLINE

This keyword, applicable only when doing a table space level restore, is specified to allow the backup to be restored online. This means that other agents can connect while the backup is being restored.

HISTORY FILE

This keyword is specified to restore the history file from the backup only.

USE ADSM

Indicates that the database is to be restored from ADSM-managed output.

OPEN num-sessions SESSIONS

The number of I/O sessions to be used with ADSM or the vendor product.

FROM directory/device

The directory or device on which the backup images reside. If USE ADSM, FROM, and LOAD are omitted, the default is the current directory.

Note: On OS/2 or the Windows operating system, the specified directory must not be a DB2-generated directory. For example, given the following commands:
db2 backup database sample to c:\backup db2 restore database sample from c:\backup

DB2 generates subdirectories under the c:\backup directory that should be ignored. To specify precisely which backup image to restore, use the TAKEN AT parameter. There may be several backup images stored in the same path.

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, when a new tape has been mounted)
d: Device terminate. Stop using only the device that generated the warning message (for example, when there are no more tapes)
t: Terminate. Abort the restore or backup utility.

Tape is not supported on OS/2. On OS/2, 0 or 0: can be specified to cause the restore operation to call the user exit program (see the Administration Guide). This option is not valid on any other platform.
Note: Redirected restore is not allowed when a user exit program is used to perform the restore.

LOAD shared-library

The name of the shared library (DLL on OS/2 or the Windows operating system) containing the vendor backup and restore I/O functions to be used. It may contain the full path. If the full path is not given, it will default to the path where the user exit programs reside.

TAKEN AT date-time

The time stamp of the database backup. The backup image file name includes the time stamp.

TO target-directory

Directory of the target database. This parameter is ignored if the utility is restoring to an existing database.

INTO target-database-alias

Alias of the target database. If the target database does not exist, it will be created.

NEWLOGPATH directory

A fully qualified directory where recovery log files for the database being restored will be kept. These logs will then be used during rollforward, and any subsequent operation requiring logging. This parameter has the same function as the database configuration parameter newlogpath, except that its effect is limited to the RESTORE command in which it is specified.

WITH num-buffers BUFFERS

The number of buffers to be used.

BUFFER buffer-size

The size, in pages, of the buffer used for the restore operation. The minimum value for this parameter is 16 pages; the default value is 1024 pages. If a buffer-size of zero is specified, the value in the database manager configuration parameter restbufsz must be set to 16.

The specified value is compared to the value specified during the backup operation. The actual restore buffer size will be an even multiple of the backup buffer size, which is equal to or greater than the backup buffer size. For example, if a backup buffer size of 1024 pages were specified, and an attempt were made to restore this backup with a buffer size of 16 pages, the actual restore buffer size would be 1024. If the specified restore buffer size were 2049, the actual restore buffer size would be 2048.

To use tape devices, DB2 users on SCO UnixWare 7 must specify a buffer size of 16.

DLREPORT filename

The file name, if specified, must be fully qualified. The files which become unlinked during restore (as a result of a fast reconcile) will be reported. This option is only to be used if the table being restored has a DATALINK column type and data linked files.

REPLACE EXISTING

If a database with the same alias as the target database alias already exists, this parameter tells the restore utility to replace the existing database with the restored database. This is useful in scripts containing the RESTORE DATABASE command, because the CLP will not prompt the user to verify deletion of the existing database. If the WITHOUT PROMPTING parameter is specified, it is not necessary to specify REPLACE EXISTING, but in this case the command will fail if events occur that normally require user intervention.

REDIRECT

Specifies a redirected restore. To complete a redirected restore, this command should be followed by one or more SET TABLESPACE CONTAINERS commands, and then by a RESTORE DATABASE command with the CONTINUE option.

Note:

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

WITHOUT ROLLING FORWARD

Specifies not to place the database in roll-forward pending state after it has been successfully restored.

If, following a successful restore, the database is in roll-forward pending state, ROLLFORWARD DATABASE must be executed before the database can be used.

WITHOUT DATALINK

Specifies that any tables with DATALINK columns be placed in DataLink_Reconcile_Pending (DRP) state, and that no reconciliation of linked files is to be performed.

PARALLELISM n

Specifies the number of buffer manipulators to be spawned during the restore process. The default value is 1.

WITHOUT PROMPTING

Specifies that the restore will run unattended, and that any actions which normally require user intervention will instead return an error message.

Examples

Following is a typical redirected restore scenario for a database whose alias is MYDB:

Issue a RESTORE DATABASE command with the REDIRECT option.
```
   db2 restore db mydb replace existing redirect
```
After successful completion of step 1, and before completing step 3, the restore can be aborted by issuing:
```
   db2 restore db mydb abort
```
Issue a SET TABLESPACE CONTAINERS command for each table space whose containers must be redefined. For example, on OS/2:
```
   db2 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.
After successful completion of steps 1 and 2, issue:
```
   db2 restore db mydb continue
```
This is the final step of the redirected restore.
If step 3 fails, or if the restore has been aborted, the redirected restore can be restarted, beginning at step 1.

Usage Notes

Database Level Restore

If restoring to an existing database, the current database configuration file is not replaced by the backup copy unless the configuration file is corrupt.

If WITHOUT ROLLING FORWARD is not specified, and the database was enabled for roll-forward recovery at the time it was backed up, the database is in roll-forward pending state after it has been successfully restored. Use GET DATABASE CONFIGURATION to check the database state. If the database is in roll-forward pending state, ROLLFORWARD DATABASE must be issued against the database before it can be used.

BACKUP and RESTORE can also be used to copy a database to another file system or node.

If the backup file being restored was created during an online backup, it is imperative that forward recovery be invoked at the completion of the restore. Forward recovery (using ROLLFORWARD DATABASE) will ensure that any changes which occurred during the course of the backup operation are captured to bring the database into a stable state.

For offline restore, this utility connects to the database in exclusive mode. The utility fails if any application, including the calling application, is already connected to the database that is being restored to.

If an interrupt occurs during a restore, it will not be possible to successfully connect to the database until a successful restore has been performed.

The backup image must be an image that was created by the BACKUP DATABASE command, and may reside on disk, diskette (on OS/2 or the Windows operating system), tape, at the ADSM utility, or on other vendor product-managed media. Tape is not supported on OS/2.

When a database backup is restored to an existing database, the database inherits the alias and database names of the existing database. When restoring to a nonexistent database, the new database will be created with an alias and database name specified by the target-database-alias parameter. If a target database alias is not specified, the database will inherit the alias and database name of the backed up database.

Although a remote client may initiate a restore, the source and target always refer to entities that exist at the server.

Restoring databases may have prerequisite requirements and restrictions that are beyond the scope of this manual. For more detailed information about these conditions, see the Administration Guide.

Table Space Level Restore

To ensure that restored table spaces are synchronized with the rest of the database, the table spaces must be rolled forward to the end of the log (or to the point where the table spaces were last used). For this reason, table space level backup and restore can only be performed if roll-forward recovery is enabled. If roll-forward recovery is disabled at any time after a table space level backup is executed, it will not be possible to restore from the backup, and then to roll the table space forward to the current point in time. In this case, all table space level backups taken prior to that time are no longer restorable. The restore operation will fail if the user tries to restore from such a backup. In cases where it cannot be determined that the backup is invalid (if, for instance, the database has been restored and rolled forward, thus creating a new log sequence), the restore may be successful, and the broken restore set will be detected during roll-forward recovery.

Each component of a table may be backed up and restored with the table space in which it resides, independently of the other components of the table.

Table space level backup and restore cannot be run concurrently.

DB2 Data Links Manager Considerations

When restoring to a database name or alias different from that of the backup image, tables with DATALINK columns are put in DRNP state.

If the WITHOUT DATALINK option is not specified, and the DB2 File Manager containing the DATALINK data is unavailable, the restore operation will fail. If this option is specified, and the DB2 File Manager containing the DATALINK data is unavailable, all table spaces which contain tables with DATALINK values on the unavailable server are placed in restore pending state.

For detailed information about DB2 Data Links Manager and database recovery, see the Administration Guide.

See Also

db2move - Database Movement Tool

BACKUP DATABASE

GET DATABASE CONFIGURATION

MIGRATE DATABASE

ROLLFORWARD DATABASE.

[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]

[ DB2 List of Books | Search the DB2 Books ]