Backs up a database and transaction log.
Syntax 1 - Image backupBACKUP DATABASE DIRECTORY backup-directory [ backup-option [ backup-option ... ]
backup-directory : { string | variable }backup-option : 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 }
Syntax 2 - Archive backupBACKUP DATABASE TO archive-root
[ backup-option [ backup-option ... ]archive-root : { string | variable }backup-option : | 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 } | WITH CHECKPOINT LOG [ NO ] COPY | MAX WRITE { number-of-writers | AUTO } | FREE PAGE ELIMINATION { ON | OFF }
comment-string : string
number-of-writers : integer
ParametersDIRECTORY 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 Use this clause to ensure that the rollback log for each connection in the backup copy of the database is empty. Use this clause with the WITH CHECKPOINT LOG NO COPY clause to verify that the backup copy of the database does not contain any information required for recovery.
If you use the WAIT BEFORE START and WITH CHECKPOINT LOG NO COPY clauses to complete a backup, 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.
When this clause is specified, the backup is delayed until there are no active transactions. All other activity on the database is prevented and a checkpoint is performed to ensure that the backup copy of the database does not require recovery. When the checkpoint is complete, other activity on the database resumes.
WAIT AFTER END clause When renaming or truncating the transaction log you can specify the WAIT AFTER END clause to ensure that all transactions are completed before the log is renamed or truncated. If you specify this clause, the backup waits for other connections to commit or rollback any open transactions before finishing. This clause should be used with caution because new, incoming transactions may cause the backup to wait indefinitely.
DBFILE ONLY clause When you specify the DBFILE ONLY clause, backup copies of the main database file and all associated dbspaces are made, but 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 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 at the completion of the backup. If the MATCH keyword is omitted, the backup copy of the transaction log has 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.
The transaction log can be renamed 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 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:
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 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 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.
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.
RemarksThe 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. For information about how the location of the backup.syb file is determined, see SALOGDIR environment variable.
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:\mynewdb\my.db' FROM archive-root |
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.
PermissionsMust have DBA, REMOTE DBA, or BACKUP authority.
Side effectsCauses a checkpoint.
See also Standards and compatibilitySQL/2008 Vendor extension.
Windows Mobile Only the BACKUP DATABASE DIRECTORY syntax (syntax 1) is supported on Windows Mobile.
ExampleBack 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 transaction 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; |
 |
Discuss this page in DocCommentXchange.
|
Copyright © 2012, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.1 |