Restore Overview

The simplest form of the RESTORE DATABASE command requires only that you specify the alias name of the database that you want to restore.

For example:

  db2 restore db sample  

NOTE

This will restore the image from the path the restore command is run from.

In this example, because the SAMPLE database exists, the following message is returned:

[View full width]

 
 [View full width] 
  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)  

If you enter Y, and a backup image for the SAMPLE database exists in the directory, the restore operation will be performed.

A database restore operation requires an exclusive connection: That is, no applications can be running against the database when the operation starts, and the restore utility prevents other applications from accessing the database until the restore operation completes successfully. A table space restore operation, however, can be done online.

A table space is not usable until the restore operation (followed by rollforward recovery) completes successfully.

If you have tables that span more than one table space, you should back up and restore the set of table spaces together.

When doing a partial or subset restore operation, you can use either a table space level backup image or a full database-level backup image and choose one or more table spaces from that image. All the log files associated with these table spaces from the time that the backup image was created must exist and will be rolled forward.

Authorities Required to Use Restore

Privileges enable users to create or access database resources. Authority levels provide a method of grouping privileges and higher level database manager maintenance and utility operations. Together, these act to control access to the database manager and its database objects. Users can access only those objects for which they have the appropriate authorization; that is, the required privilege or authority.

You must have SYSADM, SYSCTRL, or SYSMAINT authority to restore to an existing database from a full database backup. To restore to a new database, you must have SYSADM or SYSCTRL authority.

Using Restore

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 the new database will reside. If the target database does not exist, it will be created using the INTO target-database-alias clause.

The following restrictions apply to the restore utility:

• You can use the restore utility only if the database has been previously backed up using the DB2 backup utility.

• A database restore operation cannot be started while the rollforward process is running.

• You can restore a table space only if the table space currently exists, and if it is the same table space. For example, the table space was not dropped and then recreated between the backup and the restore operation.

• You cannot restore a table space level backup to a new database.

• You cannot perform an online table space level restore operation involving the system catalog tables.

The restore utility can be invoked through the CLP, the database Restore Wizard in the Control Center, or the db2Restore API.

Following is an example of the RESTORE DATABASE command issued through the CLP:

  db2 restore db sample from D:\DB2Backups taken at 20020829152626  

To open the database Restore Wizard:

1. From the Control Center, expand the object tree until you find the Databases folder.

2. Click on the Databases folder. Any existing databases are displayed in the pane on the right side of the window.

3. Click the right mouse button on the database you want in the contents pane, and select Restore Wizard from the pop-up menu. The database Restore Wizard opens.

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:

[View full width]

 
 [View full width] 
  db2 backup db proddb   Backup successful. The timestamp for this backup image is : 20020829021740   The backup will fail if the TRACKMOD is not set:   db2 backup db proddb incremental   SQL2426N  The database has not been configured to allow the incremental backup operation.  Reason code = "1".   Now, the TRACKMOD is set to ON, the database backup complete successfully   db2 update db cfg for proddb using trackmod on   DB20000I  The UPDATE DATABASE CONFIGURATION command completed successfully.   db2 backup db proddb   Backup successful. The timestamp for this backup image is : 20020829021853   db2 backup db proddb incremental   Backup successful. The timestamp for this backup image is : 20020829022152   db2ckrst -d proddb -t 20020829022152 -r database   Suggested restore order of images using timestamp 20020829022152 for database proddb   ==========================================================================   restore db proddb incremental taken at 20020829022152   restore db proddb incremental taken at 20020829021853   restore db proddb incremental taken at 20020829022152   ==========================================================================  

An example of a manual incremental restore would be:

  restore db proddb incremental taken at 20020829022152 into testdb without prompting   DB20000I The RESTORE DATABASE command completed successfully.   restore db proddb incremental taken at 20020829021853 into testdb without prompting   DB20000I The RESTORE DATABASE command completed successfully.   restore db proddb incremental taken at 20020829022152 into testdb without prompting   DB20000I  The RESTORE DATABASE command completed successfully.  

If the database TESTDB already exists, the restore operation will overwrite any data that is already there. If the database TESTDB does not exist, the restore utility will create it, then will populate it with the data from the backup images.

Because 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 TESTDB, its history must contain the backup image history for database PRODDB.

The database history for the backup image will replace any database history that already exists for database TESTDB if:

• the database TESTDB does not exist when the RESTORE DATABASE command is issued

or

• the database TESTDB exists when the RESTORE DATABASE command is issued, and the database TESTDB history contains no records.

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

  restore db proddb incremental automatic taken at 20020829021853   into testdb without prompting   DB20000I The RESTORE DATABASE command completed successfully.  

