Reference Manual
CHAPTER 9. SQL Statements
To back up a database and transaction log.
BACKUP DATABASE
DIRECTORY backup_directory
[ DBFILE ONLY ]
[ TRANSACTION LOG ONLY ]
[ TRANSACTION LOG RENAME ]
[ TRANSACTION LOG TRUNCATE ]
BACKUP DATABASE TO archive_root
[ CRC { ON | OFF } ]
[ ATTENDED { ON | OFF } ]
[ WITH COMMENT comment string ]
archive_root: string
nnnn: integer
comment-string: string
Must have DBA authority.
None.
Image backup (syntax 1) creates copies of each of the database files, in the same way that the dbbackup utility does. In the case of the SQL 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.
To restore from an image backup, copy the saved files back to their original locations and reapply transaction logs as described in Backing up your database.
You can use archive backup to create a single file that hold all the required backup information. The destination can be either a file name or a tape drive.
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.
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.
Archive backup (syntax 2) creates an archive of the database on disk or tape containing all the database files. Archive backups are only supported on NT and Unix platforms.
To restore a database from an archive backup, use the RESTORE statement.
archive_root The file name or tape drive for the archive file. The archive format saves all the backup data inside a single file.
To back up to tape, you must specify the device name of the tape drive. For example, on NT the first tape drive is:
\\.\tape0
The '\' is an escape character in SQL strings, so each backslash must be doubled.
Back up the current database and the transaction log to a file, truncating and renaming the existing transaction log.
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.