Db2 Incremental Restore steps and commands

This is one of the important database tutorial that explains how to recover / restore data from the incremental backup . Take full , incremental and incremental delta backups before doing incremental restore. Please go through my earlier post How to enable and take incremental , delta backup ? . The following commands are used to take backup offline (full , incremental , delta)

   
1. db2 backup db employee to d:\db2onlinebkp  .  The timestamp for this backup image is : 20120124001028
2. db2 backup db employee incremental to d:\db2onlinebkp .  The timestamp for this backup image is : 20120124001347
3. db2 backup db employee incremental delta to d:\b2onlinebkp. The timestamp for this backup image is : 20120124003554
4. db2 backup db employee incremental to d:\db2onlinebkp.   The timestamp for this backup image is : 20120124004124 

Now you have the following backups

1. Full Backup : Timestamp 20120124001028

2. Incremental backup : Timestamp 20120124001347

3. Incremental Delta backup : Timestamp 20120124003554

4. Incremental backup : Timestamp 20120124004124

Suppose your database is corrupted after the last backup Timestamp 20120124004124. Now you have to recover your data by using the available backups. You may be confused that what backup images are to be restored in which order. When restoring from incremental backups , you have to apply the right sequence of full , incremental and incremental delta backups . This may be tough in real time environment. For this reason , Db2 provides you two ways to restore incremental backup images.

1. Automatic : When you issue restore command with the option automatic , DB2 uses the database backup history to figure out the right sequence for applying backup images and restores them. The RESTORE command needs to be issued only once . Automatic option is recommended

2. Manual : Decide the right sequence of the backup images that needs to restored and issue restore command once for each image

Before doing Automatic or Manual incremental restore , let us the see command db2ckrst (Incremental Restore Image Sequence Command) . This utility is used to see what backup images will be applied in which order, prior to the automatic / manual restore. It generates a list of restore syntax with timestamps for the backup images that are required for an incremental restore .

Syntax : db2ckrst -d dbname -t -r database or tablespace tbs_name

where Timestamp is the last timestamp to be restored

Example :

For database : db2ckrst -d employee -t 20120124004124 -r database
For tablespace of a database - db2ckrst -d employee -t 20120124004124 -r tablespace emptbs

Suggested restore order of images using timestamp 20120124004124 for database employee.

====================================================================

restore db employee incremental taken at 20120124004124
restore db employee incremental taken at 20120124001028
restore db employee incremental taken at 20120124004124

====================================================================

The output shows that restore command with last icremental backup image needs to be run first to read the control and header information only . Then the database will be restored from the full backup . Lastly , the incremental backup will be read again to apply the data in the image. No need to run restore command for incremental backup with timestamp 20120124001347 . Because the last incremental backup cotains database data that has changed since the last, successful, full backup operation.

Automatic Incremental Restore command

To restore a set of incremental backup images using automatic incremental restore, specify the TAKEN AT timestamp option on the RESTORE DATABASE command. Use the time stamp for the last image that you want to restore. For example , you have the backup image with timestamp 20120124004124 is the final backup (incremental)

db2 restore db employee incremental automatic from d:\db2onlinebkp taken at 20120124004124

when you run the above command , you will get the following message ...

SQL2539W Warning! Restoring to an existing database that is the same as the backup image database. The database files will be deleted.

Do you want to continue ? (y/n) y

DB20000I The RESTORE DATABASE command completed successfully.

For restoring tablespace (emptbs) backup image in online

db2 "restore db employee tablespace(emptbs) online incremental automatic from d:\db2onlinebkp taken at 20120124004124"
db2 "rollforward db employee to end of logs tablespace(emptbs) online"

Manual Incremental Restore Steps with example

1. Decide the right sequence of the backup images that needs to restored .

2. Identify the last final backup image to be restored, and issue incremental restore command with the timestamp of the last backup image. This image is known as the target image of the incremental restore, because it will be the last image to be restored.

3. Restore last full database or table space image to establish a baseline against which each of the subsequent incremental backup images can be applied.

4. Restore each of the incremental backup images, in the order in which they were produced, on top of the baseline image restored .

You can use the utility to decide the sequence order .

db2ckrst -d employee -t 20120124003554 -r database

4. Repeat Step 4 until the target image from Step 2 is read second time. The target image is accessed twice . First time , the control and header information is read from the target image . Second time data of the image is read and applied. The target image of the incremental restore operation must be read two times to ensure that the database is initially configured with the correct history, database configuration, and table space definitions for the database that will be created during the restore operation.

Suppose , backup image with timestamp 20120124003554 is the final backup (Incremental delta). The following commands needs to be run.

   
 restore db employee incremental taken at 20120124003554   - Ist time  (incremental delta)
 restore db employee incremental taken at 20120124001028   - full
 restore db employee incremental taken at 20120124001347   - incremental 
 restore db employee incremental taken at 20120124003554   - IInd time (incremental delta)

Note : For an automatic incremental restore, the RESTORE command is issued only once . DB2 then uses the db history to determine the remaining required backup images and restores them. For a manual incremental restore, the RESTORE command is issued once for each backup image .