4 4 3:SAP ASE Backup
- 1 Overview
- 2 SAP ASE system databases overview
- 3 Prerequisites
- 4 Simple backup configuration using GUI
- 5 Using backup commands for external backup via isql
- 6 Known issues
- 7 See also
- 8 External references
SEP sesam provides efficient data protection for SAP ASE (formerly SAP Sybase ASE) that was developed in collaboration 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:
- SEP sesam GUI based: This method is based on integrated SAP ASE backup and allows you to backup and restore SAP ASE databases easily by using SEP sesam GUI.
With this backup method, available as of 4.4.3 Tigon V2, the SAP ASE backup extension is implemented as an internal backup appliance to provide 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 backup via SAP ASE SQL API. This includes creating a backup history and storing saveset ID into the backup history, which simplifies the restore process. The backup log provides the restore command sequence to allow a complete recover even if the backup history is lost. For details, see GUI-based simple backup.
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 external backup (for details, see below method 2).
- SEP sesam SAP ASE external backup: This method is based on SAP ASE as an external backup task and requires issuing dump commands directly from the isql utility to perform SAP ASE backups. It enables you to use multiple stripes and backup your data in concurrent streams. For details, see 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 complicates a restore as the savesets are not automatically recorded in the SAP ASE backup history. Consequently, to restore the data that was backed up using backup commands the correspondings savesets' IDs have to be identified first as they are not available as a part of a backup history on the SAP ASE system. For details, see Using backup commands for external backup.
Key features of SEP sesam SAP ASE integration
- Easy automation of backups using SEP sesam command events.
- Automatic retention management for your SAP ASE database and transaction log backups.
- Simple overview and monitoring with SEP sesam GUI and Web UI.
- No more manual housekeeping jobs for local backups.
- Allows for integration into system refresh utilities.
- As of 4.4.3 Grolar, DIFF backup level is supported to perform cumulative dump backups.
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 backup: SAP ASE system databases, user databases, transaction logs, and database files (incl. the data device and log device files). Note that application files and operating system data are not included in SAP ASE backup.
- FULL – Full database dump backup
- Saves copies of the entire database to the storage and does not truncate the transaction log. 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, then daily full backup will be sufficient. If your loss tolerance is less than 1 day, for example, down to about 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 type which contains 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 the condition it was before a failure occurred; minimized recovery time; smaller backup size; improved backup performance, etc. This is the recommended backup level in cases when the application performs large amounts of updates on a small subset of data.
- INC – Transaction log dump backup
- Incremental backup makes a copy of the transaction log alone 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 if the transaction log dumps are taken quite frequently, there will be a lot of transaction log dump files to LOAD, which can take quite a long time. This backup level enables point-in-time recovery, uses less time and resources because only the transaction log is copied, but it also represents a risk of loosing data if one of the transaction logs is unusable and the time between full backups is too long.
|To enable cumulative dumps for a database, set database option allow incremental dumps. Your SAP ASE database must be configured to maintain the list of pages that must be dumped. Use the sp_dboption to enable cumulative dumps:
sp_dboption dbname, 'allow incremental dumps', true
After setting the option, first 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
- 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.
- 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 each time it is changed.
- This database holds 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 official SAP documentation System and Optional Databases.
If you are backing up SAP ASE via GUI, create a separate backup task for each database you want to back up. If you are backing up using backup commands, use the backup command examples to create backup scripts for the respective system databases.
To ensure error-free operation of SEP sesam and improve performance, make sure that the following conditions are met:
- Check that the required configuration steps are performed and their 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 typical backup configuration, see Standard Backup Procedure.
- Make sure to regularly check your SAP ASE databases consistency and regularly validate backups of your SAP ASE database.
Simple backup configuration using GUI
As of 4.4.3 Tigon V2, you can use integrated SAP ASE backup via GUI without any extra configuration at SAP ASE side and without setting up backup commands. The sbc_proxy front-end is used as a wrapper to create all configuration parameters for SAP ASE isql to perform backup via SAP ASE SQL API. This includes creating a backup history and storing a saveset ID into the backup history, which simplifies the restore process.
Creating a backup task with 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.
- From Main Selection -> Tasks -> By Clients, select SAP ASE client and click New Backup Task. The New backup task window opens.
- Enter the Task name, for example, SAP_ASE_master.
- From the Task type drop-down list, select SAP ASE.
- Under Source, enter the name of the SAP ASE database. Note that client browsing does not work.
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 description of isql -S server_name.
- with port: /SYBASE16:5000/pubs2
- without port: /SYBASE16/pubs2
- without server name and port: pubs2
|If you do not specify the server name, then isql looks for the server specified by your DSQUERY environment variable.|
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 must 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 which are described above in the section Supported backup levels.
SAP ASE backup execution
When a backup is executed according to schedule or manually by using the Immediate Start option, SAP ASE writes backup processing information to backup log, including the isql script for backup and the required restore commands via
load database <database_name> with listonly=load_sql
The output of the above command produces 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
|The load commands from this command output can be copied to a script and used to start isql.|
You can view the status of your backup jobs in GUI by selecting Last backup state in the Main selection window. The backup status overview provides detailed information on the last run of backup jobs, including the task name, start and stop time of the last backup, backup level, data size, throughput, assigned media pool, etc.
|As of 4.4.3 Beefalo V2, you can check the details of your backups online by using new Web UI. For details, see SEP sesam Web UI.|
Using backup commands for external backup via isql
In order 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 shorten your backup time, it also complicates a restore as the savesets are not automatically recorded in the SAP ASE backup history.
|Make sure that you have the backup history enabled, as described in Enabling dump history file. By default, the backup history is not enabled which means that no track of the backups from dump database and dump transaction commands will be 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 parameter during the ASE backup command created on the ASE system.
Creating a backup command on ASE System
Database dump and load commands follow similar syntax regarding the use of 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:
DRIVE=(drivenumber) POOL=(mediapool) SERVER=(sesamserver) JOB=(sesam_taskname)
- Backup of master database (recommended) to backup server "backup001.sep.de" into 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"
DUMP DATABASE PRD TO "sepsybase::/tmp/;DRIVE=2;POOL=daily_7;SERVER=backup001.sep.de;JOB=sybase_ase-1"
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 parameters exceeds 127 characters an configuration file can be specified:
DUMP DATABASE SESAM_DB TO "sepsybase::/tmp/;SESAM_INI_FILE=/tmp/backup.ini"
The configuration file can then include a list of arguments as shown above.
For details on SAP ASE backup, see 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. Run of the following it is necessary to execute your ASE server by one of those DUMP commands:
- Open text editor and copy content below into a new file:
- Replace the variables with the values suitable for your environment, for example:
- Save the file into your sybase directory as sysdbbackup.sql. To make your SAP ASE server execute the SQL file, you can use a SEP sesam command event configuration. Use the following command syntax:
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>"
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"
isql -S server_name -U user_name -P password -i script_name
isql -S localhost -U backup -P supersecret -i $SYBASE/$SYBASE_ASE/sysdbbackup.sqlBy running this command all relevant system databases on your SAP ASE server will be backed up. You may 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.