BACKUP statement

Description

Backs up a Sybase IQ database on one or more archive devices.

Syntax

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

Parameters

Examples

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, 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

Example 2

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

The IQ database might be open for use by many readers and writers when you execute a BACKUP command. It acts as a read-only user and relies 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 restores only up to that initial CHECKPOINT.

The BACKUP command lets you specify full or incremental backups. You can choose two kinds of incremental backups. INCREMENTAL backs up 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 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 must RESTORE the full backup archive first, and then each incremental archive in the proper order. (Sybase IQ keeps track of which ones are needed.) The second type requires 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.

The keyword INCREMENTAL is not allowed with READONLY FILES.

BACKUP and RESTORE write your 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.

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 command to avoid creating multiple files per backup device and consider the value used in the BLOCK FACTOR clause on the BACKUP command.

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

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.

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, BACKUP 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 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.

Other issues for BACKUP include:

• BACKUP 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, 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. Sybase IQ 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 continues these attempts indefinitely until either it is successful or the operation is terminated with a Ctrl+C.

• If you enter Ctrl+C, BACKUP 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.

• If you are recovering a SQL Anywhere database, see “Backup and Data Recovery” in SQL Anywhere Server – Database Administration > Maintaining Your Database for additional options.

Automatic commit.

Standards

Permissions

Must be the owner of the database or have DBA authority. Users without DBA authority require OPERATOR authority.

See also