How to restore DB2 database by using DB2 RESTORE command?

By | November 27, 2015

Restore command to rescover db2 database

RESTORE DATABASE comes handy when you are not able to backup your corrupted or damaged DB2 database by using inbuilt DB2 backup utility. If you use the restore command then it will recover the database in the same state as it was when you created the backup. However, this utility may also backup copy to new database or overwrite the different copy.

One can also use the Restore command to restore backup copy that was created on DB2 Universal Database version 8. If a migration is needed, it will be called at the end of the restore process. However, if the database was enabled for Rollforward Recovery during the time of creating backup, then the database can be brought back to its earlier version by applying the rollforward command but only after a successful restore process. This inbuilt command can also recover table space level backup.

But, if there is a difference operating system or word size (32-bit or 64-bit). Then “delta image” cannot be restored with this command. Even after a successful restore process from one environment to different environment, no backup (incremental or delta) is permitted until and unless non-incremental backup is created. Also there are some considerations such as external libraries must be re-created on the new platform, packages should be rebound before use and SQL operation should be dropped and rebuilt. Note – If you are operating the restore command on same environments these are not considerations.)

You must also keep in mind that a restore process performed on an existing database and existing containers must use the same table space map and containers.

Note – This Restore command only affects the node on which it is implemented.

Connection Needed

The connection needed will vary on the type of restore performed:

If you are performing the restore to an existing database then you will need the database connection. This command itself generates the exclusive connection to the particular database. To perform restore of existing database one of following authorization is needed:

  • sysadm
  • sysctrl
  • sysmaint

If you are performing the restore to a new database, then you will need database and instance connection. The instance connection is needed to make the database. To perform restore to a new database one of the following authorization is needed:

  • sysadm
  • sysctrl

If you are performing the snapshot restore then again you will need both database and instance connection.

Note: If a username is defined, then particular user will need the CONNECT authority on the database.

Steps to use Restore Command

  1. First of all close all application and server connected to database.
  2. Now conclude which backup copy you need to restore by looking at the available backup files. Also consider timestamp of the available backup files.
  3. Now apply the RESTORE DATABASE command. This example refers to the WPSDB database, the default name for the Release database domain. RESTORE DATABASE WPSDB FROM backup_directory_path TAKEN AT timestamp REPLACE EXISTING
  4. Roll the restored database forward by selecting the same point in time for the database.

Reference – https://www-01.ibm.com/support/knowledgecenter/SSYJ99_8.5.0/admin-system/i_wadm_t_restr_db2_cmnd.dita

What if there is no backup or Restore Command fails?

In this case you can use third party tool popularly known as DB2 File Repair tool to recover and restore your corrupted DB2 database. It is very powerful tool that is particularly designed for repairing and recovering all objects and data elements of DB2 database. Free download is available you can just install and check its reliability and features.

downloadnow buynow learn-more-button-orange

Steps to repair and restore DB2 database

Step 1:  Download, Install and Run DB2 File Repair software. It will provides two options to repair db2 file, first option is for if the database is attached to DB2, then you can select the option ‘Repair the database stored at default location‘, and if the database is not attached to DB2 then choose the manual selection of database.

1 (3)

Step 2: It will provide you the list of database attached to DB2. Select the particular database you want to repair and recover corresponding database objects.

2 (2)

Step 3: In this step this tool provide the list of recoverable database files. You can see the preview by clicking on it.

3 (2)

Step 4: Give the path location for the recoverable database and its object to be saved. Click on ‘Ok’ button.

4 (2)