The restore utility will create the TESTDB database and populate it.

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

  drop db testdb   DB20000I The DROP DATABASE command completed successfully.   restore db proddb incremental automatic taken at 20020829021853   into testdb 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:

[View full width]

 
 [View full width] 
  connect to testdb   Database Connection Information   Database server = DB2/6000 8.1.0   SQL authorization ID = V8INST   Local database alias = TESTDB   prune history 9999 with force option   DB20000I The PRUNE command completed successfully.   connect reset   DB20000I The SQL command completed successfully.   restore db proddb incremental automatic taken at 20020829022152   into testdb without prompting   SQL2540W Restore is successful, however a warning "2528" 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 TESTDB did not exist.

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 will have to perform a manual incremental restore. This is because automatic incremental restore operations require that each of the backup images restored during an automatic incremental restore be 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.

Redefining Table Space Containers During a Restore Operation (Redirected Restore)

During a database backup operation, a record is kept of all the table space containers associated with the table spaces that are being backed up. During a restore operation, all containers listed in the backup image are checked to determine if they exist and whether they are accessible. If one or more of these containers is inaccessible because of media failure (or for any other reason), the restore operation will fail. A successful restore operation in this case requires redirection to different containers. DB2 supports adding, changing, or removing table space containers.

You can redefine table space containers by invoking the RESTORE DATABASE command and specifying the REDIRECT parameter or by using the Containers page of the database Restore Wizard in the Control Center.

The following example using the redirected restore from the table space ID 3 containers /db00/ts1/c0 and /db00/ts1/c1 to /db01/ts1/con00 and /db01/ts1/con01 of 2,000 4-KB pages per container. The database SAMPLE is defined on all two partitions, numbered 0 through 1. A table space TS1 is created on two containers /db00/ts1/c0 and /db00/ts1/c1 of 1,000 4-KB pages per container:

[View full width]

 
 [View full width] 
  1. Connect to the database   db2 connect to sample   Database Connection Information   Database server                      = DB2/6000 8.1.0   SQL authorization ID                 = V8INST   Local database alias                 = SAMPLE   2. Create tablespace ts1 with two containers, 1,000 4-KB pages each container   db2 "create tablespace ts1 managed by database using   (file '/db00/ts1/c $N' 1000)"   DB20000I  The SQL command completed successfully.   db2 terminate   DB20000I  The TERMINATE command completed successfully.   3. Backup sample database (catalog partition first, then the remaining database partition)   db2_all '<<+0< db2 backup db sample to /data/dbbackup'   Backup successful. The timestamp for this backup image is: 20030108231322   phantom: db2 backup database ... completed ok   db2_all '<<-0< db2 backup db sample to /data/dbbackup'   rah: omitting logical node 0   Backup successful. The timestamp for this backup image is: 20030108231410   phantom: db2 backup database ... completed ok   4. Show the database backup images   ls /data/dbbackup/   SAMPLE.0.v8inst.NODE0000.CATN0000.20030108231322.001   SAMPLE.0.v8inst.NODE0001.CATN0000.20030108231410.001   5. Show the containers for tablespace ID 3   db2_all "db2 connect to sample; db2 list tablespace containers for 3 show detail" egrep  i "nametotal"   Name               = /db00/ts1/c0   Total pages        = 1000   Name               = /db00/ts1/c1   Total pages        = 1000   6. Restore from the last database backup images using redirected restore. Replace existing  containers for tablespace ts1 to new containers /db01/ts1/con00 and /db01/ts1/con01 with 2  ,000 4-KB pages per container.   At the catalog partition:   export DB2NODE=0   db2 terminate   DB20000I  The TERMINATE command completed successfully.   db2 restore db sample taken at 20030108231322 replace existing redirect   SQL2539W  Warning! Restoring to an existing database that is the same as the backup image  database. The database files will be deleted.   SQL1277N  Restore has detected that one or more table space containers are inaccessible,  or has set their state to 'storage must be defined'.   DB20000I  The RESTORE DATABASE command completed successfully.   db2 "set tablespace containers for 3   using (file '/db01/ts/con00' 2000)"   DB20000I  The SET TABLESPACE CONTAINERS command completed successfully.   At the non-catalog partition (database partition number 1)   export DB2NODE=1   db2 terminate   DB20000I  The TERMINATE command completed successfully.   db2 restore db sample taken at 20030108231410 replace existing redirect   SQL2539W  Warning! Restoring to an existing database that is the same as the backup image  database. The database files will be deleted.   SQL1277N  Restore has detected that one or more table space containers are inaccessible,  or has set their state to 'storage must be defined'.   DB20000I  The RESTORE DATABASE command completed successfully.   db2 "set tablespace containers for 3 using (file '/db01/ts/con01' 2000)"   DB20000I  The SET TABLESPACE CONTAINERS command completed successfully.   7. Continue restore   At the catalog partition:   db2 restore db testdb continue   DB20000I  The RESTORE DATABASE command completed successfully.   At the non-catalog partition (database partition number 1):   db2 restore db testdb continue   DB20000I  The RESTORE DATABASE command completed successfully.   8. Verify the tablespace ts1   db2_all "db2 connect to sample; db2 list tablespace containers for 3 show detail" egrep  i "nametotal"   Name               = /db01/ts1/con00   Total pages        = 2000   Name               = /db01/ts1/con01   Total pages        = 2000  

