DB2 10.5 for Linux, UNIX, and Windows

Using incremental restore in a test and production environment

Once a production database is enabled for incremental backup and recovery, you can use an incremental or delta backup image to create or refresh a test database. You can do this by using either manual or automatic incremental restore.

To restore the backup image from the production database to the test database, use the INTO target-database-alias option on the RESTORE DATABASE command. For example, in a production database with the following backup images:

   backup db prod
   Backup successful. The timestamp for this backup image is : ts1

   backup db prod incremental
   Backup successful. The timestamp for this backup image is : ts2

an example of a manual incremental restore would be:

   restore db prod incremental taken at ts2 into test without 
   prompting
   DB20000I  The RESTORE DATABASE command completed successfully.

   restore db prod incremental taken at ts1 into test without 
   prompting
   DB20000I  The RESTORE DATABASE command completed successfully.

   restore db prod incremental taken at ts2 into test without 
   prompting
   DB20000I  The RESTORE DATABASE command completed successfully.

If the database TEST already exists, the restore operation overwrites any data that is already there. If the database TEST does not exist, the restore utility creates it and then populates it with the data from the backup images.

Since automatic incremental restore operations are dependent on the database history, the restore steps change slightly based on whether the test database exists. To perform an automatic incremental restore to the database TEST, its history must contain the backup image history for database PROD. The database history for the backup image replaces any database history that already exists for database TEST if either of the following are true:

The database TEST does not exist when the RESTORE DATABASE command is issued.
The database TEST exists when the RESTORE DATABASE command is issued, and the database TEST history contains no records.

The following example shows an automatic incremental restore to database TEST which does not exist:

   restore db prod incremental automatic taken at ts2 into test without 
   prompting
   DB20000I  The RESTORE DATABASE command completed successfully.

The restore utility creates the TEST database and populates it.

If the database TEST does exist and the database history is not empty, you must drop the database before the automatic incremental restore operation as follows:

   drop db test
   DB20000I  The DROP DATABASE command completed successfully.

   restore db prod incremental automatic taken at ts2 into test without 
   prompting
   DB20000I  The RESTORE DATABASE command completed successfully.

If you do not want to drop the database, you can issue the PRUNE HISTORY command with a timestamp far into the future and the WITH FORCE OPTION parameter before issuing the RESTORE DATABASE command:

   connect to test
   Database Connection Information
   
   Database server            = server_id
   SQL authorization ID       = id
   Local database alias       = TEST

   prune history 9999 with force option
   DB20000I  The PRUNE command completed successfully.

   connect reset
   DB20000I  The SQL command completed successfully.
   restore db prod incremental automatic taken at ts2 into test without 
   prompting
   SQL2540W  Restore is successful, however a warning "2539" was 
   encountered during Database Restore while processing in No 
   Interrupt mode.

In this case, the RESTORE DATABASE command acts in the same manner as when the database TEST did not exist.

If the database TEST does exist and the database history is empty, you do not have to drop the database TEST before the automatic incremental restore operation:

   restore db prod incremental automatic taken at ts2 into test without 
   prompting
   SQL2540W  Restore is successful, however a warning "2539" was 
   encountered during Database Restore while processing in No 
   Interrupt mode.

You can continue taking incremental or delta backups of the test database without first taking a full database backup. However, if you ever need to restore one of the incremental or delta images you have to perform a manual incremental restore. This requirement is because automatic incremental restore operations require that each of the backup images restored during an automatic incremental restore are created from the same database alias.

If you make a full database backup of the test database after you complete the restore operation using the production backup image, you can take incremental or delta backups and can restore them using either manual or automatic mode.