ASA SQL Reference
SQL Statements
Use this statement to back up a database and transaction log.
BACKUP DATABASE
DIRECTORY backup-directory
[ WAIT BEFORE START ]
[ WAIT AFTER END ]
[ DBFILE ONLY ]
[ TRANSACTION LOG ONLY ]
[ TRANSACTION LOG RENAME [ MATCH ] ]
[ TRANSACTION LOG TRUNCATE ]
backup-directory : string
BACKUP DATABASE TO archive-root
[ ATTENDED { ON | OFF } ]
[ WITH COMMENT comment string ]
archive-root : string
comment-string : string
backup-directory The target location on disk for those files, relative to the server's current directory at startup. If the directory does not already exist, it is created. Specifying an empty string as a directory allows you to rename or truncate the log without making a copy of it first.
WAIT BEFORE START clause This clause ensures 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, the customer can avoid making an additional copy of the database.
WAIT AFTER END clause This clause may be used 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 This clause may be used to cause backup copies of the main database file and any associated dbspaces to be made. The transaction log is not copied.
TRANSACTION LOG ONLY clause This clause may be used 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 will cause the server to rename current transaction log at the completion of the backup. The name for the renamed copy will be of the form YYMMDDnn.log. 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.
archive-root 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 Windows NT or NetWare, the first tape drive is \\.\tape0.
The backslash ( \ ) is an escape character in SQL strings, so each backslash must be doubled. For more information on escape characters and strings, see Strings.
ATTENDED 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 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.
Each BACKUP operation, whether image or archive, updates a history file called backup.syb. This file is stored in the same directory as the database server executable.
WITH COMMENT Record a comment in the archive file and in the backup history file.
The first syntax is an image backup and the second syntax is an archive backup.
Syntax 1 An image backup creates copies of each of the database files, in the same way as the Backup utility (dbbackup). In the case of the BACKUP statement, however, the backup is made on the server, while the Backup utility makes the backup from a client machine.
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 transaction logs as described in the chapter Backup and Data Recovery.
Syntax 2 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. Archive backups to tape are not supported on versions of NetWare earlier than NetWare 5.
There can be only one backup on a given tape. The file backup.syb records the BACKUP and RESTORE operations that have been performed on a given server.
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.
Dynamically constructed filenames You can execute a BACKUP statement with a dynamically constructed filename by dynamically constructing the entire statement, then executing it using the EXECUTE IMMEDIATE statement.
Must have DBA authority.
Causes a checkpoint.
EXECUTE IMMEDIATE statement [SP]
SQL/92 Vendor extension.
SQL/99 Vendor extension.
Sybase Not compatible with Adaptive Server Enterprise.
Windows CE Only the BACKUP DATABASE DIRECTORY syntax (syntax 1 above) is supported on the Windows CE platform.
Back up the current database and the transaction log to a file, renaming 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 statement with a dynamically-constructed filename, via the EXECUTE IMMEDIATE statement:
CREATE EVENT NightlyBackup
SCHEDULE
START TIME '23:00' EVERY 24 HOURS
HANDLER
BEGIN
DECLARE cmd LONG VARCHAR;
DECLARE day_name CHAR(20);
SET day_name = DATENAME(WEEKDAY,CURRENT DATE);
SET cmd = 'BACKUP DATABASE DIRECTORY ' ||
'''d:\\backups\\' || day_name || ''' ' ||
'TRANSACTION LOG RENAME';
EXECUTE IMMEDIATE WITH ESCAPES OFF cmd;
END