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\CATN0000020829 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:
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:
DB_alias.Type.Inst_name.NODE nnnn .CATN nnnn .timestamp.Seq_num
On Windows operating systems, a five-level subdirectory tree is used:
[View full width]
When a backup image is written to tape:
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.
Displaying Backup Information
You can use db2ckbkp to display information about existing backup images.
This utility allows you to:
Authorities Required to Use Backup
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:
Two situations require the backed-up image of a database partition at a database partition that does not contain user data for the database:
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:
Detailed information is provided through the online help facility within the Control Center.
Backing Up to Tape
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
Backing Up to Named Pipes
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:
Backup Database: Examples
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
Optimizing Backup Performance
To reduce the amount of time required to complete a backup operation:
Offline versus Online Backup
Database backup can be performed either offline or online:
There are a number of points to consider when planning the use of BACKUP command: