SAP ASE
You can perform your SAP ASE backups by using one of the following options. Select the one that better meet your needs.
Topics
Configure backint-related parameters
Before you can start using CxLink Backup with your SAP ASE Database you will need to ensure that following configuration options are configured in your server:
Create a dump configuration
In order to make it easier for backup adminsitrator to launch backups with specific settings, it is a best practice to create dump profiles. We can create as many Dump Profiles as we want, each one with different settings. This can be useful when we want to use, for example, one profile for Full Backups, other for Incremental Backup, another one for logs backups, and so on.
Syntax to create a dump profile, is as follows:
1> use master
2> go
1> sp_config_dump
@config_name = '<profile_name>',
@ext_api = 'emory::compression=<compression_level',
@num_stripes = '<int>',
@blocksize = '16384'
2> go
Sybase Reference Manual
Use one of the following examples to create your dump configuration file. You can adapt these parameters to any value that better fits your needs:
Create dump profile for backups - 4 vCPUs with no compression
1> use master
2> go
1> sp_config_dump
@config_name = 'emory_S8',
@ext_api = 'emory::',
@num_stripes = '8',
@blocksize = '16384'
2> go
Create dump profile for backups - 8 vCPUs with compression
1> use master
2> go
1> sp_config_dump
@config_name = 'emory_S8_C5',
@ext_api = 'emory::compression=5',
@num_stripes = '8',
@blocksize = '16384'
2> go
Create dump profile for logs - 4 vCPUs with compression
1> use master
2> go
1> sp_config_dump
@config_name = 'emory_S4_C1',
@ext_api = 'emory::compression=1',
@num_stripes = '4',
@blocksize = '16384'
2> go
List existing dump profiles
To ensure that the dump profile has been properly created you can list the existing dump profiles by executing the following command:
1> use master
2> go
1> sp_config_dump
2> go
View configuration details
To view the configuration details of a specific dump profile, execute the following command:
1> use master
2> go
1> sp_config_dump <profile_name>
2> go
Expand to see an output example
optionname optionvalue
----------------- ---------------------
ext_api emory::compression=5
num_stripes 8
blocksize 65536
Configure database specific options
In this section you will find the specific configuration settings modifiable for ASE.
All the modifiable values will be listed in the Specific Database configuration for ASE database section in CxLink Backup menu:
? CxLink Backup: [Use arrows to move, enter to select, type to filter]
Storage Providers profiles
Data storage providers for backups & restores
> Specific Database configuration for ASE database
License Software
Logging settings
Syntax Backups Dashboard
Backups Lifecycle
Debug traces
Exit
Sybase IO Block Size
By default, the value of block size is set at 16 KB. You can keep increasing the value of block size by multiple of 2 KB till you get a higher I/O during dump and load operations.
TIP
It is a best practice to set the block size as 16 KB.
If you need to change this value, follow these steps:
- Enter the proper section from the menu:
? Select action to perfom: [Use arrows to move, enter to select, type to filter]
> Sybase IO Block Size
Return
- Select the desired IO Block Size:
? Select action to perfom: Sybase IO Block Size
? Select Sybase IO Block Size (change only under supervision): [Use arrows to move, enter to select, type to filter]
4096 bytes
8192 bytes
> 16384 bytes
32768 bytes
- You can now select Return and Exit to save the configuration if no other action should be taken.
The following settings should be applied to your SAP ASE Database:
TIP
To perform all the following actions your will need to connect to your SAP ASE database ISQL command line tool.
isql -U sapsa -P <your password> -S <SAP SID> -X
Check Database Page Size. The recommended value for creating dump configuration settings should be
163841> select @@maxpagesize 2> go ----------- 16384Activate Database Logging. In order to be able to recover a database with Point In Time options, yu must activate database logging.
1> exec master..sp_dboption D04, 'trunc log on chkpt', false 2> goEnable Dumphistory File. To enable the
dump historyfile, execute the following commands:1> use master 2> go 1> sp_configure 'enable dump history', 1 2> go 1> sp_configure 'dump history filename', 1, 'dumphist' 2> goTIP
To ensure that the proper parameters have been activated you can list the current configuration by executing the following commands.
1> sp_configure 'dump history filename' 2> go Parameter Name Default Memory Used Config Value Run Value Unit Type --------------------- ----------- ----------- ------------ ------------ -------------------- -------------------- dump history filename dumphist 0 dumphist dumphist name dynamic 1> sp_configure 'enable dump history' 2> go Parameter Name Default Memory Used Config Value Run Value Unit Type --------------------- ----------- ----------- ------------ ------------ -------------------- -------------------- enable dump history 0 0 1 1 switch dynamicList Dumphistory File contents. To list the current
dump historyfile contents, you can execute the following commands:1> sp_dump_history 2> goExpand to see an example
DUMP ---- Dump_Type Dbid Database_name Stripes Dump_instant File Server_name Compression_lvl Password Status Label Dump_date ----------- ------ -------------- ------- --------------------------- ------------------------------------------------- ----------- --------------- -------- -------- ----- -------------------------- DATABASE 4 D04 4 May 22 2020 9:41:29:070AM emory::compression=-2::D04.DB.20200522.093515.000 * 0 no Success * May 22 2020 9:41:33:096AM DATABASE 4 D04 4 May 22 2020 9:41:29:070AM emory::compression=-2::D04.DB.20200522.093515.001 * 0 no Success * May 22 2020 9:41:33:096AM DATABASE 4 D04 4 May 22 2020 9:41:29:070AM emory::compression=-2::D04.DB.20200522.093515.002 * 0 no Success * May 22 2020 9:41:33:096AM DATABASE 4 D04 4 May 22 2020 9:41:29:070AM emory::compression=-2::D04.DB.20200522.093515.003 * 0 no Success * May 22 2020 9:41:33:096AM DATABASE 4 D04 4 May 22 2020 11:28:44:076AM emory::compression=9::D04.DB.20200522.105442.000 * 0 no Success * May 22 2020 11:29:29:353AM DATABASE 4 D04 4 May 22 2020 11:28:44:076AM emory::compression=9::D04.DB.20200522.105442.001 * 0 no Success * May 22 2020 11:29:29:353AM DATABASE 4 D04 4 May 22 2020 11:28:44:076AM emory::compression=9::D04.DB.20200522.105442.002 * 0 no Success * May 22 2020 11:29:29:353AM DATABASE 4 D04 4 May 22 2020 11:28:44:076AM emory::compression=9::D04.DB.20200522.105442.003 * 0 no Success * May 22 2020 11:29:29:353AM DATABASE 4 D04 0 May 22 2020 11:30:06:163AM dump.error * 0 no Error May 22 2020 11:30:06:163AM DATABASE 4 D04 4 May 22 2020 11:34:45:076AM emory::::D04.DB.20200522.113046.000 * 0 no Success * May 22 2020 11:34:53:513AM DATABASE 4 D04 4 May 22 2020 11:34:45:076AM emory::::D04.DB.20200522.113046.001 * 0 no Success * May 22 2020 11:34:53:513AM DATABASE 4 D04 4 May 22 2020 11:34:45:076AM emory::::D04.DB.20200522.113046.002 * 0 no Success * May 22 2020 11:34:53:513AM DATABASE 4 D04 4 May 22 2020 11:34:45:076AM emory::::D04.DB.20200522.113046.003 * 0 no Success * May 22 2020 11:34:53:513AM CONFIG ------ ALTER DATABASE -------------- (return status = 0)
Backup and restore your database
Once CxLink Backup agent is installed and configured on your SAP Database Server, you can initiate backup and recovery using SQL statements.
Using SQL statements
The following are examples of SQL statements that you can use to perform backup and recovery. For more details, refer to the SAP Sybase or SQL Reference guides.
Full Backups
dump database <DB_SID> using config = <config_name>
go
Incremental backups
dump database <DB_SID> cumulative
using config = <config_file>
with verify=header
go
Transaction Log backups
dump tran <DB_SID> using config = <config_name>
go
Restore your database
Get Load
LOAD DATABASE <DB_SID> WITH LISTONLY=LOAD_SQL
go
Get LOAD sequence (Point In Time)
LOAD DATABASE D04
WITH LISTONLY=LOAD_SQL,
UNTIL_TIME="may 25, 2020 18:33:43:866pm"
go
Header verification
LOAD DATABASE D04 FROM 'emory::compression=9::D04.DB.20200522.205006.000'
STRIPE ON 'emory::compression=9::D04.DB.20200522.205006.001'
STRIPE ON 'emory::compression=9::D04.DB.20200522.205006.002'
STRIPE ON 'emory::compression=9::D04.DB.20200522.205006.003'
WITH HEADERONLY
go
Dump History
View Dump records from command line
sp_dump_history [ @operation = {'list' | 'purge' | 'listfiles' | 'help'}]|
'listpurgefiles' | 'purgefiles' | 'upgrade' | 'downgrade'}][, @until_time =
'date'] [, @name = '<database or file name>'][, @dump_type = {'DATABASE' |
'TRAN[SACTION]' | 'CONFIG[URATION]' | 'CUM[ULATIVE]' | 'ALTERDB'}][, @status =
{'success' | 'fail' | 'deleted'}][, @file = '<filename>'][, @version = '1.0']
(return status = 0)
Using SAP DBACockpit
You can schedule your backup from the SAP DBACockpit by following these steps:
In SAP GUI, run transaction DBACOCKPIT.
Click on Schedule an Action under Favorites to open the scheduling wizard
Continue to start the configuration
- Select Database Dump from the action drop down list.
- Select the Database and the Dump Configuration. (For backint interfaces a dump configuration is mandatory. If you don't have any, please follow the steps in create dump configuration to create one.)
- Execute. Review the summary and execute the process.
- Finish. The job has been launched. You can check for the results in the SAP DBACockpit Logs.
Using SAP Control Center
Access the Administration Console in SAP Control Center, and follow these steps to perform your backup/restore operations:
Jump to
Schema Objects-Databases-User Databases, select your database and select Back Up
Select Backup using configuration and the Dump Configuration for your database. (For backint interfaces a dump configuration is mandatory. If you don't have any, please follow the steps in create dump configuration to create one.)
Type of Backup. Select the type of backup that you want to perform.
Options. Leave the use modified options option unckecked to use the configuration profile options.
Summary. Review the summary and finish the process.
Activity Log. You will see the backup output if you have selected not to schedule the job.
Where are your backup stored?
Your backups will be stored in the target bucket following these formatting:
<SAP_SID>/<DB>/<year>/<month>/<day>/<SID>.DB.<start_date><start_time>.<dump_file>
All backup files from the same backup will have the exact same suffix. These can help you to identify the objects of a specific backup.
Example:
HDB/SYSTEMDB/2020/05/01/D04.DB.20210713.164512.003