RESTORE DATABASE Statement

Quick Links:

Syntax

Syntax 1

RESTORE DATABASE '<db_file>'
    [MULTIPLEX]'<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
   [MULTIPLEX]
   READONLY <dbspace-or-file> [, … ]
   KEY <key_spec>
   RENAME <file-name> TO <new-file-path> ...

Syntax 3

RESTORE DATABASE '< db_file >' 
   [ FROM '< file-name >' <...>
   [ USING LOG PATH  ' directory, [...]]
RECOVER UNTIL [ TIMESTAMP  <timestamptz> | OFFSET <log-offset> ] 
KEY <key_spec>
[ VERIFY COMPATIBLE ]

Parameters

(back to top)

db_file
relative or absolute path of the database to be restored. Can be the original location, or a new location for the catalog store file.
MULTIPLEX specifies that you are restoring a shared-nothing multiplex. All secondary nodes with primary or mirror DAS dbfiles must be started before you issue a separate RESTORE command with the MULTIPLEX keyword. All secondary nodes with primary DAS dbfiles search for <archive_device.<server-id>.
key_spec
quoted string including mixed cases, numbers, letters, and special characters. It might be necessary to protect the key from interpretation or alteration by the command shell.
FROM
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 SAP IQ attempts with regard to input devices.

The backup/restore 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 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 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.

Note Only certain third-party products are certified with SAP IQ using this syntax. See the Release Bulletin for additional usage instructions or restrictions. Before using any third-party product to back up your SAP IQ database, make sure it is certified. See the Release Bulletin, or see the SAP IQ Certification Reports for the SAP IQ product in Technical Documents.

For the SAP IQ implementation of the backup/restore API, you need not specify information other than the tape device name or file name. 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 may have a different number of restoration devices than the number used to perform the backup. A specific example of an archive device for the SAP IQ API DLL that specifies a nonrewinding tape device for a UNIX system is:
```
'/dev/rmt/0n'
```
USING LOG PATH directory
instructs server to search along multiple paths for point-in-time recovery logs:
```
USING LOG PATH ' <directory 1>,  <directory 2>, ... '
```
Use a comma as a delimiter between directory names. The log name does not need to be specified. If any required files are missing, the server reports an error.
RECOVER UNTIL . . .
recovers data from the recovery logs up to the date and time specified by the timestamp, or transaction log-offset:
```
RECOVER UNTIL [ TIMESTAMP <timestamptz> | OFFSET <log-offset> ] 
```
<timestamptz> is a TIMESTAMP WITH TIMEZONE data type. <logoffset> is an UNSIGNED BIGINT that represents a transaction log offset.
Restriction
- A dbspace that you create after enabling point-in-time recovery may only be recorded in the point-in-time recovery log. If this is the case, you cannot rename the dbspace during a RESTORE DATABASE RECOVER UNTIL operation.
- For RLV-enabled databases, attempting point in time recovery to a point beyond the last log backup causes the RESTORE DATABASE RECOVER UNTIL operation to fail. Always restore the RV dbspace files of the old database in the right location manually before you run the point-in-time recovery, so that the correct version of the RLV log is used and the data recovered is correct.
CATALOG ONLY restores only the backup header record from the archive media. Cannot be used with the MULTIPLEX keyword.
RENAME
restore one or more SAP 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 path name, 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 is restored to its original location.

Relative path names 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.
VERIFY [ COMPATIBLE ] directs the server to validate the specified SAP IQ database backup archives for a full, incremental, incremental since full, or virtual backup. The backup must be SAP IQ version 12.6 or later. The verification process checks the specified archives for the same errors a restore process checks, but performs no write operations. All status messages and detected errors are written to the server log file.
You cannot use the RENAME clause with the VERIFY clause; an error is reported.

The backup verification process can run on a different host than the database host. You must have the BACKUP DATABASE system privilege to run RESTORE DATABASE VERIFY.

If the COMPATIBLE clause is specified with VERIFY, the compatibility of an incremental archive is checked with the existing database files. If the database files do not exist on the system on which RESTORE DATABASE…VERIFY COMPATIBLE is invoked, an error is returned. If COMPATIBLE is specified while verifying a full backup, the keyword is ignored; no compatibility checks need to be made while restoring a full backup.

You must have the database and log files (.db and .log) to validate the backup of a read-only dbspace within a full backup. If you do not have these files, validate the entire backup by running RESTORE DATABASE…VERIFY without the READONLY <dbspace> clause.

Note
The verification of a backup archive is different than the database consistency checker (DBCC) verify mode (sp_iqcheckdb ‘verify...’). RESTORE DATABASE VERIFY validates the consistency of the backup archive to be sure it can be restored, whereas DBCC validates the consistency of the database data.

Run sp_iqcheckdb ‘verify...’ before taking a backup. If an inconsistent database is backed up, then restored from the same backup archive, the data continues to be in an inconsistent state, even if RESTORE DATABASE VERIFY reports a successful validation.

Examples

(back to top)

Example 1
this UNIX example restores the iqdemo database from tape devices /dev/rmt/0 and /dev/rmt/2 on a Sun Solaris platform. On Solaris, a restore from tape must specify the use of the rewinding device. Therefore, do not include the letter 'n' after the device name, which specifies “no rewind on close.” To specify this feature with RESTORE DATABASE, use the naming convention appropriate for your UNIX platform. (Windows does not support this feature.)
```
RESTORE DATABASE 'iqdemo'
FROM '/dev/rmt/0'
FROM '/dev/rmt/2'
```

Example 2 restore 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'

Example 3
this example shows the syntax of a BACKUP DATABASE statement and two possible RESTORE DATABASE statements. (This example uses objects in the iqdemo database for illustration purposes. Note that iqdemo includes a sample user dbspace named iq_main that may not be present in your database.)
Given this BACKUP DATABASE statement:
```
BACKUP DATABASE READONLY DBSPACES iq_main
TO '/system1/IQ16/demo/backup/iqmain'
```
The dbspace iq_main can be restored using either of these RESTORE DATABASE statements:
```
RESTORE DATABASE 'iqdemo' READONLY DBSPACES iq_main
FROM '/system1/IQ16/demo/backup/iqmain'
```
or
```
RESTORE DATABASE 'iqdemo'
FROM '/system1/IQ16/demo/backup/iqmain'
```
A selective backup backs up either all READWRITE dbspaces or specific read-only dbspaces or dbfiles. Selective backups are a subtype of either full or incremental backups.
Notes:
- You can take a READONLY selective backup and restore all objects from this backup (as in the second example above).
- You can take an all-inclusive backup and restore read-only files and dbspaces selectively.
- You can take a READONLY selective backup of multiple read-only files and dbspaces and restore a subset of read-only files and dbspaces selectively. See Permissions.
- You can restore the read-only backup, only if the read-only files have not changed since the backup. Once the dbspace is made read-write again, the read-only backup is invalid, unless you restore the entire read-write portion of the database back to the point at which the read-only dbspace was read-only.
- Decide which backup subtype to use (either selective or non-selective) and use it consistently. If you must switch from a non-selective to a selective backup, or vice versa, always take a non-selective full backup before switching to the new subtype, to ensure that you have all changes.
Example 4
syntax to validate the database archives using the VERIFY clause, without performing any write operations:
```
RESTORE DATABASE <database_name.db>
FROM '/sys1/dump/dmp1'
FROM '/sys1/dump/dmp2'
VERIFY
```
When you use validate, specify a different database name to avoid Database name not unique errors. If the original database is iqdemo.db, for example, use iq_demo_new.db instead:
```
RESTORE DATABASE iqdemo_new.db FROM iqdemo.bkp VERIFY
```

Example 5

Point in time recovery using Point in time recovery logs only:

// enable Point in time recovery

SET OPTION PUBLIC.IQ_POINT_IN_TIME_RECOVERY_LOGGING = 'ON
ALTER DBSPACE IQ_SYSTEM_LOG RENAME '/demo/pitrLog/PITRLOG'
BACKUP DATABASE FULL to '/demo/dataBackup/FULL1'

// perform some DDL/DML operations 

CREATE TABLE T1 ( ID INT )
INSERT INTO T1 VALUES (1)

// record timestamp TS1 / OFFSET OFS1

TS1 : select getdate()
OFS1 : select db_property('LastCommitRedoPos')

// perform DDL/ DML operations

CREATE TABLE T2 ( ID INT )
INSERT INTO T2 VALUES (1)

// record timestamp TS2 / OFFSET OFS2

TS2 : select getdate()
OFS2 : select db_property('LastCommitRedoPos')

// restore the database to point in time TS1 / OFS1
// shutdown the database
// start a utility server
// drop the database

RESTORE DATABASE 'iqdemo' FROM '/demo/dataBackup/FULL1' 
    RECOVER UNTIL TIMESTAMP  '<TS1>' 
    USING LOG PATH '/demo/pitrLog/' 

// OR

RESTORE DATABASE 'iqdemo' FROM '/demo/dataBackup/FULL1' 
    RECOVER UNTIL OFFSET <OFS1> 
    USING LOG PATH '/demo/pitrLog/'

Example 6

Point-in-time recovery using point-in-time recovery logs and point-in-time recovery log backup archives:

// enable point-in-time recovery

SET OPTION PUBLIC.IQ_POINT_IN_TIME_RECOVERY_LOGGING = 'ON
ALTER DBSPACE IQ_SYSTEM_LOG RENAME '/demo/pitrLog/PITRLOG'
BACKUP DATABASE FULL to '/demo/dataBackup/FULL1'

// perform DDL / DML operations

CREATE TABLE T1 ( ID INT )
INSERT INTO T1 VALUES (1)
RECORD TIME STAMP TS1 / OFFSET OFS1
TS1 : select getdate()
OFS1 : select db_property('LastCommitRedoPos')

// perform DDL / DML operations

CREATE TABLE T2 ( ID INT )
INSERT INTO T2 VALUES (1)
RECORD TIME STAMP TS2 / OFFSET OFS2
TS2 : select getdate()
OFS2 : select db_property('LastCommitRedoPos')

// perform a PITR log backup

BACKUP DATABASE 
    POINT IN TIME RECOVERY LOGS ONLY TO '/demo/pitrLogBackup/PITR1'

// perform DDL/ DML operations

CREATE TABLE T3 ( ID INT )
INSERT INTO T3 VALUES (1)

// restore the database to point-in-time TS2 OR OFS2
// shutdown the database
// start a utility server
// drop the database

RESTORE DATABASE 'iqdemo' 
    FROM '/demo/dataBackup/FULL1' 
    RECOVER UNTIL TIMESTAMP  '<TS2>' 
    USING LOG PATH '/demo/pitrLogBackup/', '/demo/pitrLog/' 

// OR

RESTORE DATABASE 'iqdemo' 
    FROM '/demo/dataBackup/FULL1' 
    RECOVER UNTIL OFFSET <OFS2> 
    USING LOG PATH '/demo/pitrLogBackup/', '/demo/pitrLog/'

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 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 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 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 IQ data before starting the restore.
During backup and restore operations, if SAP 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 Database products—Not supported by SAP ASE.

Permissions

(back to top)

The permissions required to execute this statement are set using the -gu server command line option, as follows:

NONE No user can issue this statement.

DBA Requires the SERVER OPERATOR system privilege.

UTILITY_DB Only those users who can connect to the utility_db database can issue this statement.