The TO archive_device clause indicates the destination disk file(s) or system tape drive(s) for the backup. You specify one TO archive_device clause for each destination file or device. At least one is required. BACKUP distributes output in parallel—that is, concurrently—across all of the devices you specify.
Sybase IQ supports a maximum of 32 hardware devices for backup. For faster backups, specifying one or two devices per core will help to avoid hardware and IO contention. Set the SIZE parameter on the BACKUP command to avoid creating multiple files per backup device and consider the value used in the BLOCK FACTOR clause on the BACKUP command. See Chapter 1, “SQL Statements,” in Reference: Statements and Options.
While BACKUP attempts to distribute information equally across multiple devices, there is no guarantee that this will happen.
BACKUP always assigns file names to disk backup files by appending a suffix to the archive_device name you specify. The suffix consists of “.” followed by a number that increases by one for each new file. For example, if you specify /iqback/mondayinc as the archive_device, the backup files are /iqback/mondayinc.1, /iqback/mondayinc.2, and so on. This convention allows you to store as large a backup as you need, while allowing you control over the file size; see the SIZE option for details. Your file system must support long file names to accommodate this convention.
You must make sure that the directory names you specify for the archive_device exist. BACKUP does not create missing directories. If you try to start a backup in a directory that does not exist, the backup fails.
You should avoid using relative pathnames to specify the location of disk files. BACKUP interprets the pathname as relative to the location where the server was started, which you may not be able to identify with certainty when you do a backup. Also, if there is data in other directories along the path, you may not have enough room for the backup.
BACKUP does not position tapes for you. You must position the tape appropriately before starting your backup, and be sure that you do not overwrite any of the backup if you use a rewinding tape device. For these reasons, Sybase recommends you use a non-rewinding tape device. See the operating system documentation for your platform for appropriate naming conventions.
Here are examples of how you specify non-rewinding tape devices on UNIX platforms:
On Sun Solaris platforms, insert the letter n for “no rewind” after the device name, for example, '/dev/rmt/0n'.
On IBM AIX platforms, use a decimal point followed by a number that specifies the appropriate compression with rewind setting, for example, '/dev/rmt0.1'.
On HP-UX platforms, use '0m' to specify the default tape mechanism and 'n' for “no rewind,” for example, '/dev/rmt/0mn'.
WARNING! If you misspell a tape device name and write a name that is not a valid tape device on your system, BACKUP assumes it is a disk file.
Windows systems do not specify rewind or no rewind devices and only support fixed-length I/O operations to tape devices. Sybase IQ requires variable-length devices. It does additional processing to accommodate fixed-length tape I/O on Windows systems.
While Windows supports tape partitioning, Sybase IQ does not use it, so do not use another application to format tapes for Sybase IQ backup or restore. On Windows, the first tape device is '\\.\tape0', the second is '\\.\tape1', and so on.
WARNING! For backup (and for most other situations) Sybase IQ treats the leading backslash in a string as an escape character, when the backslash precedes an n, an x, or another backslash. For this reason, when you specify backup tape devices you must double each backslash required by the Windows naming convention. For example, indicate the first Windows tape device you are backing up to as '\\\\.\\tape0', the second as '\\\\.\\tape1', and so on. If you omit the extra backslashes, or otherwise misspell a tape device name, and write a name that is not a valid tape device on your system, Sybase IQ interprets this name as a disk file name.
For more information about fixed-length I/O on Windows, see “Tuning backup operations” in the Performance and Tuning Guide.
The SIZE option of the TO clause identifies the maximum size of the backed up data on that stripe, in KB.
If you use the Sybase-provided backup (as opposed to a third party backup product), you should specify SIZE for unattended tape backups on platforms that do not reliably detect the end-of-tape marker. Note that the value of SIZE is per output device. No volume used on the corresponding device can be shorter than this value. Although IQ does not require you to specify SIZE for an attended tape backup, it is always best to supply an accurate size estimate.
During backup, if any tape runs out of space and you have not specified SIZE, you get an error. If any tape runs out of space before the specified size, you do not get an error immediately; instead, here is what happens:
For attended backups with SIZE and STACKER specified, Backup tries to open the next tape.
For attended backups with SIZE specified but not STACKER, Backup asks you to put in a new tape.
For unattended backups with SIZE and STACKER specified, Backup tries to open the next tape. If there are no volumes available, or if you did not specify STACKER, you get an error.
Any additional tapes do not contain the header information needed for a restore, so you must be careful to mount tapes in order during the restore or your database could become inconsistent.
On Windows, there are special requirements for the SIZE option on tape devices:
The value of SIZE must be a multiple of 64. Other values are rounded down to a multiple of 64.
If you do not specify SIZE explicitly, it is automatically set to 1.5GB.
The SIZE option of the TO clause identifies the maximum size of the backed up data on that stripe, in KB. Note that the value of SIZE is per output device.
If you use the Sybase-provided backup, either attended or unattended, specify SIZE if any disk file you name as an archive_device is larger than the default of 2GB (UNIX) or 1.5GB (Windows).
During backup, when the amount of information written to a given archive_device reaches SIZE, backup closes the current file and creates another one of the same name with the next ascending number appended to the file name.
For example, if you specify one archive_device, a disk file called janfull, and you specify SIZE 200000 for a maximum 200MB file, but your backup requires 2GB, then BACKUP creates ten 200MB files: janfull.1, janfull.2,...janfull.10. You must ensure that your disk can accommodate this much data before performing the backup.
The STACKER option of the TO clause indicates that you are backing up to an automatically loaded multitape stacker device, and specifies the number of tapes in that device. When ATTENDED is ON and STACKER is specified, BACKUP waits indefinitely for the next tape to be loaded. All tapes in a given stacker device must be the same size.
Do not specify SIZE or STACKER if
you are using a third party backup product, as size information
is conveyed in the vendor_specific_information string.
To specify this string, see “Performing backups with non-Sybase products”.
