Automating Database Dumps in SAP ASE This blog post guides you through automating database and transaction log backups for your SAP system using DBACockpit in SAP Sybase ASE version 15.7.0.021 or higher. We'll focus on using the local file system for intermediate storage. Prerequisites: 1. Administrative access to the SAP ASE server. Understanding of SAP ASE utilities like dump database and dump transaction. Steps: Create a Storage Location: ◉ Designate a dedicated file system to store the dump files. ◉ Consider factors like available disk space and redundancy. Configure Dump Settings: Create a dump configuration file specifying parameters like: ◉ Dump type (full database or transaction log) ◉ Compression level ◉ Retention period for backups Schedule Database Backups: ◉ Use the isql utility to create jobs and schedules for each database. ◉ The job script should execute the dump database command with the appropriate configuration file and database name. ◉ Define a schedule that aligns with your backup requirements (e.g., daily, weekly). Automate Transaction Log Backups (Optional): ◉ Configure a threshold action to automatically dump the transaction log when its fill level reaches a predefined limit. ◉ This ensures minimal data loss in case of a server crash. Additional Considerations: Security: Restrict access to the dump directory and configuration files. Verification: Regularly test your backup process to ensure successful dumps and retrievals. Rotation: Implement a backup rotation strategy to manage storage space and avoid overwriting critical backups. By following these steps, you can automate database dumps in SAP ASE, ensuring a robust and reliable backup solution. Creating a Dedicated File System for Dumps: To safeguard your database against data loss in the event of device failures or filesystem errors, it's crucial to store database dumps on a separate file system that is not located on the same device as the database itself. This crucial practice ensures that your backups remain intact even if the database device becomes inaccessible. Here's a recommended approach for establishing separate file systems for dumps on UNIX/Linux systems: Create Directories: ◉ Within the /sybase/ directory, create the following directories: ◉ /sybase//backups for database dumps ◉ /sybase//log_archives for transaction log dumps While mounting the file systems within /sybase/ is convenient, it's not a strict requirement. You have the flexibility to choose alternate locations that best suit your specific environment and disaster recovery strategies. Mount separate file systems onto these directories. This isolates the dumps from any issues affecting the database device. Note: Ensure that your database backup and log archiving processes are configured to write dumps to these designated directories. Create a dump configuration: With The SAP ASE 15.7.0.021 and higher you can configure database and transaction log dumps. A dump configuration is stored in the ASE server configuration file and specifies all the necessary configuration parameters for the dump, such as a target directory where dumps are stored, the compression rate, and other parameters.The dump configuration can get created with stored procedure sp_config_dump. It has to be executed from database 'master' The below example steps shows the necessary commands to create a dump configuration 'SMADB' for database dumps and another dump configuration 'SMALOG' for transaction log dumps. Configuration SMADB: use master go exec sp_config_dump @config_name='SMADB', @stripe_dir = '/backup/Backup/SOL' , @compression = '101' , @verify = 'header' go Configuration SMALOG: use master go exec sp_config_dump @config_name='SMALOG', @stripe_dir = '/backup/Backup/SOL/SMALOG' , @compression = '101' , @verify = 'header' go The dump configurations SMADB and SMALOG are created. The target directories for storing dumps are specified. Compression = 101 is used for the dumps, and the header option is specified. Database and transaction dumps can now be performed using the using config syntax Example Config Syntax: dump database SMA using config = 'SMADB' Dumps database SMA using dump configuration SMADB. dump transaction SMA using config = 'SMALOG' Dumps transaction log of database SMA using dump configuration P01LOG. If you want to ensure that database dumps are only performed using the defined dump configuration, set the ASE configuration parameter enforce dump configuration to 1. Data backup: Log Backup: Note: Here we disabled the log in this system. But we configured syntax working fine. Create and schedule ASE jobs to dump databases and transaction logs: Use the DBA Planning Calendar in the DBACOCKPIT to automatically call the DUMP DATABASE and the DUMP TRANSACTION commands. In order to minimize the potential for transaction loss in the case of a disk error, SAP recommend that you schedule a DUMP TRANSACTION command frequently in a production environment. 1. In SAP GUI, run transaction DBACOCKPIT. 2. Click on "Schedule an Action" under Favorites to open the scheduling wizard 3. Complete the wizard: on page "Introduction", click "Continue". 4. on page "Job Selection", choose Database Dump or Transaction Dump if you want to backup database or transaction log respectively, and select "Start immediately", a given date, or "Schedule as Recurring Action" as needed: 5. Then choose database name, dump configuration name or Manual option if there is no dump configuration defined, and additional parameters. 6. on page "Summary", review the settings and click "Execute" to save the Scheduled Action job. 7. Job has been scheduled and running. 8. After completion of the wizard the newly scheduled job shows up in the DBA Planning calendar. 9. Follow the same steps to take SMALOG backup or schedule the LOG backup regular interval based on you requirement. Set up a threshold action for the transaction log: Also we need to setting up a scheduled DUMP TRANSACTION command, it is also strongly recommended that you set up a threshold action that triggers a DUMP TRANSACTION command whenever a threshold (fill level) in the log segment has been reached.