DB2 Version 10.1 for Linux, UNIX, and Windows

restore utilityrestrictionsdb2Restore APIrecovering dataRESTORE DATABASE commandrestoring data

Using restore

Use the RESTORE DATABASE command to recover a database or table space after a problem such as media or storage failure, or application failure. If you have backed up your database, or individual table spaces, you can re-create them if they have become damaged or corrupted in some way.

Before you begin

When restoring to an existing database, you should not be connected to the database that is to be restored: the restore utility automatically establishes a connection to the specified database, and this connection is terminated at the completion of the restore operation. When restoring to a new database, an instance attachment is required to create the database. When restoring to a new remote database, you must first attach to the instance where you want the new database to reside. Then, create the new database, specifying the code page and the territory of the server. Restore overwrites the code page of the destination database with the code page of the backup image.

About this task

The database can be local or remote.

The following restrictions apply to the restore utility:

During the RESTORE, if the database uses automatic storage, then the containers that are used on the automatic storage paths might be rebalanced. The rebalancing can happen when restoring a new or previously dropped database. Rebalancing can also happen if there are configuration changes to the free space or the file system. For rebalancing conditions, see Rebalancing during RESTORE of automatic storage database.
You can only use the restore utility if the database has been previously backed up using the DB2® backup utility.
If users other than the instance owner (on UNIX), or members of the DB2ADMNS or Administrators group (on Windows) attempt to restore a backup image, they will get an error (SQL2061N). If other users need access to the backup image, the file permissions need to be changed after the backup is generated.
A database restore operation cannot be started while the rollforward process is running.
If you do not specify the TRANSPORT option, then you can restore a table space into an existing database only if the table space currently exists, and if it is the same table space. In this situation, "same" means that the table space was not dropped and then re-created between the backup and the restore operation. The database on disk and in the backup image must be the same.
You cannot issue a table space-level restore of a table space-level backup to a new database.
You cannot perform an online table space-level restore operation involving the system catalog tables.
You cannot restore a backup taken in a single database partition environment into an existing partitioned database environment. Instead you must restore the backup to a single database partition environment and then add database partitions as required.
When restoring a backup image with one code page into a system with a different codepage, the system code page will be overwritten by the code page of the backup image.
You cannot use the RESTORE DATABASE command to convert nonautomatic storage enabled table spaces to automatic storage enabled table space.
You must verify that the server time was not reset or changed, and that the times associated with the members in multi-partitioned database environments are in sync. If the server time is not verified, the upgrade might return a SQL0440N error message.
In order to change the database directory during restore, the database can not already exist.
The following restrictions apply when the TRANSPORT option is specified:
- If the backup image can be restored by a restore operation, and is supported for upgrades, then it can be transported.
- If an online backup is used, then both source and target data servers must be running the same DB2 version.
- The RESTORE DATABASE command must be issued against the target database. If the remote client is of the same platform as the server, then schema transport can be executed locally on the server or through remote instance attachment. If a target database is a remote database cataloged in the instance where transport runs locally, then schema transport against that remote target database is not supported.
- You can only transport tables spaces and schemas into an existing database. The transport operation will not create a new database. To restore a database into a new database, you can use the RESTORE DATABASE command without specifying the TRANSPORT option.
- If the schemas in the source database are protected by any DB2 security settings or authorizations, then the transported schemas in the target database will retain these same settings or authorizations.
- The TRANSPORT option is not supported in the DB2 pureScale® environment, or in partitioned database environments.
- If any of the tables within the schema contains an XML column, the transport fails.
- The TRANSPORT option is incompatible with the REBUILD option.
- The TRANSPORT option is not supported for restore from a snapshot backup image.
- The staging database is created for transport. It cannot be used for other operations.
- The database configuration parameters on the staging table and the target table need to be the same, or the transport operation fails with an incompatibility error.
- The auto_reval configuration parameter must be set to deferred_force on the target database to transport objects listed as invalid. Otherwise, the transport fails.
- If an online backup image is used, and the active logs are not included, then the transport operation fails.
- If an online backup is used, then the backup image must have been created with the INCLUDE LOGS option.
- If the backup image is from a previous release, it must be a full offline database level backup image.
- If an error occurs on either the staging or target database, the entire restore operation must be reissued. All failures that occur are logged in the db2diag log file on the target server and should be reviewed before reissuing the RESTORE command.
- If the transport client fails, then the staging database might not be properly cleaned up. In this case, you need to drop the staging database. Before re-issuing the RESTORE command, drop all staging databases to prevent containers of staging database from blocking subsequent transport.
- Concurrent transport running against the same target database is not supported.
- Generating a redirected restore script is not supported with table space transport.
You can restore a table space if the storage group has been updated. The target storage group during the table space restore is the storage group the table space is currently associated with when RESTORE is executed.
You cannot perform a point-in-time recovery to an earlier storage group association.

Procedure

To invoke the restore utility:

Issue the RESTORE DATABASE command.
Call the db2Restore application programming interface (API).
Open the task assistant in IBM® Data Studio for the RESTORE DATABASE command.

Example

Following is an example of the RESTORE DATABASE command issued through the CLP:

db2 restore db sample from D:\DB2Backups taken at 20010320122644