5 1 0:SAP ASE Backup

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.


Overview


SEP sesam provides efficient data protection for SAP ASE (formerly SAP Sybase ASE) that was developed in cooperation with SAP.

In the past, there were two possibilities for backing up databases stored on ASE server: backup to file system and backup to tape. Both methods had the same problems: lots of scripting was required to automate the backups; there was no retention management for backups; it was not possible to monitor the backup status over time; and backup as a service (BaaS) was not possible.

Available backup methods

SEP sesam provides two different methods for SAP ASE backup:

  1. SEP sesam GUI-based: This method is based on the integrated SAP ASE backup and allows you to easily backup and restore SAP ASE databases via the SEP sesam GUI.
    The SAP ASE backup extension is implemented as an internal backup appliance to ensure efficient data protection for SAP ASE. The sbc_proxy front-end is used as a wrapper to create all configuration parameters for SAP ASE isql to perform the backup via SAP ASE SQL API. This includes creating a backup history and storing the saveset ID in the backup history, which simplifies the restore process. The backup log contains the restore command sequence to allow a complete recover even if the backup history is lost. For details, see GUI-based simple backup.
    The current SEP sesam GUI-based simple backup does not support multiple stripes (concurrent backup streams); only one stripe (backup channel) is used for backup. In order to use multiple stripes and backup your data in concurrent streams, you have to use an external backup (see method 2 below for details).
  2. SEP sesam SAP ASE external backup: This method is based on SAP ASE as the external backup task and requires dump commands to be issued directly from the isql utility to perform SAP ASE backups. This allows you to use multiple stripes and back up your data in concurrent streams. For details, see the SAP ASE documentation Dump and Load with Multiple Stripes.
    While this second 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 ASE 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 ASE system. For details, see Using backup commands for external backups.

Key features of the SEP sesam SAP ASE integration

Supported backup levels

GUI-based simple backup (based on sbc_proxy) issues an isql dump command and provides the following backup levels: FULL (database dump), DIFF (cumulative dump), and INCR (transaction log dump). COPY backup is not supported.

The following data is included in the backup: SAP ASE system databases, user databases, transaction logs, and database files (incl. data device and log device files). Note that application files and operating system data are not included in the SAP ASE backup.

FULL – Full database dump 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.
DIFF – Cumulative dump backup
This is SAP ASE incremental-since-full-backup, which includes all changes since the last full database dump backup. Note that you cannot perform a cumulative dump for master, proxy and temporary databases. The advantages of cumulative dump 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.
INC – Transaction log dump 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.
Information sign.png Note
To enable cumulative dumps for a database, set the database option allow incremental dumps. Your SAP ASE database must be configured to maintain the list of pages that need to be dumped. Use the sp_dboption to enable cumulative dumps:
sp_dboption dbname, 'allow incremental dumps', true

After you set the option, a full dump must be performed before taking a cumulative dump.

You can select the relevant backup level when creating a backup event, as described in Creating a backup event.

SAP ASE system databases overview

master
The master database controls the operation of SAP ASE and stores information about all user databases and their associated database devices. Make sure that the master database is backed up regularly.
model
This SAP ASE database is a template for new user databases and does not necessarily have to be backed up. SAP recommends to back up the model database whenever changes are made.
sybsystemprocs
This database contains the SAP system procedures for the SAP ASE server. It is recommended to back up the sybsystemprocs database each time it is changed, if you have stored procedures on your database server.

For details on system databases, see the official SAP documentation System and Optional Databases.

If you are back up SAP ASE via GUI, create a separate backup task for each database you want to back up. If you are backing up with backup commands, use the backup command examples to create backup scripts for the respective system databases.

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 ASE 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 ASE databases consistency and regularly validate the backups of your SAP ASE database.

Simple backup configuration via the GUI

Use integrated SAP ASE backup via the GUI without additional configuration on the SAP ASE side and without setting up backup commands. The sbc_proxy front-end is used as a wrapper to create all the configuration parameters for SAP ASE isql to perform the backup via SAP ASE SQL API. This includes creating a backup history and storing a saveset ID in the backup history, which simplifies the restore process.

