Performing a Database Backup, Restore, and Rollforward
© 2001 International Business Machines Corporation. All rights reserved.
This paper provides a step-by-step guide to performing the basic operations of a database backup, restore, and the rollforwarding of log files. The following examples will introduce syntactical variations of these utilities in order to display the flexibility of the DB2® product.
In general, this topic will address issues of authorization, required connections, syntax and helpful usage notes to successfully perform a backup, restore, or rollforward procedure. It is assumed you are familiar with the DB2 Universal DatabaseTM (DB2 UDB) engine and your system meets the following requirements:
- DB2 UDB version 6 or 7 is installed.
- An instance has been created in DB2.
- The operating system is UNIX®, Microsoft Windows 98, Windows 2000 or Windows NT.
- The edition of DB2 is Personal, Workgroup, Enterprise (EE), or Extended-Enterprise (EEE).
- There is enough disk space on the local machine or storage device to hold the backup image.
Note: All examples in this paper are created from a DB2 command line environment.
Creating the sample database
The database used in these examples is a "sample" database which can be created by performing the following:
- On Windows NT for DB2 V7.1:
- Click on the Start menu at the lower left hand corner of the screen.
- Click on Programs.
- Click on (IBM DB2).
- Click on First Steps.
- Click on Create the Sample Databases.
- Select Sample.
- On UNIX:
- Switch User to the DB2 instance owner.
- Execute db2profile which is located in $HOME/instance owner/sqllib
- Type db2sampl at the command line.
Note: If db2sampl is not found, it means that the optional db2sampl script was not installed with the rest of the DB2 product. You will need to manually create a database.
Testing the effects of the rollforward command
If you will be using the sample database as a test for the examples below, update or insert rows into one of the tables in the sample database. This way, you will be able to see the effect of using the rollforward procedure versus not using it. For example, one of the tables residing in the sample database is the employee table. After backing up but before restoring the database, issue a command like this:
db2 "update employee set empno = '11' where firstnme = 'CHRISTINE'"
The "empno" field for 'CHRISTINE' is changed from '10' to '11'.
Then, if you decide to use the rollforward option, you will see that this update has been recovered from the log file. If the rollforward option is not used, you will see the previous value of '10'.
Before going through the process of a backup, restore, and rollforward, it might be beneficial to look at the process from a graphical viewpoint. As we step through the process, refer to the graphic below for more clarity.
Before backing up a database, make sure to have one of the following authorities on DB2:
For these examples, create a directory such as C:\backup in which to store the database image. It is not necessary to connect to the database in order to perform a backup since the backup command will automatically create a connection. In addition, make sure no other applications are connected to the database when performing an offline backup or you will receive an SQL1035N error message. DB2 "list applications" and DB2 "force applications all" are useful commands for this task.
There are two types of backups you can perform. The first is an offline database backup. This type of backup requires an exclusive connection to the database since all tablespaces in the database will be backed up. The second type is an online database backup. This type of backup is especially useful for users who run production databases and need to have one or more of the tablespaces in the database running continuously. When an online backup is used, only the tablespaces being backed up require an exclusive connection by the user. This permits other tablespaces within the database that are not being backed up to remain online for other applications to access.
Note: If performing an online backup, make sure the rollforward recovery parameter
logretain = on or
userexit** is enabled in the database configuration file. If it is not, you will receive an SQL2413N error and will need to perform the following commands:
db2 "update database configuration for [database name] using logretain on" > db2stop > db2start
userexit option requires detailed instructions, it will not be discussed in this paper.
After logretain, userexit, or both of these parameters are enabled, make a full offline backup of the database. A verification to see if the database is in backup pending state can be made by performing the following command:
db2 "get database configuration for sample"
Then look for the "Backup Pending" value (it will be set to "YES" or "NO"). After a full database backup is made, you will be able to perform an online tablespace backup at anytime thereafter.
Example 1 - Offline database backup to a local directory
db2 "backup database sample user db2admin using db2admin
to c:\backup with 3 buffers buffer 1000 without prompting"
This command will produce a backup image of the database "sample" with a user of db2admin and password of db2admin (only needed if the current user does not have the required database authorities). The image will be stored in c:\backup using 3 buffers each having a buffer size of 1000 pages. The "without prompting" key phrase specifies the backup will run unattended and that any actions which normally require user intervention will return an error message.
Example 2 - Online tablespace level backup to a tape device
db2 "backup database sample tablespace (syscatspace,
userspace1) online to /dev/rmt0 without prompting"
This command will produce a backup image of the specified tablespaces within a database. Syscatspace and userspace1 are two of the tablespaces residing in the sample database to be backed up. The tape drive is /dev/rmt0. Since the number of buffers and buff pages is not defined in the command, the default values will be Buffers = 2 and Buffer(size) = 1024 pages. When using tape devices on SCO UNIXware 7, you must specify a buffersize of 16 pages.
The advantage of an online tablespace backup is that all other tablespaces within the database are available for processing. The default is "offline" which requires all applications to be disconnected from the database while the backup is being performed.
After executing these commands you should receive the following message on the DB2 command line:
"Backup successful. The timestamp for this backup image is: timestamp"
The timestamp is a unique identifier for each database backup image and will be needed when you perform a restore if you have more than one backup image in a single folder. On UNIX systems, the timestamp is concatenated to the backup image file name:
On Windows NT systems, the backup image is stored in a 5 level directory tree which also contains the timestamp information:
For information of previously backed-up databases, enter the following command:
db2 "list history backup all for database sample"
Before restoring a database, have one of the following authorities on DB2:
If restoring to a new database (not an existing one), SYSADM or SYSCTRL is needed.
An offline database restore will aquire an exclusive connection; so no application should be connected during this task. Also, make sure no other applications are connected to the database when performing an offline restore or you will receive an SQL1035 error message.
Example 1 - Offline restore into an existing database from a local directory
db2 "restore database sample user db2admin using db2admin
from c:\backup taken at 20010222145404 with 3 buffers buffer
1000 without rolling forward without prompting"
This command will restore the database image which is saved at c:\backup with a timestamp of 20010222145404, to the existing sample database (please refer to the database backup section on how to obtain the previously mentioned timestampvalue). This command will effectively overwrite the old sample database files with the backed up database image files. The key phrase "without rolling forward" will keep the database manager from putting the restored database in rollforward pending state (this phrase is not needed if the database was not enabled for rollforward recovery at the time it was backed up). If you are interested in recovering all database files to the point of the last successful transaction and the database was enabled for rollforward recovery at the time it was backed up, leave out the "without rolling forward" key phrase.
If the keyword "from" is not used, the current directory will be the default location of the backup image. Also, the key phrase "taken at timestamp" is not necessary if only one database backup image resides in the target folder.
Example 2 - Online tablespace restore into existing database from a tape device
db2 "restore database sample tablespace (syscatspace,
userspace1) online from /dev/rmt0 taken at 20010222151429 newlogpath
/home/db2v71/samplelogs without prompting"
This command will restore the syscatspace and userspace1 tablespaces to the existing sample database while allowing the uninhibited processing of other tablespaces not involved in the online restore command. Again, the "taken at timestamp" key phrase must be declared if there is more than one backup image in the same folder. The "newlogpath" keyword may be included if the logpath in the backup image is not suitable to be used after the restore. For example, when the path is no longer valid or is currently used by a different database.
Example 3 - Offline restore into a new database on AIX®
db2 "restore database sample from /home/db2v71/backup into
newsamp without prompting"
This command will restore the sample database backup image into the database newsamp. The "newsamp" database will be created by DB2 immediately after the restore command is executed. The backup image will then be restored into the "newsamp" database.
Note: the "taken at timestamp" key phrase is not used if there is only one backup database image in the /home/db2v71/backup directory.
The above command is useful when creating a copy of an existing database. The sample database will remain unaffected by the restore procedure and thus be available to be used in the future. You may also use the same restore command to restore into an existing database. For example, you may have already created the newsamp database before executing the above command.
Note: if you plan to perform this function, the codepage of the database to be restored into must match the codepage of the backed-up database image.
Additional comments: You can restore to an existing remote database if the platform, DB2 version, and edition are the same. Make sure to have the aforementioned authorities on either the local machine or server, depending on which machine is authenticating the user id, to perform the remote restore. In addition, have the database, which is to be restored, catalogued on your local machine in order to perform a remote restore (this is necessary in order to connect to the database being restored).
Before performing a rollforward on a database, make sure to have one of the following authorities on DB2:
There is no need to connect to the database before performing the rollforward procedure since the command establishes a database connection.
Note: In a multinode environment, this command can only be issued from a catalogued node. Also, if the database is in rollforward pending state and the command is successfully performed, the database manager will take the database out of rollforward pending state.
Example 1 - Offline rollforward database to a point in time
db2 "rollforward database sample user db2admin using
db2admin to 2001-02-22-184.108.40.206422 and stop"
This command will rollforward all logs located in the log folder specified in the database configuration file for Sample up to and including the above stated point-in-time*. The "and stop" key phrase completes the rollforward recovery process by rolling back incomplete transactions and turning off the rollforward pending state of the database.
* The database configuration file contains information regarding the path to the database logfiles. To view this path, enter the following command:
db2 "get database configuration for sample"
Then look for the parm name - logpath. This is the path where the database log files are stored.
Note: When rollforwarding tablespaces to a point-in-time, the tablespaces are placed in backup pending state and will not be useable until a database backup is performed.
Example 2 - Online rollforward tablespace level to end of logs
db2 "rollforward database sample to end of logs and stop
tablespace (userspace1) online"
This command will rollforward all logs located in the log folder specified in the database configuration file for "sample". Only the syscatspace and userspace1 tablespaces will be rolled forward. Since this is an online rollforward, all other tablespaces in the database may be accessed by other applications.
Example 3 - Online tablespace level rollforward on a multinode database
db2 "rollforward database sample to end of logs on nodes
(0,2) and stop tablespace userspace1 online"
This command rollsforward all log files associated with the tablespace userspace1 on nodes 0 and 2. If the "nodes" keyword is not used, DB2 will check all nodes associated with the database. For instance, if the "nodes" keyword is not used in the above command, and node 1 is not ready to be rollforwarded, the command will fail.
Here is a sample output returned to the command line after performing a rollforward:
Rollforward Status Input database alias = sample Number of nodes have returned status = 1 Node number = 0 Rollforward status = not pending Next log file to be read = Log files processed = S0000001.LOG - S0000001.LOG Last committed transaction = 2001-02-26-21.54.54.000000 DB20000I The ROLLFORWARD command completed successfully.
Note: With a tablespace rollforward to a point-in-time, the "node" keyword is not accepted. This is because all database partitions must be rolled forward to ensure that all partitions are at the same level.
As we have seen, there are different ways to perform the backup, restore, and rollforward commands depending on each unique situation. The above commands attempt to provide some basic scenarios, but may not fit the requirements for a particular situation. For more information regarding the use of these commands, please refer to the DB2 Command Reference book. If the command book is not available, visit the DB2 Information Center.