BACKUP DATABASE Statement

Quick Links:

Syntax

BACKUP DATABASE
   [ backup-option … ]
   TO archive_device [ archive-option... ]
   … [ WITH COMMENT string ]

backup-option - (back to Syntax)
   { READWRITE FILES ONLY | 
   READONLY dbspace-or-file [, … ] }
   CRC { ON | OFF }
   ATTENDED { ON | OFF }
   BLOCK FACTOR integer
   { FULL | INCREMENTAL | INCREMENTAL SINCE FULL }
   VIRTUAL { DECOUPLED |  
   ENCAPSULATED ‘shell_command’ } 
   WITH COMMENT comment

dbspace-or-file - (back to backup-option)
   { DBSPACES identifier-list | FILES identifier-list }

identifier-list - (back to dbspace-or-file)
   identifier [, … ]

archive-option - (back to Syntax) 
   SIZE integer STACKER integer

Parameters

(back to top)

TO – specify 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 use 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 SAP Sybase IQ attempts with regard to output devices.

WITH COMMENT – specify an optional comment recorded in the archive file and in the backup history file. Maximum length is 32KB. If you do not specify a value, a NULL string is stored.

READWRITE FILES ONLY – restricts FULL, INCREMENTAL, and INCREMENTAL SINCE FULL backups to only the set of read-write files in the database. The read-write dbspaces/files must be SAP Sybase IQ dbspaces.
If READWRITE FILES ONLY clause is used with an INCREMENTAL or INCREMENTAL SINCE FULL backup, the backup will not back up data on read-only dbspaces or dbfiles that has changed since the depends-on backup. If READWRITE FILES ONLY is not specified for an INCREMENTAL or INCREMENTAL SINCE FULL backup, the backup backs up all database pages that have changed since the depends-on backup, both on read-write and read-only dbspaces.

CRC – 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 – applies only when backing up to a tape device. If ATTENDED ON clause (the default) is used, a message is sent to the application that issued the BACKUP DATABASE statement if the tape drive requires intervention. This might happen, for example, when a new tape is required. If you specify OFF, BACKUP DATABASE does not prompt for new tapes. If additional tapes are needed and OFF has been specified, SAP Sybase IQ gives an error and aborts the BACKUP DATABASE command. However, a short delay is included to account for the time an automatic stacker drive requires to switch tapes.

BLOCK FACTOR integer – specify the number of blocks to write at one time. The value must be greater than 0, or SAP 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. Set BLOCK FACTOR to at least 25.

FULL | INCREMENTAL | INCREMENTAL SINCE FULL –
- FULL – specify a full backup; all blocks in use in the database are saved to the archive devices. This is the default action.
- INCREMENTAL – specify an incremental backup; all blocks changed since the last backup of any kind are saved to the archive devices. The keyword INCREMENTAL is not allowed with READONLY FILES.
- INCREMENTAL SINCE FULL – specify an incremental backup; all blocks changed since the last full backup are saved to the archive devices.
VIRTUAL DECOUPLED – specify a decoupled virtual backup. For the backup to be complete, you must copy the SAP Sybase IQ dbspaces after the decoupled virtual backup finishes, and then perform a nonvirtual incremental backup.
VIRTUAL ENCAPSULATED – specify 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. For security reasons, it is recommended that an absolute path be specified in the 'shell-command,' and file protections on that directory be in place to prevent execution of an unintended program.

SIZE – Specify 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), although in general, less than 1GB is inappropriate. For example, for a 3.5GB tape, specify 3500000. Defaults are by platform and medium. The final size of the backup file will not be exact, because backup writes in units of large blocks of data.

BACKUP DATABASE default sizes
Platform	Default SIZE for Tape	Default SIZE for Disk
UNIX	none	2GB
Windows	1.5GB SIZE must be a multiple of 64. Other values are rounded down to a multiple of 64.	1.5GB

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 DATABASE does one of the following:

If the device is a file system device, BACKUP DATABASE closes the current file and creates another file of the same name, with the next ascending number appended to the file name, for example, bkup1.dat1.1, bkup1.dat1.2, bkup1.dat1.3.
If the device is a tape unit, BACKUP DATABASE closes the current tape and you need to mount another tape.

STACKER – specify that the device is automatically loaded, and specifies the number of tapes with which it is loaded. This value is not the tape position in the stacker, which could be zero. When ATTENDED is OFF and STACKER is ON, SAP Sybase IQwaits for a predetermined amount of time to allow the next tape to be autoloaded. The number of tapes supplied along with the SIZE clause are 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.

Examples

(back to top)

