DB2 10.5 for Linux, UNIX, and Windows

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. The restore utility 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 get an error. If other users need access to the backup image, you need to change the file permissions the backup is generated.
  • You cannot start a database restore operation 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 you are restoring a backup image with one code page into a system with a different codepage, the system code page is 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 you specify the TRANSPORT option:
    • If the backup image can be restored by a restore operation, and is supported for upgrades, then it can be transported.
    • If you are using an online backup, then both source and target data servers must be running the same DB2 version.
    • Issue the RESTORE DATABASE command against the target database. If the remote client is of the same platform as the server, then you can execute the schema transport 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 does 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 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. You cannot use it 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 you use an online backup image and don't include the active logs, then the transport operation fails.
    • If you use 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, you must reissue the entire restore operation. All failures that occur are logged in the db2diag.log file on the target server and should be reviewed before you reissue the RESTORE DATABASE 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 reissuing the RESTORE DATABASE 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 was updated. The target storage group during the table space restore is the storage group the table space is currently associated with when RESTORE DATABASE is executed.
  • You cannot perform a point-in-time recovery to an earlier storage group association.
  • Restoring a database that contains encrypted data in a DB2 instance that does not support encryption (where the IBM Global Security Kit is not installed) is supported. The encrypted data is included in the restored database, but you cannot access the encrypted data. To access the encrypted data, upgrade or restore the database to an instance that supports encryption.

Procedure

To invoke the restore utility:

Example

Following is an example of the RESTORE DATABASE command issued through the CLP:
db2 restore db sample from D:\DB2Backups taken at 20010320122644
Related concepts:
Optimizing restore performance
Performing a redirected restore operation
Privileges, authorities, and authorization required to use restore
Rebuild sessions - CLP examples
Redirected restore sessions - CLP examples
Related tasks:
Monitoring the progress of restore operations
Related reference:
db2Restore API - Restore a database or table space
RESTORE DATABASE command
Related information:
IBM Data Studio: Administering databases with task assistants