RESTORE DATABASE Statement

Restores an SAP Sybase IQ database backup from one or more archive devices.

Quick Links:

Go to Parameters

Go to Examples

Go to Usage

Go to Standards

Go to Permissions

Syntax

Syntax 1

RESTORE DATABASE 'db_file'
    'archive_device' [ FROM 'archive_device' ]… 
   … [ CATALOG ONLY ]
   … [ KEY key_spec ]
   … [ [ RENAME logical-dbfile-name TO 'new-dbspace-path']...
       | VERIFY [ COMPATIBLE ] ]

Syntax 2

RESTORE DATABASE 'database-name' 
   [ restore-option ...]
   FROM 'archive_device...

restore-option
   READONLY dbspace-or-file [, … ]
   KEY key_spec
   RENAME file-name TO new-file-path ...

Parameters

(back to top)

Examples

(back to top)

Usage

(back to top)

The RESTORE DATABASE command requires exclusive access by a user with the SERVER OPERATOR system privilege 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.

Issue the RESTORE DATABASE command before you start the database (you must be connected to the utility_db database). Once you finish specifying RESTORE DATABASE 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.

The maximum size for a complete RESTORE DATABASE command, including all clauses, is 32KB.

When restoring to a raw device, make sure the device is large enough to hold the dbspace you are restoring. SAP Sybase IQ RESTORE DATABASE checks the raw device size and returns an error, if the raw device is not large enough to restore the dbspace.

BACKUP DATABASE allows you to specify full or incremental backups. There are 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 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 DATABASE 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 DATABASE ensures that backup media sets are accessed in the proper order. This order restores 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 a user with the SERVER OPERATOR system privilege 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 backup made since the INCREMENTAL SINCE FULL backup, it also must be applied.

SAP Sybase IQ ensures that the restoration order is appropriate, or it displays an error. Any other errors that occur during the restore results in the database being marked corrupt and unusable. To clean up a corrupt database, do a restore from a full backup, followed by any additional incremental backups. Since the corruption probably happened with one of those backups, you might need to ignore a later backup set and use an earlier set.

To restore read-only files or dbspaces from an archive backup, the database may be running and the administrator may connect to the database when issuing the RESTORE DATABASE statement. The read-only file pathname need not match the names in the backup, if they otherwise match the database system table information.

The database must not be running to restore a FULL, INCREMENTAL SINCE FULL, or INCREMENTAL restore of either a READWRITE FILES ONLY or an all files backup. The database may or may not be running to restore a backup of read-only files. When restoring specific files in a read-only dbspace, the dbspace must be offline. When restoring read-only files in a read-write dbspace, the dbspace can be online or offline. The restore closes the read-only files, restores the files, and reopens those files at the end of the restore.

You can use selective restore to restore a read-only dbspace, as long as the dbspace is still in the same read-only state.

Other RESTORE DATABASE issues:

  • RESTORE DATABASE to disk does not support raw devices as archival devices.
  • SAP 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 SAP Sybase IQ data before starting the restore.
  • 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 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 Ctrl+C.
  • If you press Ctrl+C, RESTORE DATABASE 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 file_name column in the SYSFILE system table for the SYSTEM dbspace is not updated during a restore. For the SYSTEM dbspace, the file_name column always reflects the name when the database was created. The file name of the SYSTEM dbspace is the name of the database file.

Standards

(back to top)

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

Permissions

(back to top)

The permissions required to execute this statement are set using the -gu server command line option, as follows:
  • NONENo user can issue this statement.
  • DBARequires the SERVER OPERATOR system privilege.
  • UTILITY_DBOnly those users who can connect to the utility_db database can issue this statement.
Related reference
BACKUP DATABASE Statement