5 1 0:SAP IQ Backup

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

Overview

Additional resources

Visit SEP sesam YouTube Channel for latest videos and webinars.

Check FAQ to find the answers to most common questions.

Problems? See the Troubleshooting Guide.

If you are using an older SEP sesam version, refer to Documentation archive.

SEP sesam provides efficient data protection for SAP IQ that was developed in cooperation with SAP.

The integration enables using multiple stripes and backing up your data in concurrent streams. SEP sesam SAP IQ external backup is based on SAP IQ as the external backup task and requires dump commands to be issued directly from the dbisql utility to perform SAP IQ backups. For details, see the SAP IQ documentation SAP IQ Administration: Backup, Restore, and Data Recovery.

While this method enables you to perform a fast backup and shorten your backup time, it also makes it more difficult to restore because the savesets are not automatically recorded in the SAP IQ backup history. Consequently, to restore data that was backed up using backup commands, you must first determine the corresponding saveset IDs, as these are not available as part of the backup history on the SAP IQ system.

Supported backup levels

The SAP IQ backup includes the selected backup level of an SAP IQ database and a full backup of the catalog store. The integration provides the following backup levels: FULL (a complete copy of the database), DIFF (all changes to the database since the last full backup, also called INCREMENTAL-SINCE-FULL) and INCR (all transactions since the last backup of any type). COPY backup is not supported.

FULL – Full database backup: Saves copies of the entire database to storage and does not truncate the transaction log. The FULL backup is a baseline to which subsequent backup levels are applied (DIFF and INC). You can perform a full backup of an online (default) or offline database. Depending on your loss tolerance, schedule your backups accordingly. For example, if your loss tolerance is 24 hours, a daily full backup will be sufficient. If your loss tolerance is less than 1 day, for example, up to half an hour, plan your backup strategy by adding DIFF (cumulative dump) and INCR (transaction log dump) backups.
INCREMENTAL SINCE FULL – Backup of changes since last full backup (equivalent to SEP sesam DIFF): Includes all changes since the last full database backup. Note that you cannot perform a DIFF backup for master, proxy and temporary databases. The advantages of DIFF backups are: roll-forward recovery to restore a damaged database to its pre-failure state; minimized recovery time; smaller backup size; improved backup performance, etc. This is the recommended backup level when the application performs large amounts of updates for a small subset of data.
INCREMENTAL – Transaction log changes since last previous backup: The incremental backup creates only one copy of the transaction log and contains all transactions since the last backup of any type. When a transaction log is backed up, it is truncated to the exact time of the backup. Note that frequent transaction log dumps create a large number of transaction log dump files to LOAD, which can take quite a long time. This backup level enables point-in-time recovery, consumes less time and resources as only the transaction log is copied, but also carries the risk of data loss if one of the transaction logs is unusable and the time between full backups is too long.

Note that 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), and Point-In-Time Recovery cannot be performed for SEP sesam backups.

The relevant backup level is specified with the BACKUP DATABASE command, as described in Creating a backup SQL script on the SAP IQ server.

Configuring external backup using `dbisql`

SAP IQ external backup process involves several components:

