5 1 0:SAP IQ Restore

From SEPsesam
Other languages:

Docs latest icon.png Welcome to the latest SEP sesam documentation version 5.1.0 Apollon. For previous documentation version(s), check documentation archive.


SEP sesam provides efficient data protection for SAP IQ that was developed in cooperation with SAP. SEP sesam's API-based backup module for SAP IQ centralizes and automates data backup and restore in SAP environments.


  • Ensure that the required configuration steps are performed and the prerequisites are met. See SAP IQ Configuration and SAP IQ Backup.
  • Check that there is sufficient free space on the target storage system to which you want to restore your data.

Information sign.png Note
SAP IQ also enables Point-In-Time Recovery (PITR), where a database is restored from a full data backup, and then all incremental backups up to the point in time you want to recover. However, SAP does not allow restoring backups performed by external backup solutions (including SEP sesam), therefore Point-In-Time Recovery cannot be performed for SEP sesam backups.

Restore process

To restore a database, you must be connected to the utility_db database. All RESTORE DATABASE commands are executed from the utility database utility_db, and require exclusive access to the database.

To restore a database, connect to the utility database and run the RESTORE DATABASE commands from dbisql:

  1. Shut down the database server:
  2.  stop_iq -stop all
  3. Start a server that you can use to connect to the utility database:
     start_iq -su <password> -gd DBA -gm 1 -n sapiq_iqdemo
    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.
  4. Start dbisql and connect to the utility database:
  5.  dbisql -c "UID=DBA;PWD=<password>;DBN=utility_db" -nogui -onerror exit <restore_SQL_script>

    For example:

    dbisql -c 'UID=DBA;PWD=sql;DBN=utility_db' -nogui -onerror exit /root/sapiq/restore.sql
  6. Run all RESTORE DATABASE commands from dbisql.
  7. Shut down the server and utility database.
  8. Start the database server normally.

When SAP 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.

The command RESTORE DATABASE supports two syntax variations:

Restoring an IQ database:

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

Restoring a catalog store:

  '<archive_device>' [ FROM '<archive_device>' ]…
  … [ KEY key_spec ]
  … [ [ RENAME <logical-dbfile-name> TO '<new-dbspace-path>']...

For more information on RESTORE DATABASE command and syntax, refer to SAP IQ SQL Reference: RESTORE DATABASE Statement.

Validating a database after restore

To check validity of the current database after restore, run the command sp_iqcheckdb:

sp_iqcheckdb '<mode> <target> [ … ] [ resources <resource-percent> ]'

sp_iqcheckdb reads all storage in the database, updates the internal allocation map to reflect the true storage allocation for the database, and generates a report listing the performed actions.

For incremental backups, you can run the sp_iqcheckdb after restoring each backup or after you restore the last incremental backup.

Note that it is recommended to verify consistency of the database before performing backup. If an inconsistent database is backed up and then restored, the database is inconsistent even if RESTORE DATABASE VERIFY reports a successful validation.

For more detail on sp_iqcheckdb command and syntax, refer to SAP IQ SQL Reference: sp_iqcheckdb Procedure.

Restoring a database from the latest full backup

To restore a database to its original destination, you can use the following example dbisql statement. You have to specify the following parameters:

  • SESAM_SERVER= (backup server)
  • SESAM_JOB= (name of the backup job)
  • SESAM_POOL= (name of the backup pool)

An example script would look like this:

    FROM 'sepsybase::/tmp/;SESAM_LOGFILE=/tmp/sapiq-restore.log;SESAM_LOGLEVEL=4;SESAM_SERVER=https://sesam-backup-srv.sep.de:11000;SESAM_JOB=SAP_IQ;SESAM_POOL=SAPIQ_pool;'

An example script for restore using 4 stripes:

  FROM 'sepsybase::/tmp/;SESAM_LOGFILE=/tmp/sapiq-full-restore_stripe1.log;SESAM_LOGLEVEL=4;SESAM_SERVER=https://sesam-backup-srv.sep.de:11000;SESAM_JOB=SAP_IQ;SESAM_POOL=SAPIQ_pool;' 
  FROM 'sepsybase::/tmp/;SESAM_LOGFILE=/tmp/sapiq-full-restore_stripe2.log;SESAM_LOGLEVEL=4;SESAM_SERVER=https://sesam-backup-srv.sep.de:11000;SESAM_JOB=SAP_IQ;SESAM_POOL=SAPIQ_pool;' 
  FROM 'sepsybase::/tmp/;SESAM_LOGFILE=/tmp/sapiq-full-restore_stripe3.log;SESAM_LOGLEVEL=4;SESAM_SERVER=https://sesam-backup-srv.sep.de:11000;SESAM_JOB=SAP_IQ;SESAM_POOL=SAPIQ_pool;' 
  FROM 'sepsybase::/tmp/;SESAM_LOGFILE=/tmp/sapiq-full-restore_stripe4.log;SESAM_LOGLEVEL=4;SESAM_SERVER=https://sesam-backup-srv.sep.de:11000;SESAM_JOB=SAP_IQ;SESAM_POOL=SAPIQ_pool;' 

Restoring a database with a specific saveset ID

The SAP IQ backup extension enables you to restore your database using a specific saveset. The saveset ID is specified with the following parameter:

  • SAVESET=(saveset ID to restore)

For example:

   FROM 'sepsybase::/tmp/;SESAM_LOGFILE=/tmp/sapiq-restore.saveset.log;SESAM_LOGLEVEL=4;SESAM_SERVER=https://sesam-backup-srv.sep.de:11000;SAVESET=SF20230905092226537@iBaygMym2dL;SESAM_JOB=SAP_IQ;SESAM_POOL=SAPIQ_pool;'

Restoring a database to a different target with the latest full backup

To restore a database under a different name, you can use the following command:

  FROM '<archive_device>'
  RENAME <file_name> TO <new_file_path>

For example:

  FROM 'sepsybase::/tmp/;SESAM_LOGFILE=/tmp/sapiq-full-restore_stripe1.log;SESAM_LOGLEVEL=4;SESAM_SERVER=https://sesam-backup-srv.sep.de:11000;SESAM_JOB=SAP_IQ;SESAM_POOL=SAPIQ_pool;' 
  RENAME iqdemo.db TO /tmp/iqnew.db

See also

SAP IQ BackupSAP IQ Configuration

Copyright © SEP AG 1999-2024. All rights reserved.
Any form of reproduction of the contents or parts of this manual is allowed only with the express written permission from SEP AG. When compiling and designing user documentation SEP AG uses great diligence and attempts to deliver accurate and correct information. However, SEP AG cannot issue a guarantee for the contents of this manual.