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.

Since automatic incremental restore operations are dependent on the database history, the restore steps change slightly based on whether or not 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 will replace any database history that already exists for database TEST if:

• the database TEST does not exist when the RESTORE DATABASE command is issued, or
• 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 will create the TEST database and populate 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 using 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 will act 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.