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 default), the database server periodically examines the read and write performance from all 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.

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 for image backups and COPY for archive backups.

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 entire checkpoint log and the system dbspace are copied to the backup directory. The next time the database server 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.

MAX WRITE clause   For archive backups, by default one thread is dedicated to writing the backup files. 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 AUTO is specified, one output stream is created for each reader thread. The value n specifies the maximum number of output streams that can be created, up to the number of reader threads. The default value for this clause is 1. If you are backing up to tape, only one writer can be used.

The first stream, stream 0, produces files named myarchive.X, where X is a number that starts at 1 and continues incrementing to the number of files required. All of the other streams produce files named myarchive.Y.Z, where Y is the stream number (starting at 1), and Z is a number that starts at 1 and continues incrementing to the number of files required.

SQL/2003   Vendor extension.

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