Information sign.png Note
  • SEP sesam automatically retrieves the required system environment variables used by your SAP ASE software from the %SYBASE%\SYBASE.env.
  • Currently, this method does not support multiple stripes (concurrent backup streams); only one stripe (backup channel) is used for backup. If you want to use multiple stripes, consider using backup commands instead.

Creating a backup task with the SAP ASE task type on SEP sesam Server

Use the SEP sesam GUI to configure SAP ASE backup tasks. You have to create a separate backup task for each SAP ASE database you want to back up.

  1. From Main Selection -> Tasks -> By Clients, select SAP ASE client and click New Backup Task. The New backup task window opens.
  2. Enter the Task name, for example, SAP_ASE_master.
  3. From the Task type drop-down list, select SAP ASE.
  4. Under Source, enter the name of the SAP ASE database. Note that client browsing does not work.
  5. Since you have to create a separate backup task for each SAP ASE database you want to back up, the source must be specified accordingly, e.g., specify a database master for the backup task SAP_ASE_master. The database name can be prefixed by the SAP ASE server name, which may be specified together with the SAP ASE port. The format of the source is [/<server name>[:<port>]/]<database>. For details, see the description of isql -S server_name. Examples:
    • with port: /SYBASE16:5000/pubs2
    • without port: /SYBASE16/pubs2
    • without server name and port: pubs2
    Information sign.png Note
    If you do not specify the server name, isql searches for the server specified by your DSQUERY environment variable.

    SAP ASE bck task.jpg

  6. Click the tab SAP ASE and enter the user name and password to access the database. Note that a user account with sufficient privileges is required to perform backup and restore operations!
  7. SAP ASE tab.jpg
  8. Click OK to create the task.

Creating a backup event

Once you created a task, create a schedule to specify when you want to back up your data. Once you have configured a schedule, you need to create a new backup event for it. Go to Main Selection -> Scheduling -> New Backup Event, click the Parameter tab and select one of the available backup levels described above in the section Supported backup levels.

SAP ASE backup levels.jpg

SAP ASE backup execution

When a backup is executed according to schedule or manually with the Immediate Start option, SAP ASE writes backup processing information to the backup log, including the isql script for the backup and the required restore commands via

load database <database_name> with listonly=load_sql

The output of the above command provides the commands needed to recover the database to the state of the recent backup. The following example shows the output for the cumulative backup, therefore two commands are generated:

2018-06-14 11:57:09: sba-3200: Info:     'load_sql command' [/var/opt/sesam/var/tmp/isql_load_sql-SAP_ASE_pubs2-d.txt]:
1> load database pubs2 with listonly=load_sql
LOAD DATABASE pubs2 FROM   'sepsybase::/tmp/;;SAVESET=SF20180614115614486@sZTodquLIL3;INI_FILE=gv_rw_times:SAP_ASE_pubs2-f.ini'
go
LOAD DATABASE pubs2 CUMULATIVE FROM 'sepsybase::/tmp/;;SAVESET=SD20180614115621330@Msz-- mhhouh;INI_FILE=gv_rw_times:SAP_ASE_pubs2-d.ini'
go
SEP Tip.png Tip
The load commands from this command output can be copied to a script and used to start isql.

Monitoring backup

You can view the status of your backup jobs in the GUI (Monitoring -> Last Backup State or Job State -> Backups) or SEP sesam Web UI. The backup status overview provides detailed information about the last run of backup jobs, including task name, start and stop time of the last backup, backup level, data size, throughput, assigned media pool, etc.

Using backup commands for external backup via isql

To use multiple stripes and backup your data in concurrent streams, you have to use external backup via isql with dump. 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 ASE backup history.

Information sign.png Note
Make sure that you have enabled the backup history, as described in Enabling dump history file. By default, backup history is not enabled, which means that no track of the backups from the dump database and dump transaction commands is available.

The following backup configuration is used with SEP sesam 4.4.3 Tigon.

Creating a backup task

Before the ASE backint component can be started on the ASE system, a backup task has to be specified. This backup task is set as a parameter during the ASE backup command created on the ASE system.

Sybase backup.jpg

Creating a backup command on the ASE System

