BACKUP statement

Use this statement to back up a database and transaction log.

Syntax 1 (image backup)
BACKUP DATABASE
DIRECTORY backup-directory
[ WAIT BEFORE START ]
[ WAIT AFTER END ]
[ DBFILE ONLY ]
[ TRANSACTION LOG ONLY ]
[ TRANSACTION LOG RENAME [ MATCH ] ]
[ TRANSACTION LOG TRUNCATE ]
[ ON EXISTING ERROR ]
[ WITH COMMENT comment string ]
[ HISTORY { ON | OFF } ]
[ AUTO TUNE WRITERS { ON | OFF } ]
[ WITH CHECKPOINT LOG { AUTO | COPY | NO COPY | RECOVER } ]
backup-directory : { string | variable }
Syntax 2 (archive backup)
BACKUP DATABASE TO archive-root
[ WAIT BEFORE START ]
[ WAIT AFTER END ]
[ DBFILE ONLY ]
[ TRANSACTION LOG ONLY ]
[ TRANSACTION LOG RENAME [ MATCH ] ]
[ TRANSACTION LOG TRUNCATE ]
[ ATTENDED { ON | OFF } ]
[ WITH COMMENT comment string ]
[ HISTORY { ON | OFF } ]
archive-root :  { string | variable }
comment-string :   string
Parameters
  • DIRECTORY clause   The target location on disk for the backup files, relative to the database server's current directory at startup. If the directory does not exist, it is created. Specifying an empty string as a directory allows you to rename or truncate the log without first making a copy of it. Do not use this clause if you are using database mirroring. See Database mirroring and transaction log files.

  • WAIT BEFORE START clause   Use this clause to ensure that the backup copy of the database does not contain any information required for recovery. In particular, it ensures that the rollback log for each connection is empty.

    If a backup is carried out using this clause, you can start the backup copy of the database in read-only mode and validate it. By enabling validation of the backup database, you can avoid making an additional copy of the database.

  • WAIT AFTER END clause   Use this clause if the transaction log is being renamed or truncated. It ensures that all transactions are completed before the log is renamed or truncated. If this clause is used, the backup must wait for other connections to commit or rollback any open transactions before finishing.

  • DBFILE ONLY clause   Use this clause to cause backup copies of the main database file and any associated dbspaces to be made. The transaction log is not copied. You cannot use the DBFILE ONLY clause with the TRANSACTION LOG RENAME or TRANSACTION LOG TRUNCATE clauses.

  • TRANSACTION LOG ONLY clause   Use this clause to cause a backup copy of the transaction log to be made. No other database files are copied.

  • TRANSACTION LOG RENAME [MATCH] clause   This clause causes the database server to rename the current transaction log at the completion of the backup. If the MATCH keyword is omitted, the backup copy of the log will have the same name as the current transaction log for the database. If you supply the MATCH keyword, the backup copy of the transaction log is given a name of the form YYMMDDnn.log, to match the renamed copy of the current transaction log. Using the MATCH keyword enables the same statement to be executed several times without writing over old data.

  • TRANSACTION LOG TRUNCATE clause   If this clause is used, the current transaction log is truncated and restarted at the completion of the backup. Do not use this clause if you are using database mirroring. See Database mirroring and transaction log files.

  • archive-root clause   The file name or tape drive device name for the archive file.

    To back up to tape, you must specify the device name of the tape drive. For example, on NetWare the first tape drive is \\.\tape0. The number automatically appended to the end of the archive file name is incremented each time you execute an archive backup.

    The backslash ( \ ) is an escape character in SQL strings, so each backslash must be doubled. For more information about escape characters and strings, see Strings.

  • ON EXISTING ERROR clause   This clause applies only to image backups. By default, existing files are overwritten when you execute a BACKUP DATABASE statement. If this clause is used, an error occurs if any of the files to be created by the backup already exist.

  • ATTENDED clause   The clause applies only when backing up to a tape device. ATTENDED ON (the default) indicates that someone is available to monitor the status of the tape drive and to place a new tape in the drive when needed. A message is sent to the application that issued the BACKUP DATABASE statement if the tape drive requires intervention. The database server then waits for the drive to become ready. This may happen, for example, when a new tape is required.

    If ATTENDED OFF is specified and a new tape is required or the drive is not ready, no message is sent and an error is given.

  • WITH COMMENT clause   This clause records a comment in the backup history file. For archive backups, the comment is also recorded in the archive file.

  • HISTORY clause   By default, each backup operation appends a line to the backup.syb file. You can prevent updates to the backup.syb file by specifying HISTORY OFF. You may want to prevent the file from being updated if any of the following conditions apply:

    • your backups occur frequently
    • there is no procedure to periodically archive or delete the backup.syb file
    • disk space is very limited

  • AUTO TUNE WRITERS clause   When the backup starts, one thread is dedicated to writing the backup files to the backup directory. However, if the backup directory is on a device that can handle an increased writer load (such as a RAID array), then overall backup performance can be improved by increasing the number of threads acting as writers. If this clause is ON, the database server periodically examines the read and write performance from all of the devices taking part in the backup. If the overall backup speed can be improved by creating another writer, then the database server creates another writer. This option is ON by default.

  • WITH CHECKPOINT LOG clause   This clause specifies how the backup 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. The default setting is AUTO.

  • COPY clause   This option cannot be used with the WAIT BEFORE START clause of the BACKUP statement.

    When you specify COPY, the backup reads the database files without applying 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 the state it was in as of the checkpoint at the time the backup started.

    Because pages 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 checkpoint log contains original images of modified pages, it will grow in the presence of database updates. With copy specified, 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 if disk space in the destination directory is not an issue.

  • NO COPY clause   When you specify NO COPY, the checkpoint log is not copied as part of the backup. This option causes 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.

    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 clause   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 the 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).

  • AUTO clause   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 this option behaves as if copy were specified. Otherwise, it behaves as NO COPY. AUTO is the default behavior.

