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]SQL2539W Warning! Restoring to an existing database that is the same as the backup imagedatabase. 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:
From the Control Center, expand the object tree until you find the Databases folder.
Click on the Databases folder. Any existing databases are displayed in the pane on the right side of the window.
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]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.
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]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
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]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
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 allowed?any 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]db2_all '<<+0< db2 restore db sample from /data/dbbackup taken at 20020829013314 into
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]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,
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.