The process for invoking a redirected restore of an incremental backup image is similar to the process for a non-incremental backup image: Call the RESTORE DATABASE command with the REDIRECT parameter and specify the backup image from which the database should be incrementally restored. During a redirected restore operation, directory and file containers are automatically created if they do not already exist. The database manager does not automatically create device containers.

Container redirection provides considerable flexibility for managing table space containers. For example, even though adding containers to SMS table spaces is not supported, you could accomplish this by specifying additional containers when invoking a redirected restore operation.

Restoring to an Existing Database

You can restore a full database backup image to an existing database. The backup image may differ from the existing database in its alias name, its database name, or its database seed.

A database seed is a unique identifier for a database that does not change during the life of the database. The seed is assigned by the database manager when the database is created. DB2 always uses the seed from the backup image.

When restoring to an existing database, the restore utility:

• Deletes table, index, and long field data from the existing database and replaces it with data from the backup image.

• Replaces table entries for each table space being restored.

• Retains the current recovery history file, unless it is damaged or has no entries. If the recovery history file is damaged, the database manager copies the file from the backup image.

• Retains the authentication type for the existing database.

• Retains the database directories for the existing database. The directories define where the database resides and how it is cataloged.

• Compares the database seeds. If the seeds are different:

  • Deletes the logs associated with the existing database.

  • Copies the database configuration file from the backup image.

  • Specifies the NEWLOGPATH if the log path in the backup image is not suitable to be used after the restore (i.e., the path is no longer valid or is currently used by a different database).

• If the database seeds are the same:

  • Deletes the logs if the image is for a non-recoverable database.

  • Retains the current database configuration file, unless the file has been corrupted, in which case, the file is copied from the backup image.

  • Specifies the NEWLOGPATH if the log path in the backup image is not suitable to be used after the restore (i.e., the path is no longer valid or is currently used by a different database).

Restoring to a New Database

You can create a new database, then restore a full database backup image to it. If you do not create a new database, the restore utility will create one.

When restoring to a new database, the restore utility:

• Creates a new database, using the database alias name that was specified through the target database alias parameter. If a target database alias was not specified, the restore utility creates the database with an alias that is the same as that specified through the source database alias parameter.

• Restores the database configuration file from the backup image.

• Specifies the NEWLOGPATH if the log path in the backup image is not suitable to be used after the restore (i.e., the path is no longer valid or is currently used by different a database).

• Restores the authentication type from the backup image.

• Restores the comments from the database directories in the backup image.

• Restores the recovery history file for the database.

Restore Database: Examples

The RESTORE DATABASE utility rebuilds a damaged or corrupted database that has been backed up using the DB2 backup utility. The restored database is in the same state it was in when the backup copy was made. This utility can also restore to a database with a name different from the database name in the backup image (in addition to being able to restore to a new database).

This utility can also be used to restore offline backup images, which were produced by the previous two versions of DB2. If a migration is required, it will be invoked automatically at the end of the restore operation. If, at the time of the backup operation, the database was enabled for rollforward recovery, the database can be brought to the state it was in prior to the occurrence of the damage or corruption by invoking the rollforward utility after successful completion of a restore operation.

This utility can also restore from a table space level backup.

When working in an environment that has more than one operating system, you must consider that you cannot back up a database on one operation system, then restore that database on another operating system (such as UNIX to/from Linux). In this case, you can use the db2move utility. There is more cross-operating system backup/restore allowedany UNIX to any UNIX, Linux to Linux, and Windows to Windows.

In the following example, the database SAMPLE is defined on all four database partitions, numbered 0 through 3 (catalog partition is 0). The path /data/dbbackup is accessible from all database partitions. The following offline backup images are available from /data/dbbackup:

  cd /data/dbbackup   ls   SAMPLE.0.v8inst.NODE0000.CATN0000.20020829013314.001   SAMPLE.0.v8inst.NODE0001.CATN0000.20020829013401.001   SAMPLE.0.v8inst.NODE0002.CATN0000.20020829013012.001   SAMPLE.0.v8inst.NODE0003.CATN0000.20020829013028.001  

To restore the catalog partition first, then all other database partitions of the NEWDB database from the /data/dbbackup directory (one at a time), issue the following commands from one of the database partitions:

[View full width]

 
 [View full width] 
  db2_all '<<+0< db2 restore db sample from /data/dbbackup taken at 20020829013314 into  newdb replace existing'   db2_all '<<+1< db2 restore db sample from /data/dbbackup taken at 20020829013401 into  newdb replace existing'   db2_all '<<+2< db2 restore db sample from /data/dbbackup taken at 20020829013012 into  newdb replace existing'   db2_all '<<+3< db2 restore db sample from /data/dbbackup taken at 20020829013028 into  newdb replace existing'  

NOTE

The db2_all utility issues the restore command to each specified database partition.

The following example using redirected restore from the tablespace id 3 container ts1.dat to newts:

[View full width]

 
 [View full width] 
  db2 connect to testdb   Database Connection Information   Database server             = DB2/6000 8.1.0   SQL authorization ID        = V8INST   Local database alias        = TESTDB   db2 "create tablespace ts1 managed by database   using (file '/data/ts/ts1.dat' 1024)"   DB20000I  The SQL command completed successfully.   db2 list tablespace containers for 3 show detail  grep ts1   Name                        = /data/ts/ts1.dat   db2 backup database testdb   Backup successful. The timestamp for this backup image is : 20020829125126   db2 restore db testdb taken at 20020829125126 into newdb replace existing redirect   SQL1277N  Restore has detected that one or more table space containers are inaccessible,  or has set their state to 'storage must be defined'.   DB20000I  The RESTORE DATABASE command completed successfully.   db2 "set tablespace containers for 3   using (file '/data/ts/newts' 1024)"   DB20000I  The SET TABLESPACE CONTAINERS command completed successfully.   db2 restore db testdb continue   DB20000I  The RESTORE DATABASE command completed successfully.   db2 connect to newdb   Database Connection Information   Database server             = DB2/6000 8.1.0   SQL authorization ID        = V8INST   Local database alias        = NEWDB   db2 list tablespace containers for 3 show detail  grep newts   Name                        = /data/ts/newts  

Following is a sample weekly incremental backup strategy for a recoverable database. It includes a weekly full database backup operation, a daily non-cumulative (delta) backup operation, and a mid-week cumulative (incremental) backup operation:

  Day of week       Backup Operation   Sunday            Full   Monday            Online incremental delta   Tuesday           Online incremental delta   Wednesday         Online incremental   Thursday          Online incremental delta   Friday            Online incremental delta   Saturday          Online incremental   For an automatic database restore of the images created on Friday morning, issue:   restore db mydb incremental automatic taken at <Friday timestamp>   For a manual database restore of the images created on Friday morning, issue:   restore db mydb incremental taken at <Friday timestamp>   restore db mydb incremental taken at <Sunday timestamp>   restore db mydb incremental taken at <Wednesday timestamp>   restore db mydb incremental taken at <Thursday timestamp>   restore db mydb incremental taken at <Friday timestamp>  

Optimizing Restore Performance

To reduce the amount of time required to complete a restore operation:

• Increase the restore buffer size. The restore buffer size must be a positive integer multiple of the backup buffer size specified during the backup operation. If an incorrect buffer size is specified, the buffers allocated will be the smallest acceptable size.

• Increase the number of buffers. The value you specify must be a multiple of the number of pages that you specified for the backup buffer. The minimum number of pages is 16.

• Increase the value of the PARALLELISM option. This will increase the number of buffer manipulators (BM) that will be used to write to the database during the restore operation. The default value is 1.

Restore Considerations

There are a number of points to consider when planning the use of RESTORE command:

• A full database backup image can be used to create a new DB2 database that can be used for disaster recovery at a remote location or as a source for running read-only queries.

• The restore command must read the entire backup image to complete the processing.

• If the archive logging is enabled, selected table space can be restored from either a full database backup or a table space backup that contains the select subset of table space.

• The DB2 catalog table space must be restored in offline mode; then it must be rolled forward to the end of logs to maintain a consistent database (restoring the catalog partition first, then all other partitions in parallel).

• A dropped table space cannot be recovered from a previous backup image with a RESTORE command in table space mode, even if the table space was recreated with a new CREATE TABLESPACE command.

• Use the REDIRECT restore option to allocate additional space or to bypass disk media failures.

Restore to a Damaged Partition

If the database is damaged at a partition level, normally you can use RESTORE and ROLLFORWARD utilities to recover the database partition. If for some reasons you cannot connect to this database partition, the restore will fail. First you must drop the database at the database partition number, create a database at the database partition number, then restore the database from the last backup.

• Determine which database partition is damaged.

• Capture the database configuration.

• Drop the database at the database partition number.

• Create a database at the database partition number.

• Update the database configuration.

• The database partition is placed in restore pending condition.

• Run DB2 restore to recreate the database partition.