Backs up an Sybase IQ database on one or more archive devices.
BACKUP DATABASE
... [ CRC { ON | OFF } ]
... [ ATTENDED { ON | OFF } ]
... [ BLOCK FACTOR integer ]
... [ { FULL | INCREMENTAL | INCREMENTAL SINCE FULL } ]
... [ { VIRTUAL DECOUPLED |
VIRTUAL ENCAPSULATED ‘shell_command’ } ]
...TO archive_device [ SIZE integer ] [ STACKER integer ] ...
... [ WITH COMMENT string ]
The following UNIX example backs up the asiqdemo database onto tape devices /dev/rmt/0 and /dev/rmt/2 on a Sun Solaris platform. On Solaris, the letter n after the device name specifies the “no rewind on close” feature. Always specify this feature with BACKUP, using the naming convention appropriate for your UNIX platform (Windows does not support this feature). This example backs up all changes to the database since the last full backup.
BACKUP DATABASE INCREMENTAL SINCE FULL TO '/dev/rmt/0n' SIZE 10000000 TO '/dev/rmt/2n' SIZE 15000000
SIZE units are kilobytes (KB). In this example, the
specified sizes are 10GB and 15GB.
The IQ database may be open for use by many readers and writers when you execute a BACKUP command. It will act as a read-only user and will rely on the Table Level Versioning feature of Sybase IQ to achieve a consistent set of data. BACKUP implicitly issues a CHECKPOINT prior to commencing, and then it backs up the catalog tables that describe the database (and any other tables you have added to the Catalog Store). During this first phase, Sybase IQ does not allow any metadata changes to the database (such as adding or dropping columns and tables). Correspondingly, a later RESTORE of the backup will only restore up to that initial CHECKPOINT.
The BACKUP command allows you to specify full or incremental backups. You can choose two kinds of incremental backups. INCREMENTAL will only backup those blocks that have changed and committed since the last BACKUP of any type (incremental or full). INCREMENTAL SINCE FULL will backup all of the blocks that have changed since the last full backup. The first type of incremental backup can be smaller and faster to do for BACKUP commands, but slower and more complicated for RESTORE commands. The opposite is true for the other type of incremental backup. The reason is that the first type generally results in N sets of incremental backup archives for each full backup archive. If a restore is required, the DBA will first RESTORE the full backup archive and then each incremental archive in the proper order. (Sybase IQ keeps track of which ones are needed.) The second type will require the DBA to restore only the full backup archive and the last incremental archive.
Incremental virtual backup is supported using the VIRTUAL DECOUPLED and VIRTUAL ENCAPSULATED parameters of the BACKUP statement.
CRC clause Activates 32–bit cyclical redundancy checking on a per block basis (in addition to whatever error detection is available in the hardware). When you specify this clause, the numbers computed on backup are verified during any subsequent RESTORE operation, affecting performance of both commands. The default is ON.
ATTENDED clause This clause applies only when backing up to a tape device. If ATTENDED ON (the default) is used, a message is sent to the application that issued the BACKUP statement if the tape drive requires intervention. This may happen, for example, when a new tape is required. If you specify OFF, BACKUP does not prompt for new tapes. If additional tapes are needed and OFF has been specified, Sybase IQ gives an error and aborts the BACKUP command. However, a short delay is included to account for the time an automatic stacker drive will require to switch tapes.
BLOCK FACTOR clause Specifies the number of blocks to write at one time. Its value must be greater than 0 or Sybase IQ generates an error message. Its default is 25 for UNIX systems and 15 for Windows systems (to accommodate the smaller fixed tape block sizes). This clause effectively controls the amount of memory used for buffers. The actual amount of memory is this value times the block size times the number of threads used to extract data from the database. Sybase recommends setting BLOCK FACTOR to at least 25.
FULL clause Specifies a full backup; all blocks in use in the database are saved to the archive device(s). This is the default action.
INCREMENTAL clause Specifies an incremental backup; all blocks changed since the last backup of any kind are saved to the archive device(s).
INCREMENTAL SINCE FULL clause Specifies an incremental backup; all blocks changed since the last FULL backup are saved to the archive device(s).
VIRTUAL DECOUPLED clause Specifies a decoupled virtual backup. For the backup to be complete, you must copy the IQ dbspaces after the decoupled virtual backup finishes and then perform a non-virtual incremental backup.
VIRTUAL ENCAPSULATED clause Specifies an encapsulated virtual backup. The ‘shell-command’ argument can be a string or variable containing a string that is executed as part of the encapsulated virtual backup. The shell commands execute a system-level backup of the IQ Store as part of the backup operation.
TO clause Specifies the name of the archive_device to be used for backup, delimited with single quotation marks. The archive_device is a file name or tape drive device name for the archive file. If you are using multiple archive devices, specify them using separate TO clauses. (A comma-separated list is not allowed.) Archive devices must be distinct. The number of TO clauses determines the amount of parallelism Sybase IQ attempts with regard to output devices.
BACKUP overwrites existing archive files unless you move the old files or use a different archive_device name or path.
The backup API DLL implementation allows you to specify arguments to pass to the DLL when opening an archive device. For third party implementations, the archive_device string has the following format:
'DLLidentifier::vendor_specific_information'
A specific example is:
'spsc::workorder=12;volname=ASD002'
The archive_device string length can be up to 1023 bytes. The DLLidentifier portion must be 1 to 30 bytes in length and can only contain alphanumeric and underscore characters. The vendor_specific_information portion of the string is passed to the third party implementation without checking its contents. Do not specify the SIZE or STACKER clauses of the BACKUP command when using third party implementations, as that information should be encoded in the vendor_specific_information portion of the string.
Only certain third party products are certified with Sybase IQ using
this syntax. See the Sybase IQ Release Bulletin for additional
usage instructions or restrictions. Before using any third party
product to back up your IQ database in this way, make sure it is
certified. See the Sybase IQ Release Bulletin, or see the Sybase
Certification Reports for the Sybase IQ product in Technical Documents.
For the Sybase implementation of the backup API, you do not have to specify this other information, just the tape device name or filename. For disk devices, you should also specify the SIZE value or Sybase IQ assumes that each created disk file will be no larger than 2GB on UNIX, or 1.5 GB on Windows. An example of an archive device for the Sybase API DLL that specifies a tape device for certain UNIX systems is:
'/dev/rmt/0'
SIZE clause Specifies maximum tape or file capacity per output device (some platforms do not reliably detect end-of-tape markers). No volume used on the corresponding device should be shorter than this value. This value applies to both tape and disk files but not third party devices. Units are kilobytes (KB) so, for example, for a 3.5GB tape you specify 3500000. Defaults are by platform and medium.
The SIZE parameter is per output device. SIZE does not limit the number of bytes per device; SIZE limits the file size. Each output device can have a different SIZE parameter. During backup, when the amount of information written to a given device reaches the value specified by the SIZE parameter, BACKUP does one of the following:
If the device is a file system device, then BACKUP closes the current file and creates another file of the same name, with the next ascending number appended to the filename, for example, bkup1.dat1.1, bkup1.dat1.2, bkup1.dat1.3.
If the device is a tape unit, then BACKUP closes the current tape and you need to mount another tape.
It is your responsibility to mount additional tapes if needed, or to ensure that the disk has enough space to accommodate the backup.
When multiple devices are specified, BACKUP distributes the information across all devices.
Platform |
Default SIZE for tape |
Default SIZE for disk |
|---|---|---|
UNIX |
none |
2GB |
Windows |
1.5GB
|
1.5GB |
STACKER clause Specifies that the device is automatically loaded, and specifies the number of tapes it is loaded with. This value is not the tape position in the stacker, which could be zero. When ATTENDED is OFF and STACKER is ON, Sybase IQ waits for a pre-determined amount of time to allow the next tape to be autoloaded. The number of tapes supplied along with the SIZE clause will be used to determine whether there is enough space to store the backed up data. Do not use this clause with third party media management devices.
WITH COMMENT clause Specifies an optional comment recorded in the archive file and in the backup history file. Maximum length is up to 32KB. If you do not specify a value, a NULL string is stored.
Other issues for BACKUP are these:
BACKUP does not support raw devices as archival devices.
Windows systems only support fixed-length I/O operations to tape devices (for more information about this limitation, see your Sybase IQ Installation and Configuration Guide). While Windows supports tape partitioning, Sybase IQ does not use it, so do not use another application to format tapes for BACKUP. Windows has a simpler naming strategy for its tape devices, where 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.
Sybase IQ does not rewind tapes before using them. You must ensure the tapes used for BACKUP or RESTORE are at the correct starting point before putting them in the tape device. It does rewind tapes after using them on rewinding devices.
During BACKUP and RESTORE operations, if Sybase IQ cannot open the archive device (for example, when it needs the media loaded) and the ATTENDED parameter is ON, it waits for ten seconds and tries again. It will continue these attempts indefinitely until either it is successful or the operation is terminated with a Ctrl-C.
If you enter a Ctrl-C, BACKUP will fail and return the database to the state it was in before the backup started.
If disk striping is used, such as on a RAID device, the striped disks are treated as a single device.
If you are recovering an Adaptive Server Anywhere database, see Backup and Data Recovery in Adaptive Server Anywhere Database Administration Guide for additional options.
None.
Must be the owner of the database or have DBA authority.
Chapter 14, “Backup and Data Recovery” in Sybase IQ System Administration Guide
