Creates a backup copy of a database or a table space.
Scope
This command only affects the node on which it is executed.
Authorization
One of the following:
Required Connection
Database. This command automatically establishes a connection to the specified database.
Command Syntax
>>-BACKUP----+-DATABASE-+---database-alias---------------------->
'-DB-------'
>-----+---------------------------------------+----------------->
'-USER--username--+------------------+--'
'-USING--password--'
>-----+--------------------------------------------+------------>
| .-,------------------. |
| V | |
'-TABLESPACE--(-----tablespace-name---+---)--'
>-----+---------+----------------------------------------------->
'-ONLINE--'
>-----+-------------------------------------------------------+->
+-USE ADSM--+-------------------------------+-----------+
| '-OPEN--num-sessions--SESSIONS--' |
| .-,--------. |
| V | |
+-TO----+-dir-+--+--------------------------------------+
| '-dev-' |
'-LOAD--library-name--+-------------------------------+-'
'-OPEN--num-sessions--SESSIONS--'
>-----+-----------------------------+--------------------------->
'-WITH--num-buffers--BUFFERS--'
>-----+----------------------+---+-----------------+------------>
'-BUFFER--buffer-size--' '-PARALLELISM--n--'
>----+-------------------+-------------------------------------><
'-WITHOUT PROMPTING-'
Command Parameters
| Note: | An online backup operation may time out if there is an IX lock on sysibm.systables, because the DB2 backup utility requires an S lock on objects containing LOBs. |
Use of tape devices or floppy disks may generate messages and prompts for user input. Valid response options are:
Tape is not supported on OS/2. On OS/2, 0 or 0: can be specified to cause the backup operation to call the user exit program (see the Administration Guide). This option is not valid on any other platform.
To use tape devices, DB2 users on SCO UnixWare 7 must specify a buffer size of 16.
Examples
db2 backup database sample use adsm open 2 sessions with 4 buffers
db2 backup database payroll tablespace syscatspace, userspace1 to
/dev/rmt0, /dev/rmt1 with 8 buffers without prompting
Usage Notes
Database Level Backup
| Note: | Backup each database on a regular basis. If a database becomes damaged or corrupted, it can be returned to the state of the backed-up copy (see RESTORE DATABASE). |
If a successfully restored database was enabled for roll-forward recovery at the time of the backup operation, it can be returned to the state it was in prior to the occurrence of damage (see ROLLFORWARD DATABASE).
The backup can be directed to fixed disk, diskette, tape, ADSM utility, or to other vendor products enabled for DB2.
On UNIX based systems, a backup file name consists of the concatenation of several units of information separated by periods:
dbname.type.instance.nodexxxx.catnxxxx.yyyymmddhhmmss.seq
In addition to fixed disk, tape, ADSM and other vendors, the backup may be directed to diskettes. Since there is no general tape support on OS/2 or the Windows operating system, each type of tape device requires a unique device driver.
To back up to the FAT file system on OS/2 or the Windows operating system, users must conform to the 8.3 naming restriction. The file is placed in a 5-level subdirectory tree as follows:
dbname.type\db2instance.nodexxx\catnxxx\yyyymmdd\hhmmss.seq
Online backups are permitted only if roll-forward recovery is enabled. The utility can perform an online backup while it is being accessed and modified by other applications.
To perform an offline backup, the utility must be able to connect to the database in exclusive mode. BACKUP fails if any application, including the calling application, is already connected to the database. If the connection is successful, BACKUP locks out other applications until the backup is completed.
Execute BACKUP DATABASE offline when the database is not currently needed.
An offline backup operation will fail if the database is not in a consistent state. If the database is inconsistent, it must be restarted to be brought back to a consistent state through crash recovery before BACKUP is executed (see RESTART DATABASE, and a description of the autorestart configuration parameter in the Administration Guide). If the database is in a partially restored state after a system failure during restoration, the restore operation must be successfully rerun before a backup can be executed. If the database has been restored and a roll-forward operation is needed, the database must be rolled forward to a consistent state before it can be backed up.
If a database is changed from roll-forward disabled to roll-forward enabled state, either the logretain or the userexit database configuration options must be enabled before a backup of the database can be made (see GET DATABASE CONFIGURATION).
Table Space Level Backup
A table space level backup contains one or more table spaces for a database, specified when the command is executed. A table space level backup can be taken online or offline, but the database must be configured with roll-forward recovery enabled.
Table space level backup can be used to recover from problems that only affect specific table spaces. While this recovery is taking place, all other table spaces are available for processing.
To ensure that restored table spaces are synchronized with the rest of the database, the table spaces must be rolled forward to the end of the log (or to the point where the table spaces were last used). For this reason, table space level backup and restore can only be performed if roll-forward recovery is enabled. If roll-forward recovery is disabled at any time after a table space level backup is executed, it will not be possible to restore from the backup, and then to roll the table space forward to the current point in time. In this case, all table space level backups taken prior to that time are no longer restorable. The restore operation will fail if the user tries to restore from such a backup. In cases where it cannot be determined that the backup is not valid (if, for instance, the database has been restored and rolled forward, thus creating a new log sequence), the restore may be successful, and the broken restore set will be detected during roll-forward recovery.
The user may choose to separate data, index, long field (LONG), and large objects (LOB) into different table spaces. Long field and LOB data for the same table must reside in the same table space.
Each component of a table may be backed up and restored with the table space in which it resides, independently of the other components of the table.
It is not necessary to back up table spaces for temporary tables. If a list of table spaces to be backed up contains such a table space, BACKUP fails.
Table space level backup and restore cannot be run concurrently.
See Also