


Using conditional parsing in configuration files Next Page

Backup utility (dbbackup)

Creates a client-side backup of database files and transaction logs for running databases.


dbbackup [ options ] target-directory

Option Description

Use this option to read in options from the specified environment variable or configuration file. See Using configuration files.


-b block-sizeUse this option to specify the maximum block size (in number of pages) to be used to transfer pages from the database server to dbbackup. The dbbackup utility tries to allocate this number of pages; if it fails, it repeatedly reduces this value by half until the allocation succeeds. The default size is 128 pages.
-c "keyword=value; ..."

Specify connection parameters. The user ID must have DBA authority or REMOTE DBA authority to connect to the database. See Connection parameters.

For example, the following command backs up the sample database running on the server sample_server, connecting as the DBA user, into the SQLAnybackup directory:

-d Back up the main database files only, without backing up the transaction log file, if one exists.
-k checkpoint-log-copy-option

This option specifies how dbbackup processes the database files before writing them to the destination directory. The choice of whether to apply pre-images during a backup, or copy the checkpoint log as part of the backup, has performance implications. If the -s option is specified to cause the backup to be performed on the server, the default setting for -k is auto; otherwise, the default setting is copy.

  • auto  When you specify auto, the database server checks the amount of available disk space on the volume hosting the backup directory. If there is at least twice as much disk space available as the size of the database at the start of the backup, then the backup proceeds as if copy was specified. Otherwise, it proceeds as if nocopy was specified. This setting can only be used if -s is specified.

  • copy  When you specify copy, the backup reads the database files without applying pre-images for any modified pages. The checkpoint log in its entirety, as well as the system dbspace, is copied to the backup directory. The next time the database is started, the database server automatically recovers the database to its as of the checkpoint at the start of the backup.

    Because page pre-images do not have to be written to the temporary file, using this option can provide better backup performance and reduce internal server contention for other connections that are operating during a backup. However, since the backup copy of the database file includes the checkpoint log, which has pre-images of any pages modified since the start of the backup, the backed-up copy of the database files may be larger than the database files at the time the backup started. The copy option should be used when disk space in the destination directory is not an issue.

  • nocopy  When you specify nocopy, the checkpoint log is not copied as part of the backup. This option causes pre-images of modified pages to be saved in the temporary file so that they can be applied to the backup as it progresses. The backup copies of the database files will be the same size as the database when the backup operation commenced. The backup copies may actually be slightly smaller because the checkpoint log is not present in this copy. This option results in smaller backed up database files, but the backup may proceed more slowly, and possibly decrease performance of other operations in the database server. It is useful in situations where space on the destination drive is limited.

  • recover  When you specify recover, the database server copies the checkpoint log (as with the copy option), but applies the checkpoint log to the database when the backup is complete. This restores the backed up database files to the same state (and size) that they were in at the start of the backup operation. This option is useful if space on backup drive is limited (it requires the same amount of space as the copy option for backing up the checkpoint log, but the resulting file size is smaller). This setting can only be used if -s is also specified.

-l filename

This option is provided to enable a secondary system to be brought up rapidly in the event of a server crash. A live backup does not terminate, but continues running while the server runs. It runs until the primary server becomes unavailable. At that point, it shuts down, but the backed up log file is intact and can be used to bring a secondary system up quickly. See Differences between live backups and transaction log mirrors, and Making a live backup.

If you specify -l, then you cannot use -s to create an image back up on the server.


This option is used in conjunction with -r. It changes the naming convention of the backup transaction log file to yymmddxx.log, where xx are sequential letters ranging from AA to ZZ and yymmdd represents the current year, month, and day.

The backup copy of the transaction log file is stored in the directory specified in the command, and with the yymmddxx.log naming convention. This allows backups of multiple versions of the transaction log file to be kept in the same backup directory.

You can also use both the -x option and the -n option to rename the log copy. For example

dbbackup -c "UID=DBA;PWD=sql" -x -n mybackupdir
-o filename Write output messages to the named file.
-q Do not display output messages. This option is available only when you run this utility from a command prompt.

This option renames the transaction log and starts a new transaction log. It forces a checkpoint and causes the following three steps to occur:

  1. The current working transaction log file is copied and saved to the directory specified in the command.

  2. The current transaction log remains in its current directory, but is renamed using the format yymmddxx.log, where xx are sequential characters starting at AA and running through to ZZ, and yymmdd represents the current year, month, and day. This file is then no longer the current transaction log.

  3. A new transaction log file is generated that contains no transactions. It is given the name of the file that was previously considered the current transaction log, and is used by the database server as the current transaction log.

-s This option allows you to create an image backup on the server using the BACKUP DATABASE statement. If you specify the -s option, the -l option (to create a live backup of the transaction log) cannot be used. The directory specified is relative to the server's current directory, so it is recommended that you specify a full pathname. In addition, the server must have write permissions on the specified directory. When -s is specified, the Backup utility does not display progress messages and does not prompt you when it overwrites existing files. If you want to be prompted when an attempt is made to overwrite an existing file, do not specify -s or -y. You must specify -s if you specify the -k recover option.
-t This can be used as an incremental backup since the transaction log can be applied to the most recently backed up copy of the database file(s).
-x Back up the existing transaction log, delete the original log, and then start a new transaction log.
-xo Delete the current transaction log and start a new one. This operation does not perform a backup; its purpose is to free up disk space in non-replication environments.
-y Choosing this option creates the backup directory or replaces a previous backup file in the directory without confirmation. If you want to be prompted when an attempt is made to overwrite an existing file, do not specify -s or -y.
target-directory The directory the backup files are copied to. If the directory does not exist, it is created. However, the parent directory must exist.

The Backup utility makes a backup copy of all the files for a single database. A simple database consists of two files: the main database file and the transaction log. More complicated databases can store tables in multiple files, with each file as a separate dbspace. All backup file names are the same as the database file names. The image backup created by the Backup utility consists of a separate file for each file that is backed up.

For more information about making archive backups (a single file that contains both the database file and the transaction log), see Making archive backups.

Using the Backup utility on a running database is equivalent to copying the database files when the database is not running. You can use the Backup utility to back up the database while other applications or users are using it.

If neither of the options -d or -t are used, all database files are backed up.

The Backup utility creates a client-side backup of the database files. You can specify -s to create a backup on the server using the BACKUP DATABASE statement.

For information about performing server-side backups, see BACKUP statement.

In addition to dbbackup, you can access the Backup utility in the following ways:

For more information about recommended backup procedures, see Backup and Data Recovery.

Exit codes are 0 (success) or non-zero (failure).
