Database Restore

Interactive SQL supports two RESTORE DATABASE syntax variations.

Syntax 1:

RESTORE DATABASE 'dbfile'     
  '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' ...

For this backup statement:

BACKUP DATABASE READONLY DBSPACES iq_main
TO '/system1/IQ16/demo/backup/iqmain'

You can use either of one these RESTORE DATABASE commands to restore the dbspace iq_main :

//syntax 1
RESTORE DATABASE 'iqdemo.db' READONLY DBSPACES iq_main
FROM '/system1/IQ16/demo/backup/iqmain'

//syntax 2
RESTORE DATABASE 'iqdemo'
FROM '/system1/IQ16/demo/backup/iqmain'

The dbfile declaration in Syntax 1 refers to the location of the catalog store. The dbfile declaration can include a full or relative path to the location of the file.

Guidelines

To restore an inconsistent database, or to move the database to a new location, restore from a FULL backup.
To restore a database to the state before you performed any incremental backups or if your most recent backup is a FULL backup, restore the FULL backup only.
To recover from a database failure when a INCREMENTAL_SINCE_FULL backup is available, restore the last FULL backup first, then restore the INCREMENTAL_SINCE_FULL backup.
If no INCREMENTAL_SINCE_FULL backup is available, but you performed one or more INCREMENTAL backups since your last FULL backup, restore the FULL backup first, then restore the INCREMENTAL backups in order.

Restrictions

Depending on the type of backup you plan to restore, you may need to delete some objects and verify others.

Restore Option	Description
Full	Delete or move the catalog store (.db) , IQ store files (.iq), transaction log (.log), and user-defined stores. If any of these files reside in the target directory, RESTORE DATABASE generates an exception and does not restore your files.
Incremental	The catalog store must reside in the target directory. If the catalog does not exist, run a full restore to restore the catalog, then perform the incremental restore. The database must not have changed since the last restore. The catalog store and IQ store must match the files they replace on the file system. These restrictions apply to all types of incremental restores.

Restore Option

Description

Full

Delete or move the catalog store (.db) , IQ store files (.iq), transaction log (.log), and user-defined stores.

If any of these files reside in the target directory, RESTORE DATABASE generates an exception and does not restore your files.

Incremental

The catalog store must reside in the target directory. If the catalog does not exist, run a full restore to restore the catalog, then perform the incremental restore.

The database must not have changed since the last restore. The catalog store and IQ store must match the files they replace on the file system. These restrictions apply to all types of incremental restores.

Database Status

In almost all cases, the database must not be running while you restore all files from a backup. This applies to FULL, INCREMENTAL SINCE FULL, or INCREMENTAL backups, as well as to READWRITE FILES ONLY backups.

To restore a backup of read-only files, the database may or may not be running. To restore specific files in a read-only dbspace, the dbspace must be offline.
To restore read-only files in a read-write dbspace, the dbspace can be online or offline. RESTORE DATABASE closes the read-only files, restores the files, then reopens the files.
To restore read-only files or dbspaces, the database may be running. The read-only file pathnames not not need match the names in the backup, if they otherwise match the database system table information.

Disk Files

Specify the same number of disk files to restore the database that you used to create the backup.

Tape Sets

Position the tapes to the correct starting point before you place them in the tape device. Keep track of the order of tapes in each backup tape set, that is, the set of tapes produced in a given backup on a given archive device.

Restore the tape set that contains the backup of the catalog store first, and it must be on the first archive device. Restore all tapes in order. Do not interleave sets. Restore all the tapes in one set before you restore another. After the first set, the order in which sets are restored does not matter, as long as the order is correct within each set. Use the same number of drives to restore the tapes as you used to produce the backup, so that you do not accidentally interleave tapes from different sets.

Utility Database

All RESTORE DATABASE commands are executed from the utility database (utility_db), and requires exclusive access to the database.

Use the -gd DBA and -gm 1 parameters to start the server. The -gd DBA parameter sets the privileges required to start or stop a database on a running server to users with SERVER OPERATOR privileges. The -gm 1 parameter limits the number of concurrent connections to a single connection plus one DBA connection above the limit. This allows a user with DROP CONNECTION privileges to connect to the server and drop other connections.

Shutdown the database server.
Start a server that you can use to connect to the utility database:
```
start_iq -su mypwd -gd DBA -gm 1 -n my_server
```
Start dbisql and connect to the utility database:
```
dbisql -c "UID=DBA;PWD=mypwd;DBN=utility_db"
```
Run all RESTORE DATABASE commands from dbisql. On Windows, you do not need to redouble the backslashes in path names for tape devices for restore, as you did for BACKUP DATABASE.
Shutdown the server and utility database.
Start the database server normally.

When SAP Sybase IQ completes the restore, the database is left in the state that existed at the end of the first implicit CHECKPOINT of the backup you restored.

Database Validation

To ensure that you restored a tape set in the correct order, run sp_iqcheckdb. For incremental backups, run sp_iqcheckdb after you restore each backup. To save time, however, you can run sp_iqcheckdb only after you restore the last incremental backup.