Remarks

The BACKUP statement performs a server-side backup. To perform a client-side backup, use the dbbackup utility. See Backup utility (dbbackup).

Each backup operation, whether image or archive, updates a history file called backup.syb. This file records the BACKUP and RESTORE operations that have been performed on a database server. For information about how the location of the backup.syb file is determined, see SALOGDIR environment variable.

Syntax 1 (image backup)   An image backup creates copies of each of the database files, in the same way as the Backup utility (dbbackup). By default, the Backup utility makes the backup on the client computer, but you can specify the -s option to create the backup on the database server when using the Backup utility. In the case of the BACKUP DATABASE statement, however, the backup can only be made on the database server.

Optionally, only the database file(s) or transaction log can be saved. The log may also be renamed or truncated after the backup has completed.

Alternatively, you can specify an empty string as a directory to rename or truncate the log without copying it first. This is particularly useful in a replication environment where space is a concern. You can use this feature with an event handler on transaction log size to rename the log when it reaches a given size, and with the delete_old_logs option to delete the log when it is no longer needed.

To restore from an image backup, copy the saved files back to their original locations and reapply the transaction logs as described in Recovering from multiple transaction logs.

Syntax 2 (archive backup)   An archive backup creates a single file holding all the required backup information. The destination can be either a file name or a tape drive device name.

There can be only one backup on a given tape. The tape is ejected at the end of the backup.

Only one archive per tape is allowed, but a single archive can span multiple tapes. To restore a database from an archive backup, use the RESTORE DATABASE statement.

If a RESTORE DATABASE statement references an archive file containing only a transaction log, the statement must specify a file name for the location of the restored database file, even if that file does not exist. For example, to restore from an archive containing only a log to the directory C:\MYNEWDB, the RESTORE DATABASE statement is:

RESTORE DATABASE 'c:\mynewdb\my.db' FROM archive-root
Caution

Backup copies of the database and transaction log must not be changed in any way. If there were no transactions in progress during the backup, or if you specified BACKUP DATABASE WITH CHECKPOINT LOG RECOVER or WITH CHECKPOINT LOG NO COPY, you can check the validity of the backup database using read-only mode or by validating a copy of the backup database.

However, if transactions were in progress, or if you specified BACKUP DATABASE WITH CHECKPOINT LOG COPY, the database server must perform recovery on the database when you start it. Recovery modifies the backup copy, which is not desirable.

Permissions

Must have DBA, REMOTE DBA, or BACKUP authority.

Side effects

Causes a checkpoint.

See also
Standards and compatibility
  • SQL/2003   Vendor extension.

  • Windows Mobile   Only the BACKUP DATABASE DIRECTORY syntax (syntax 1 above) is supported on Windows Mobile.

Example

Back up the current database and the transaction log, each to a different file, and rename the existing transaction log. An image backup is created.

BACKUP DATABASE
DIRECTORY 'd:\\temp\\backup'
TRANSACTION LOG RENAME;

The option to rename the transaction log is useful especially in replication environments, where the old transaction log is still required.

Back up the current database and transaction log to tape:

BACKUP DATABASE
TO '\\\\.\\tape0';

Rename the log without making a copy:

BACKUP DATABASE DIRECTORY ' '
TRANSACTION LOG ONLY
TRANSACTION LOG RENAME;

Execute the BACKUP DATABASE statement with a dynamically-constructed directory name:

CREATE EVENT NightlyBackup
SCHEDULE 
START TIME '23:00' EVERY 24 HOURS
HANDLER
BEGIN
    DECLARE dest LONG VARCHAR;
    DECLARE day_name CHAR(20);
    
    SET day_name = DATENAME( WEEKDAY, CURRENT DATE );
    SET dest = 'd:\\backups\\' || day_name;
            BACKUP DATABASE DIRECTORY dest
    TRANSACTION LOG RENAME;
END;