BACKUP Statement

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 transaction log without first making a copy of it. Do not use this clause if you are using database mirroring.

WAIT BEFORE START clause

This clause delays the backup until there are no active transactions. All other activity on the database is prevented and a checkpoint is performed.

Using this clause with the WITH CHECKPOINT LOG NO COPY clause verifies that the backup copy of the database does not require recovery and allows you to start the backup copy of the database in read-only mode and validate it. When you validate the backup database, you do not need to make an additional copy of the database.

WAIT AFTER END clause

This clause ensures that all transactions are completed before the transaction log is renamed or truncated. The database server waits for other connections to commit or rollback any open transactions before finishing the backup. Use this clause with caution as new, incoming transactions can cause the backup to wait indefinitely.

DBFILE ONLY clause

This clause makes backup copies of the main database file and all associated dbspaces, but not the transaction log. You cannot use the DBFILE ONLY clause with the TRANSACTION LOG RENAME or TRANSACTION LOG TRUNCATE clauses.

TRANSACTION LOG ONLY clause

You can specify the TRANSACTION LOG ONLY clause to create a backup copy of the transaction log, without copying the other database files.

TRANSACTION LOG RENAME [ MATCH ] clause

This clause causes the database server to rename the current transaction log to a file name of the form <YYMMDDnn.log> and start a new transaction log that has the same name as the database file. The backup transaction log gets the same name as the active transaction log unless MATCH is specified. If MATCH is specified, the backup copy of the transaction log gets the same name as the renamed file (<YYMMDDnn.log>). Using the MATCH keyword enables the same statement to be executed several times without writing over old data.

The transaction log can be renamed and restarted without completing a backup by specifying an empty directory name with the TRANSACTION LOG ONLY clause. For example:

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

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.

The transaction log can be truncated without completing a backup by specifying an empty directory name with the TRANSACTION LOG ONLY clause. For example:

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

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

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

This clause enables or disables backup history. By default, this clause is ON, meaning that each backup operation appends a line to the backup.syb file. Specifying HISTORY OFF prevents updates to the backup.syb file, and is recommended when:

• The database is backed up frequently.
• There is no procedure in place to periodically archive or delete the backup.syb file.
• Disk space is limited.

AUTO TUNE WRITERS clause

Specifying this clause enables or disables the automatic tuning of writers. During the backup process, one writer writes the backup files to the backup directory. If the backup directory is on a device that can handle an increased writer load (such as a RAID array), the default AUTO TUNE WRITERS ON improves overall backup performance by increasing the number of writers. The database server periodically examines the read and write performances of all devices that are participating in the backup. Specifying AUTO TUNE WRITERS OFF prevents the database server from creating additional writers.

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 DATABASE 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 grows 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 are 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.

FREE PAGE ELIMINATION clause

By default, archive backups skip some free pages, which can result in smaller and potentially faster backups. Free page elimination has no effect on the back up of transaction log files because transaction log files do not contain free pages. Databases with large transaction log files may not benefit as much from free page elimination as databases with small transaction log files.

When you back up a strongly encrypted database with free page elimination turned on, you must specify the encryption key when restoring the database. When you back up a strongly encrypted database with free page elimination turned off, you do not need to specify the encryption key when restoring the database.

As of version 12, you cannot restore archive backups created with version 11 or earlier database servers.

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

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.

To create a backup that can be started on a read-only server without having to go through recovery, you must use both the WAIT BEFORE START and WITH CHECKPOINT LOG NO COPY clauses. The WAIT BEFORE START clause ensures that the rollback log is empty, and the WITH CHECKPOINT LOG NO COPY clause ensures that the checkpoint log is empty. If either of these files is missing, then recovery is required. You can use WITH CHECKPOINT LOG RECOVER as an alternative to the WAIT BEFORE START and WITH CHECKPOINT LOG NO COPY clauses if you do not need to recover the database you backed up.

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. For 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 transaction 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 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 transaction log when it reaches a given size, and with the delete_old_logs option to delete the transaction 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.

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 that only contains a transaction log to the directory C:\MYNEWDB, the RESTORE DATABASE statement is:

RESTORE DATABASE 'c:\\temp\\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.

During the execution of this statement, you can request progress messages.

You can also use the Progress connection property to determine how much of the statement has been executed.

You must have the BACKUP DATABASE system privilege.

Causes a checkpoint.

SQL/2008

Vendor extension.