Restores an IQ database backup from one or more archive devices.
RESTORE DATABASE 'db_file' FROM 'archive_device' [ FROM 'archive_device' ]... ...[KEY key_spec] ... [ RENAME dbspace-name TO 'new-dbspace-path']... ... [ CATALOG ONLY ]
relative or absolute path of the database to be restored. Can be the original location, or a new location for the Catalog Store file.
quoted string including mixed cases, numbers, letters, and special characters. It may be necessary to protect the key from interpretation or alteration by the command shell.
The following UNIX example restores the asiqdemo database from 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. To specify this feature with RESTORE, use the naming convention appropriate for your UNIX platform. (Windows does not support this feature.)
RESTORE DATABASE 'asiqdemo' FROM '/dev/rmt/0n' FROM '/dev/rmt/2n'
The following example restores an encrypted database named marvin that was encrypted with the key is!seCret.
RESTORE DATABASE 'marvin' FROM 'marvin_bkup_file1' FROM 'marvin_bkup_file2' FROM 'marvin_bkup_file3' KEY 'is!seCret'
The RESTORE command requires exclusive access by the DBA to the database. This exclusive access is achieved by setting the -gd switch to DBA, which is the default when you start the server engine. You issue the RESTORE command before you start the database (you must be connected to the utility_db database). Once you finish specifying RESTORE commands for the type of backup, that database is ready to be used. The database is left in the state that existed at the end of the first implicit CHECKPOINT of the last backup you restored. You can now specify a START DATABASE to allow other users to access the restored database.
IQ 12.6 can only restore databases created using IQ
12.4.3 and higher versions. If the database was created using a
12.x version prior to 12.4.3, you must upgrade it to 12.4.3 or greater
before backup. For instructions, see the Adaptive Server
IQ Installation and Configuration Guide for the version
to which you are upgrading.
When restoring to a raw device, you need to make sure
that the device is large enough to hold the dbspace you are restoring.
IQ RESTORE checks the raw device size and returns an error, if the
raw device is not large enough to restore the dbspace. For more
information, see “Restoring
to a raw device” in Chapter 14, “Backup
and Data Recovery” of the Sybase IQ System Administration Guide.
The BACKUP command allows you to specify full or incremental backups. You can choose two kinds of incremental backups. INCREMENTAL only backs up 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. If a RESTORE of a full backup is followed by one or more incremental backups (of either type), no modifications to the database are allowed between successive RESTORE commands. This rule prevents a RESTORE from incremental backups on a database in need of crash recovery, or one that has been modified. You can still overwrite such a database with a RESTORE from a full backup.
Before starting a full restore you must delete two files: the Catalog Store file (default name dbname.db) and the transaction log file (default name dbname.log).
If you restore an incremental backup, RESTORE ensures that backup media sets are accessed in the proper order. This order is to restore the last full backup tape set first, then the first incremental backup tape set, then the next most recent set, and so forth until the most recent incremental backup tape set. If the DBA produced an INCREMENTAL SINCE FULL backup, only the full backup tape set and the most recent INCREMENTAL SINCE FULL backup tape set is required; however, if there is an INCREMENTAL made since the INCREMENTAL SINCE FULL, it also must be applied.
Sybase IQ ensures that the restoration order is appropriate, or it gives an error. Any other errors that occur during the restore will result in the database being marked corrupt and unusable. To clean up such a corrupt database, you need to do a RESTORE from a full backup, potentially followed by any additional incremental backups. Since the corruption probably happened with one of those backups, you may need to ignore a later backup set and use an earlier set.
FROM clause Specifies the name of the archive_device from which you are restoring, delimited with single quotation marks. If you are using multiple archive devices, specify them using separate FROM clauses. (A comma-separated list is not allowed.) Archive devices must be distinct. The number of FROM clauses determines the amount of parallelism IQ attempts with regard to input devices.
The backup/restore 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.
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/restore API, you do not have to specify this other information, just the tape device name or filename. However, if you use disk devices, you must specify the same number of archive_devices on the RESTORE as given on the backup. (Otherwise, you can have a different number of restoration devices from the number used to perform the backup.) A specific example of an archive device for the Sybase API DLL that specifies a non-rewinding tape device for a UNIX system is:
'/dev/rmt/0n'
RENAME clause Allows you to restore one or more IQ database files to a new location. Specify each dbspace-name you are moving as it appears in the SYSFILE table. Specify new-dbspace-path as the new raw partition, or the new full or relative pathname, for that dbspace.
If relative paths were used to create the database files, the files are restored by default relative to the Catalog Store file (the SYSTEM dbspace), and a rename clause is not required. If absolute paths were used to create the database files and a rename clause is not specified for a file, it will be restored to its original location.
Relative pathnames in the RENAME clause work as they do when you create a database or dbspace: the main IQ Store dbspace, Temporary Store dbspaces, and Message Log are restored relative to the location of db_file (the Catalog Store); user-created IQ Store dbspaces are restored relative to the directory that holds the main IQ dbspace.
Do not use the RENAME clause to move the SYSTEM dbspace, which holds the Catalog Store. To move the Catalog Store, and any files created relative to it and not specified in a RENAME clause, specify a new location in the db_file parameter.
CATALOG ONLY option Restores only the backup header record from the archive media.
Note other RESTORE issues:
RESTORE to disk does not support raw devices as archival devices.
Sybase IQ does not rewind tapes before using them; on rewinding tape devices, it does rewind tapes after using them. You must position each tape to the start of the IQ data before starting the RESTORE.
During BACKUP and RESTORE operations, if IQ cannot open the archive device (for example, when it needs the media loaded) and the ATTENDED option is ON, it waits for ten seconds for you to put the next tape in the drive, and then tries again. It continues these attempts indefinitely until either it is successful or the operation is terminated with a Ctrl-C.
If you enter a Ctrl-C, RESTORE fails and returns the database to its state before the restoration began.
If disk striping is used, the striped disks are treated as a single device.
The maximum size for a complete RESTORE command, including all clauses, is 32KB.
None.
Must have DBA authority.