a SEP sesam backup task, which links SEP sesam and the SQL Anywhere Backup (dbisql utility,
an SQL script on the SAP IQ database server, which contains the command for starting backup,
a SEP sesam command with dbisql command, which executes the script and triggers the backup process,
a scheduled command event, which automates the SAP IQ backup process and performs regular backups at predefined intervals.

To use multiple stripes and backup your data in concurrent streams, perform external backup via dbisql with BACKUP DATABASE command. Note that while this method enables you to perform a fast backup and shortens the backup time, it also makes it difficult to restore because the savesets are not automatically recorded in the SAP IQ backup history.

Before starting the backup, BACKUP DATABASE issues a CHECKPOINT and performs a full backup of the catalog store first. Data that is not committed when this initial checkpoint occurs is not included in the backup. At the end of the backup, the second checkpoint occurs. Data that is committed during backup is included in subsequent backups. On SAP IQ backups can run concurrently with most other read-write operations, except those that affect the database structure.

	Note
	Make a copy of the `params.cfg` file and save the contents of the SYSDBFILE and SYSDBSPACE system views. SAP IQ does not back up the temporary store (`<db-name>.iqtmp`) and `params.cfg`, but does back up the metadata and other information that is necessary for re-creating the temporary store structure.

Prerequisites

To ensure error-free operation of SEP sesam and improve performance, make sure that the following conditions are met:

Ensure that the required configuration steps are performed and the prerequisites are met. See SAP IQ Configuration.
A user account with sufficient privileges to perform backup and restore operations.
Before proceeding, validate that you can back up to SEP sesam. Run a test Path backup manually to ensure that the backup process works. For details on a typical backup configuration, see Standard Backup Procedure.
Make sure to regularly check your SAP IQ database consistency and regularly validate the backups of your SAP IQ database.

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

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

Creating a backup task

A backup task defines the scope of the backup and establishes the link between the dbisql utility and the SEP sesam savesets. The backup process in SEP sesam is handled externally with BACKUP DATABASE command, so there is no need to create or schedule a backup event for this task.

For general information on SEP sesam backup configuration and prerequisites, see Standard Backup Procedure. This section deals only with SAP IQ-specific information.

	Note
	It is recommended to create separate backup tasks for each SAP IQ database. This helps to identify valid backups for a specific SAP IQ database in case restore is required.

To configure a backup of SAP IQ database, first create a backup task for the SAP IQ client:

From Main Selection -> Tasks -> By Clients, select the SAP IQ client and then click New Backup Task. The New Backup Task window opens.
From the Task type drop-down list, select SAP ASE.
Under Source, enter the name of the SAP IQ database or use the browse button to select the database in the Client File View.
Configure the options as required and click OK to create the task. For detailed instructions see Creating a Backup Task.

Screenshots

Creating a backup SQL script on the SAP IQ server

To back up SAP IQ databases, you have to trigger them from the SQL interface on the SAP IQ database server. This is possible by using SQL scripts with the BACKUP DATABASE command. The BACKUP DATABASE command backs up an SAP IQ database to one or more archive devices and follows a similar syntax in terms of using the SAP IQ backup API.

Using a text editor, write the SQL script as required and save the file into your sybase directory as <script_name>.sql. For sample BACKUP DATABASE queries refer to the section below.

Basic BACKUP DATABASE command syntax includes the backup type, and a TO clause that directs the output to SEP sesam:

BACKUP DATABASE
[FULL|INCREMENTAL|INCREMENTAL SINCE FULL]
TO "sepsybase::<pipename>;<arguments>"

Possible arguments are:

SESAM_DRIVE=(drive number)
SESAM_POOL=(media pool)
SESAM_SERVER=(SEP sesam Server)
SESAM_JOB=(backup task)

Example database backup SQL scripts

Full backup of SAP IQ database to the backup server "sesam_backup-srv.sep.de" into the media pool "SAPIQ_pool" using the task named "SAP_IQ":

 BACKUP DATABASE 
  TO 'sepsybase::/tmp/;SESAM_LOGFILE=/tmp/sapiq-full.log;SESAM_LOGLEVEL=4;SESAM_SERVER=https://sesam_backup-srv.sep.de:11000;SESAM_JOB=SAP_IQ;SESAM_POOL=SAPIQ_pool;'

Differential backup of the SAP IQ database to the backup server "sesam_backup-srv.sep.de" into the media pool "daily_7" using the task named "SAP_IQ":

 BACKUP DATABASE
      INCREMENTAL SINCE FULL
      TO 'sepsybase::/tmp/;SESAM_SERVER=https://sesam_backup-srv.sep.de:11000;SESAM_JOB=SAP_IQ;SESAM_POOL=daily_7;'

Full backup of SAP IQ database using 4 stripes of backup data:

BACKUP DATABASE 
  TO 'sepsybase::/tmp/;SESAM_LOGFILE=/tmp/sapiq-full-backup_stripe1.log;SESAM_LOGLEVEL=4;SESAM_SERVER=https://sesam-backup-srv.sep.de:11000;SESAM_JOB=SAP_IQ;SESAM_POOL=SAPIQ_pool'
  TO 'sepsybase::/tmp/;SESAM_LOGFILE=/tmp/sapiq-full-backup_stripe2.log;SESAM_LOGLEVEL=4;SESAM_SERVER=https://sesam-backup-srv.sep.de:11000;SESAM_JOB=SAP_IQ;SESAM_POOL=SAPIQ_pool'
  TO 'sepsybase::/tmp/;SESAM_LOGFILE=/tmp/sapiq-full-backup_stripe3.log;SESAM_LOGLEVEL=4;SESAM_SERVER=https://sesam-backup-srv.sep.de:11000;SESAM_JOB=SAP_IQ;SESAM_POOL=SAPIQ_pool'
  TO 'sepsybase::/tmp/;SESAM_LOGFILE=/tmp/sapiq-full-backup_stripe4.log;SESAM_LOGLEVEL=4;SESAM_SERVER=https://sesam-backup-srv.sep.de:11000;SESAM_JOB=SAP_IQ;SESAM_POOL=SAPIQ_pool'
WITH COMMENT 'backup-full-4stripes'

Backup of the read-write or read-only files or dbspaces (the read-write dbspaces and files must be SAP IQ dbspaces):

 BACKUP DATABASE READONLY DBSPACES <dbspace>
      TO 'sepsybase::/tmp/;SESAM_LOGFILE=/tmp/sapiq-diff.log;SESAM_LOGLEVEL=4;SESAM_SERVER=https://sesam-backup-srv.sep.de:11000;SESAM_JOB=SAP_IQ;SESAM_POOL=SAPIQ_pool;'

BACKUP DATABASE READONLY FILES <file1>, <file2>
      TO 'sepsybase::/tmp/;SESAM_LOGFILE=/tmp/sapiq-diff.log;SESAM_LOGLEVEL=4;SESAM_SERVER=https://sesam-backup-srv.sep.de:11000;SESAM_JOB=SAP_IQ;SESAM_POOL=SAPIQ_pool;'

BACKUP DATABASE READONLY DBSPACES <dbspace1>, <dbspace2> READONLY FILES <file1>, <file2>
      TO 'sepsybase::/tmp/;SESAM_LOGFILE=/tmp/sapiq-diff.log;SESAM_LOGLEVEL=4;SESAM_SERVER=https://sesam-backup-srv.sep.de:11000;SESAM_JOB=SAP_IQ;SESAM_POOL=SAPIQ_pool;'

In case the maximum length of the parameters exceeds 127 characters, a configuration file can be specified:

 BACKUP DATABASE SESAM_DB TO "sepsybase::/tmp/;'''SESAM_INI_FILE=/tmp/backup.ini'''"

The configuration file can then contain a list of arguments as shown above.

For details on SAP IQ, see the SAP Help Portal. For more information on BACKUP DATABASE command and syntax, refer to SAP IQ SQL Reference: BACKUP DATABASE Statement.

Creating a SEP sesam command event

To trigger your SQL script and back up your SAP IQ database, configure a SEP sesam command and command event.

Create a command with the following command syntax:

 dbisql -c 'uid=<username>;pwd=<password>' -nogui -onerror <action> <SQL script>

Example:

dbisql -c 'uid=DBA;pwd=password' -nogui -onerror exit /root/sapiq/backup.sql

dbisql -c 'uid=DBA;pwd=sql' -nogui -onerror exit /root/sapiq/backup.4stripes.sql

Then create and schedule a command event to automate the backup process. For instructions see Creating a SEP sesam Command Event.

By running this command all relevant system databases on your SAP IQ server will be backed up. You can either maintain an SQL file for each of your user databases and execute or configure a separate command event for every file, or put all BACKUP DATABASE calls into one file and execute all backups one after another.