Database dump and load commands follow a similar syntax in terms of using the SAP ASE backup API. The library delivered by SEP sesam to save the backup to the SEP sesam Server is treated as a special destination location.

  • Special destination syntax:
  •  "sepsybase::<pipename>;<arguments>" 
  • Possible arguments are:
  •  DRIVE=(drivenumber)
     POOL=(mediapool)
     SERVER=(sesamserver)
     JOB=(sesam_taskname)
  • Backup command examples:
    • Backup of master database (recommended) to the backup server "backup001.sep.de" into the media pool "daily_7" using drive "2" and the task name "sybase_ase-1":
    •  DUMP DATABASE master TO "sepsybase::/tmp/;DRIVE=2;POOL='''daily_7''';SERVER='''backup''';JOB='''sybase_sesam_db'''"
    • Backup of the database PRD to the backup server "backup001.sep.de" into the media pool "daily_7" using drive "2" and the task name "sybase_ase-1":
    •  DUMP DATABASE PRD TO "sepsybase::/tmp/;DRIVE=2;POOL=daily_7;SERVER=backup001.sep.de;JOB=sybase_ase-1"
    • Backup of the transaction log from the database SRD:
    •  DUMP TRANSACTION SRD TO "sepsybase::/tmp/;DRIVE=4;POOL=tp-month;SERVER=sesamsrv02.yourcompa.ny;JOB=SRD-22-syb"

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

 DUMP 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 ASE backup, see the SAP Help Portal.

Creating a database backup SQL file

To back up databases, you have to trigger them from the SQL interface of the ASE. This is possible by using SQL scripts. There are some examples for DUMP DATABASE queries in the section below. To execute the following, it is necessary to run your ASE server via one of these DUMP commands:

  1. Open a text editor and copy the content below into a new file:
  2.  DUMP DATABASE master TO "sepsybase::/tmp/;DRIVE=<sesam-drive>;POOL=<sesam-pool>;SERVER=<sesam-server>;JOB=<sesam-taskname>"
     DUMP DATABASE sybsystemprocs TO "sepsybase::/tmp/;DRIVE=<sesam-drive>;POOL=<sesam-pool>;SERVER=<sesam-server>;JOB=<sesam-taskname>"
     DUMP DATABASE sybsystemdb TO "sepsybase::/tmp/;DRIVE=<sesam-drive>;POOL=<sesam-pool>;SERVER=<sesam-server>;JOB=<sesam-taskname>" 
     DUMP DATABASE sybmgmtdb TO "sepsybase::/tmp/;DRIVE=<sesam-drive>;POOL=<sesam-pool>;SERVER=<sesam-server>;JOB=<sesam-taskname>" 
  3. Replace the variables with the values suitable for your environment, for example:
  4.  DUMP DATABASE master TO "sepsybase::/tmp/;DRIVE=2;POOL=daily_7;SERVER=backup001.sep.de;JOB=sybase_ase-1"
     DUMP DATABASE sybsystemprocs TO "sepsybase::/tmp/;DRIVE=2;POOL=daily_7;SERVER=backup001.sep.de;JOB=sybase_ase-1"
     DUMP DATABASE sybsystemdb TO "sepsybase::/tmp/;DRIVE=2;POOL=daily_7;SERVER=backup001.sep.de;JOB=sybase_ase-1"
     DUMP DATABASE sybmgmtdb TO "sepsybase::/tmp/;DRIVE=2;POOL=daily_7;SERVER=backup001.sep.de;JOB=sybase_ase-1"
  5. Save the file into your sybase directory as sysdbbackup.sql. To get your SAP ASE server to execute the SQL file, you can use a SEP sesam command event configuration. Use the following command syntax:
  6.  isql -S server_name -U user_name -P password -i script_name
  7. Example:
  8.  isql -S localhost -U backup -P supersecret -i $SYBASE/$SYBASE_ASE/sysdbbackup.sql
    By running this command all relevant system databases on your SAP ASE 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 DUMP DATABASE calls into one file and execute all backups one after another. For details, see Creating a Command Event.


Known issues

If you have problems with SAP ASE backup, check the Troubleshooting Guide.

See also

SAP ASE ConfigurationSAP ASE Restore

External references

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.