Backup overview

Create a backup of your Db2® database and related stored data to prevent data loss in the event of a database service outage. There are several tools that you can use to complete the backup process.

The simplest form of the Db2 BACKUP DATABASE command requires only that you specify the alias name of the database that you want to back up. For example: 
db2 backup db sample

In IBM® Data Studio Version 3.1 or later, you can use the task assistant for backing up databases. Task assistants can guide you through the process of setting options, reviewing the automatically generated commands to perform the task, and running these commands. For more details, see Administering databases with task assistants.

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. Backup images created by Db2 Version 9.5 and later are generated with file mode 600, meaning that on UNIX only the instance owner has read and write privileges and on Windows only members of the DB2ADMNS (and Administrators) group have access to the backup images.
Note: If the Db2 client and server are not located on the same system, Db2 database systems will determine which directory is the current working directory on the client machine and use that as the backup target directory on the server. For this reason, it is recommended that you specify a target directory for the backup image.
Backup images are created at the target location specified when you invoke the backup utility. This location can be:
  • A Tivoli® Storage Manager (TSM) server
  • Another vendor's server

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.

If you want to delete old backup images that are no longer required, you can remove the files if the backups are stored as files. If you subsequently run a LIST HISTORY command with the BACKUP option, information about the deleted backup images will also be returned. You must use the PRUNE command to remove those entries from the recovery history file.

If your recovery objects were saved using Tivoli Storage Manager (TSM), you can use the db2adutl utility to query, extract, verify, and delete the recovery objects. On Linux® and UNIX, this utility is located in the sqllib/adsm directory, and on Windows operating systems, it is located in sqllib\bin. For snapshots, use the db2acsutil utility located in sqllib/bin.

On all operating systems, file names for backup images created on disk consist of a concatenation of several elements, separated by periods: 
DB_alias.Type.Inst_name.DBPARTnnn.timestamp.Seq_num
For example: 
STAFF.0.DB201.DBPART000.19950922120112.001
Database 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, and 4 represents a backup image generated by the LOAD COPY TO command.
Instance name
A 1- to 8-character name of the current instance that is taken from the DB2INSTANCE environment variable.
Database partition number
In single partition database environments, this is always DBPART000. In partitioned database environments, it is DBPARTxxx, where xxx is the number assigned to the database partition in the db2nodes.cfg file.
Time stamp
A 14-character representation of the date and time at which the backup operation was performed. The time stamp is in the form yyyymmddhhnnss, 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)
  • hh represents the hour (00 to 23)
  • nn represents the minutes (00 to 59)
  • ss represents the seconds (00 to 59)
Sequence number
A 3-digit number used as a file extension.

You cannot back up a database that is not in a normal or backup-pending state. A table space that is in a normal or backup-pending state can be backed up. If the table space is not in a normal or backup-pending state, a backup may or may not be permitted.

Concurrent backup operations on the same table space are not permitted. Once a backup operation has been initiated on a table space, any subsequent attempts will fail (SQL2048N).

If a database or a table space is in a partially restored state because a system crash occurred during the restore operation, you must successfully restore the database or the table space before you can back it up.

A backup operation will fail if a list of the table spaces to be backed up contains the name of a temporary table space.

The backup utility provides concurrency control for multiple processes that are making backup copies of different databases. This concurrency control keeps the backup target devices open until all the backup operations have ended. If an error occurs during a backup operation, and an open container cannot be closed, other backup operations targeting the same drive might receive access errors. To correct such access errors, you must terminate the backup operation that caused the error and disconnect from the target device.

Displaying backup information

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 or not 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.