Virtual Backups

Virtual backups perform a full backup of the catalog store and select SAP Sybase IQ metadata, and metadata not specific to individual tables, including information specific to the freelist, backup, and checkpoint. Virtual backups do not back up table data and metadata in the IQ store. Use the VIRTUAL backup parameters as part of a full database backup.

Virtual backups can quickly (in seconds or minutes) back up and restore an entire database minimizing downtime. SAN technologies create multiple mirrored copies of dbfile devices, which offload maintenance tasks, such as consistency checks, on the mirrored copies. Use the sp_iqfile procedure to create a backup list of files that comprise the database.

You must make a separate OS-level copy of the corresponding IQ store. To restore from a virtual backup, restore the corresponding system-level copy of the IQ store, then proceed with the IQ full restore of the virtual backup.

Encapsulated Virtual Backups

In an encapsulated virtual backup, the 'shell-command' parameter executes arbitrary shell commands as part of the backup operation:

BACKUP DATABASE 
    FULL VIRTUAL ENCAPSULATED 
     ' shell_command '  
    TO archive_device

The BACKUP DATABASE command backs up the catalog and metadata; the 'shell-command' executes an OS-level script or command that backs up the user dbspaces. Encapsulated means that the two phases are wrapped in a transactional boundary. In other words, the backup is complete and consistent–the catalog, metadata and user data all match.

Shell commands must perform correctly for system-level backups. If a shell command fails, the backup operation throws an exception. For system-level backups of table data to be consistent with the virtual backup without additional steps, the system-level backup must be made during the backup command and by the backup transaction.

This example runs the ‘dd’ command to copy iqdemo:

BACKUP DATABASE FULL VIRTUAL ENCAPSULATED 
'dd if=iqdemo.iq of=iqdemo.iq.copy' 
TO 'iqdemo.full'

To fully restore a database from an encapsulated virtual backup, restore the system-level backup first, then restore the virtual backup.

Decoupled Virtual Backups

A decoupled virtual backup copies all dbspaces after the backup operation is complete:

BACKUP DATABASE 
    FULL VIRTUAL DECOUPLED
    TO archive_device

Decoupled virtual backups allow users to make catalog changes before the BACKUP DATABASE command completes the full backup. To gain that flexibility, you must perform an incremental backup after the decoupled virtual backup is complete, to resynchronized the catalog and data.

If you perform a system-level backup outside of the backup transaction, the IQ store backup will not be consistent with the IQ backup file. However, a nonvirtual IQ incremental backup, together with the virtual full backup, represents a consistent database. This is because the IQ incremental backup copies all IQ store data and metadata that have changed during or since the virtual full backup. Even the automatic commit and checkpoint that are part of the backup command modify the IQ store, making an independent system-level backup inconsistent. Trying to use the database without applying the incremental restore gives unpredictable results.

To perform a full virtual decoupled backup, first perform the full backup:

BACKUP DATABASE
    FULL VIRTUAL DECOUPLED 
    TO 'iqdemo.full'

Then perform a nonvirtual incremental backup:

BACKUP DATABASE
    INCREMENTAL SINCE FULL 
    TO 'iqdemo.isf'

For a fully restored database from a system-level backup with a virtual decoupled backup, restore the system-level backup, then restore of the virtual decoupled backup followed by an incremental-since-full restore.

Virtual Backups with SAN Snapshot or Shadow Hardware

Storage area network (SAN) snapshot or shadow hardware provides more flexibility in the backup process by allowing the system-level backup to take place on the shadow copy rather than on the main database. Instead of the system-level copy that is part of the virtual backup, you can create a shadow copy of the IQ store. You can then perform a system-level backup against the shadow copy. This allows the full backup to complete quickly.