The simplest form of the BACKUP DATABASE command requires only that you specify the alias or name of the database that you want to back up.
db2 backup db sample
If the command completes successfully, you will have acquired a new backup image that is located in the path or the directory from which the command was issued. It is located in this directory because the command in this example does not explicitly specify a target location for the backup image.
On the Windows operating systems, for example, this command (when issued from the root directory of the D: drive) creates an image that appears in a directory listing as follows:
Windows: Directory of D:\SAMPLE.0\DB2\NODE0000\CATN0000\20020829 08/29/2002 15:26p <DIR> . 08/29/2002 15:26p <DIR> .. 08/29/2002 15:26p 12,615,680 122644.001
On UNIX, the backup image name will be of the form below:
If the DB2 client and server are not located on the same system, the default target directory for the backup image is the current working directory on the client system where the command was issued, unless a path for the backup image is specified. This target directory or device must exist on the server system.
You have the option to specify the backup target location when you invoke the backup utility. This backup target location can be:
A directory (for backups to disk or diskette)
A device (for backups to tape)
A Tivoli Storage Manager (TSM) server
Another vendor's server, such as VERITAS, etc....
The recovery history file is updated automatically with summary information whenever you invoke a database backup operation. This file is created in the same directory as the database configuration file.
On UNIX-based systems, file names for backup images created on disk consist of a concatenation of several elements, separated by periods:
On Windows operating systems, a five-level subdirectory tree is used:
[View full width]DB_alias.Type\Inst_name\NODEnnnn\CATNnnnn\yyyymmdd\hhmmss.Seq_num For example: D:\SAMPLE.0\DB2\NODE0000\CATN0000\20020829\122644.001 DB_alias: A 1- to 8-character database alias name that was specified when the backup utility was invoked. Type: Type of backup operation, where: • 0 represents a full database-level backup • 3 represents a table space level backup • 4 represents a backup image generated by the LOAD...COPY TO command. Inst_ name: A 1- to 8-character name of the current instance that is taken from the DB2INSTANCE environment variable. NODEnnnn: The database partition number. In non-partitioned database systems, this is always NODE0000. In partitioned database systems, it is NODExxxx, where xxxx is the number assigned to the database partition in the db2nodes.cfg file. CATNnnnn: The database partition number of the catalog partition for the database. In non-partitioned database systems, this is always CATN0000. In partitioned database systems , it is CATNnnnn, where nnnn is the number assigned to the database partition in the db2nodes.cfg file. Date: An 8-character representation of the date at which the backup operation was performed. The date is in the form yyyymmdd, where: • yyyy represents the year (1995 to 9999) • mm represents the month (01 to 12) • dd represents the day of the month (01 to 31) Time: A 6-character representation of the time at which the backup operation was performed . The time is in the form hhmmss, where: • hh represents the hour (00 to 23) • mm represents the minutes (00 to 59) • ss represents the seconds (00 to 59) Seq_num: is a 3-digit number used as a file extension.
When a backup image is written to tape:
File names are not created, but the information described above is stored in the backup header for verification purposes.
A tape device must be available through the standard operating system interface. On a large partitioned database system, however, it may not be practical to have a tape device dedicated to each database partition. You can connect the tape devices to one or more TSM servers, so that access to these tape devices is provided to each database partition.
On a partitioned database system, you can also use products that provide virtual tape device functions, such as CLIO/S. You can use these products to access the tape device connected to other database partitions through a pseudo tape device. Access to the remote tape device is provided transparently, and the pseudo tape device can be accessed through the standard operating system interface.
You cannot back up a database that is in an inconsistent state. If any table space is in an abnormal state, you cannot back up the database or that table space, unless it is in backup pending state.
If a database or a table space is in a partially restored state because a system crash occurred during the restore operation, you must complete the restore of the database or the table space before you can back it up.
A table space level backup operation will fail if a list of the table spaces to be backed up contains the name of a temporary table space.
If you are using the backup utility for concurrent backup operations to tape, ensure that the processes do not target the same tape.
You can use db2ckbkp to display information about existing backup images.
This utility allows you to:
Test the integrity of a backup image and determine whether it can be restored.
Display information that is stored in the backup header.
Display information about the objects and the log file header in the backup image.
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, i.e., the required privilege or authority. You must have SYSADM, SYSCTRL, or SYSMAINT authority to use the backup utility.
You should not be connected to the database that is to be backed up. The backup utility automatically establishes a connection to the specified database, and this connection is terminated at the completion of the backup operation. Offline backup gets an exclusive connection to the database.
The database can be local or remote. The backup image remains on the database server, unless you are using a storage management product, such as TSM.
On a partitioned database system, database partitions are backed up individually. The operation is local to the database partition on which you invoke the utility. You can, however, issue db2_all from one of the database partitions in the instance to invoke the backup utility on a list of servers (or all servers), which you identify by database partition number. (Use the LIST DBPARTITIONNUMS command to identify the database partitions that have user tables on them.) If you do this, you must back up the catalog partition first, then back up the other database partitions.
You should also keep a copy of the db2nodes.cfg file with any backup copies you take as protection against possible damage to this file.
On a distributed request system, backup operations apply to the distributed request database and the metadata stored in the database catalog (wrappers, servers, nicknames, and so on). Data source objects (tables and views) are not backed up, unless they are stored in the distributed request database.
If a database was created with a previous release of the database manager, and the database has not been migrated, you must migrate the database before you can back it up.
The following restrictions apply to the backup utility:
A table space backup operation and a table space restore operation cannot be run at the same time, even if different table spaces are involved.
If you want to be able to do rollforward recovery in a partitioned database environment, you must regularly back up the database on the list of database partitions, and you must have at least one backup image of the rest of the database partitions in the system (even those that do not contain user data for that database).
Two situations require the backed-up image of a database partition at a database partition that does not contain user data for the database:
You added a database partition to the database system after taking the last backup, and you need to do forward recovery on this database partition.
Point-in-time recovery is used, which requires that all database partitions in the system are in rollforward pending state.
The backup utility can be invoked through the command line processor (CLP), the database Backup Wizard in the Control Center, or the db2Backup API.
Following is an example of the BACKUP DATABASE command issued through the CLP:
db2 backup database sample to c:\DB2Backups
To open the database Backup 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 Backup Wizard from the pop-up menu. The database Backup Wizard opens.
Detailed information is provided through the online help facility within the Control Center.
When you back up your database or table space to tape, you must correctly set your block size and your buffer size. This is particularly true if you are using a variable block size (on AIX, for example, if the block size has been set to zero).
There is a restriction on the number of fixed block sizes that can be used when backing up. This restriction exists because DB2 writes out the backup image header as a 4-KB block. The only fixed block sizes DB2 supports are 512, 1024, 2048, and 4096 bytes. If you are using a fixed block size, you can specify any backup buffer size. However, you may find that your backup operation will not complete successfully if the fixed block size is not one of the sizes that DB2 supports.
An example of backing up and restoring the database to and from an attached tape drive:
db2 backup database sample to /dev/rmt0 with 2 buffers buffer 512 db2 restore database sample from /dev/rmt0 taken at <timestamp> with 2 buffers buffer 512
DB2 supports database backup to (and database restore from) local named pipes on UNIX-based systems. Both the writer and the reader of the named pipe must be on the same machine. The pipe must exist and be located on a local file system. Because the named pipe is treated as a local device, there is no need to specify that the target is a named pipe.
Following is an example:
Create a named pipe:
cd /data/dbbackup/ mkfifo mypipe ls -altr mypipe prw-r--r-- 1 v8inst sys 0 Aug 29 01:09 mypipe
If this backup image is going to be used by the restore utility, the restore operation must be invoked before the backup operation, so that it will not miss any data:
db2 restore database sample from /data/dbbackup/mypipe into newdb &
Use this pipe as the target for a database backup operation:
db2 backup db sample to /data/dbbackup/mypipe
In the following example, the database SAMPLE is defined on all four partitions, numbered 0 through 3. The path /data/dbbackup is accessible from all partitions. Partition 0 is the catalog partition and needs to be backed up separately because this is an offline backup. To perform an offline backup of all the SAMPLE database partitions to /data/dbbackup, issue the following commands from one of the database partitions:
db2_all '<<+0< db2 backup db sample to /data/dbbackup' Backup successful. The timestamp for this backup image is : 20020829012853 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 : 20020829012956 phantom: db2 backup database ... completed ok Backup successful. The timestamp for this backup image is : 20020829013012 phantom: db2 backup database ... completed ok Backup successful. The timestamp for this backup image is : 20020829013028 phantom: db2 backup database ... completed ok ls /data/dbbackup/ SAMPLE.0.v8inst.NODE0000.CATN0000.20020829012853.001 SAMPLE.0.v8inst.NODE0001.CATN0000.20020829012956.001 SAMPLE.0.v8inst.NODE0002.CATN0000.20020829013012.001 SAMPLE.0.v8inst.NODE0003.CATN0000.20020829013028.001
In the second command, the db2_all utility will issue the same backup command to each database partition in turn (except partition 0). All four database partition backup images will be stored in the /data/dbbackup directory.
In the following example, database SAMPLE is backed up to a TSM server using two concurrent TSM client sessions. The backup utility will use four buffers, which are of the default buffer size (1024 x 4-KB pages).
db2 backup database sample use tsm open 2 sessions with 4 buffers or db2 backup database sample use tsm open 2 sessions with 4 buffers buffer 1024
If the util_heap_sz < ( ( number of buffers x buffer size ) x number of data partitions), then an error (SQL2009C) will occur. The SQL2009C error indicates that there is not enough memory available to run the utility. You must increase the utility heap size (util_heap_sz).
db2 backup database sample with 4 buffers buffer 4096 SQL2009C There is not enough memory available to run the utility.
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 db2 backup db sample use tsm db2 backup db sample online incremental delta use tsm db2 backup db sample online incremental delta use tsm db2 backup db sample online incremental use tsm db2 backup db sample online incremental delta use tsm db2 backup db sample online incremental delta use tsm db2 backup db sample online incremental use tsm
The following command failed because an online backup allows other transactions to be changing the database simultaneously. Therefore, only a database configured for rollforward recovery can support an online backup.
User A connects to the database and selects a table db2 connect to sample db2 "select count(*) from employee" This database is configured as circular logging, and you want to take an online backup db2_all "db2 backup db sample online to /data/backup" the utility did not complete
Now, you want to take an offline backup, and the utility still failed because an offline backup requires exclusive use of the database. User A is already connected to the database earlier. To resolve this problem and perform the backup again, you must do the following.
Now you want to perform an offline backup db2_all "db2 backup db sample to /data/backup" the utility did not complete To resolve, you must do db2 terminate db2 force application all db2_all "db2 backup db sample to /data/backup" the utility complete successfully
To reduce the amount of time required to complete a backup operation:
Specify table space backup. You can back up (and subsequently recover) part of a database by using the TABLESPACE option on the BACKUP DATABASE command. This facilitates the management of table data, indexes, and long field or LOB data in separate table spaces.
Increase the value of the PARALLELISM parameter on the BACKUP DATABASE command so that it reflects the number of table spaces being backed up. The PARALLELISM parameter defines the number of processes or threads that are started when reading data from the database. Each process or thread is assigned to a specific table space. When it finishes backing up this table space, it requests another. Note, however, that each process or thread requires both memory and CPU overhead: On a heavily loaded system, keep the PARALLELISM parameter at its default value of 1.
Increase the backup buffer size. The ideal backup buffer size is a multiple of the table space extent size. If you have multiple table spaces with different extent sizes, specify a value that is a multiple of the largest extent size.
Increase the number of buffers. If you use multiple buffers and I/O channels, you should use at least twice as many buffers as channels to ensure that the channels do not have to wait for data.
Use multiple target devices, and the backup images must be directed to local devices.
Database backup can be performed either offline or online:
If the backup is performed offline, then only the backup task can be connected to the database partition.
An offline backup is the only backup supported when circular logging is used.
No connection is allowed when the backup task is running.
Concurrent offline backup of both catalog partition and non-catalog partitions is not possible.
If the backup is performed online, other applications or processes can continue to connect to the database while the backup task is running.
An online backup is supported when archive logging is enabled.
While the online backup operation is running, changes can be performed on the tables.
At the end of an online backup, the current archive log is truncated and made available for offline archival.
There are a number of points to consider when planning the use of BACKUP command:
For disaster recovery support, it is necessary to perform a full database backup as the starting point for recovery.
It is best to take table space level backups of the DB2 catalog table space (SYSCATSPACE). This will allow the restore of a damaged DB2 catalog to be as quick as possible.
Online backup, either table space level or full database, requires the retention of the archive DB2 logs at the time of the backup, for use in the restore and rollforward process. If the logs are not available, the database or table space cannot be recovered using that backup.
Selected table space backup that includes all of the table spaces related to a single application could be useful to perform point-in-time recovery of an application to a point prior to the execution of an erroneous application process.
Using the DB2 incremental backup options can reduce the size and duration of periodic DB2 backups by including only the changed portion of the database or table space.