Example 1 – this UNIX example backs up the iqdemo 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 DATABASE, 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
```
Note: Size units are kilobytes (KB), although in most cases, size of less than 1GB are inappropriate. In this example, the specified sizes are 10GB and 15GB.

Example 2 – these BACKUP DATABASE commands specify read-only files and dbspaces:

BACKUP DATABASE READONLY DBSPACES dsp1
TO '/dev/rmt/0'

BACKUP DATABASE READONLY FILES dsp1_f1, dsp1_f2
TO 'bkp.f1f2'

BACKUP DATABASE READONLY DBSPACES dsp2, dsp3
READONLY FILES dsp4_f1, dsp5_f2
TO 'bkp.RO'

Usage

(back to top)

The SAP Sybase IQ database might be open for use by many readers and writers when you execute a BACKUP DATABASE command. It acts as a read-only user and relies on the Table Level Versioning feature of SAP Sybase IQ to achieve a consistent set of data.

BACKUP DATABASE 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, SAP Sybase IQ does not allow any metadata changes to the database (such as adding or dropping columns and tables). Correspondingly, a later RESTORE DATABASE of the backup restores only up to that initial CHECKPOINT.

The BACKUP DATABASE command lets you specify full or incremental backups. You can choose two kinds of incremental backups. INCREMENTAL backsup only those blocks that have changed and committed since the last backup of any type (incremental or full). INCREMENTAL SINCE FULL backs up 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 DATABASE commands, but slower and more complicated for RESTORE DATABASE 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, a user with the SERVER OPERATOR system privilege must restore the full backup archive first, and then each incremental archive in the proper order. (SAP Sybase IQ keeps track of which ones are needed.) The second type requires the user with the SERVER OPERATOR system privilege 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 DATABASE statement.

Although you can perform an OS-level copy of tablespaces to make a virtual backup of one or more read-only dbspaces, use the virtual backup statement, because it records the backup in the SAP Sybase IQ system tables.

BACKUP DATABASE and RESTORE DATABASE write your SAP Sybase IQ data in parallel to or from all of the archive devices you specify. The catalog store is written serially to the first device. Faster backups and restores result from greater parallelism.

SAP Sybase IQ supports a maximum of 36 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 DATABASE command to avoid creating multiple files per backup device and consider the value used in the BLOCK FACTOR clause on the BACKUP DATABASE command.

BACKUP DATABASE overwrites existing archive files unless you move the old files or use a different archive_device name or path.

The backup API DLL implementation lets you specify arguments to pass to the DLL when opening an archive device. For third-party implementations, the archive_device string has this format:

'DLLidentifier::vendor_specific_information'

A specific example:

'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 contain only 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 DATABASE command when using third-party implementations, as that information should be encoded in the vendor_specific_information portion of the string.

Note: Only certain third-party products are certified with SAP Sybase IQ using this syntax. See the Release Bulletin for additional usage instructions or restrictions. Before using any third-party product to back up your SAP Sybase IQ database in this way, make sure it is certified. See the Release Bulletin, or see the SAP Sybase Certification Reports for the SAP Sybase IQ product in Technical Documents at http://www.sybase.com/support/techdocs/.

For the SAP Sybase IQ implementation of the backup API, you need to specify only the tape device name or file name. For disk devices, you should also specify the SIZE value, or SAP Sybase IQ assumes that each created disk file is no larger than 2GB on UNIX, or 1.5GB on Windows.

An example of an archive device for the SAP Sybase API DLL that specifies a tape device for certain UNIX systems is:

'/dev/rmt/0'

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 DATABASE distributes the information across all devices. Other issues for BACKUP DATABASE include:

BACKUP DATABASE does not support raw devices as archival devices.
Windows systems support only fixed-length I/O operations to tape devices (for more information about this limitation, see your Installation and Configuration Guide). Although Windows supports tape partitioning, SAP Sybase IQ does not use it, so do not use another application to format tapes for BACKUP DATABASE. 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) SAP 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, SAP Sybase IQ interprets this name as a disk file name.
SAP Sybase IQ does not rewind tapes before using them. You must ensure the tapes used for backup and restore are at the correct starting point before putting them in the tape device. SAP Sybase IQ does rewind tapes after using them on rewinding devices.
During backup and restore operations, if SAP Sybase IQ cannot open the archive device (for example, when it needs the media loaded) and the ATTENDED clause is ON, it waits for ten seconds and tries again. It continues these attempts indefinitely until either it is successful or the operation is terminated with a Ctrl+C.
If you enter Ctrl+C, BACKUP DATABASE fails and returns 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.

Side effects:

Automatic commit

Standards

(back to top)

SQL—Vendor extension to ISO/ANSI SQL grammar.
SAP Sybase Database product—Not supported by SAP Sybase Adaptive Server® Enterprise.

Permissions

(back to top)

Requires one of:

BACK UP DATABASE system privilege.
You own the database.