The simplest form of the RESTORE DATABASE command requires only that you specify the alias name of the database that you want to restore.
db2 restore db sample
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]
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.
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:
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:
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]
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 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]
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]
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:
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:
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]
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]
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:
There are a number of points to consider when planning the use of RESTORE command:
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.