Dump database
Job details
| Name: | Dump database |
| Platform: | Sybase |
| Category: | Maintenance |
| Premium package: | Maintenance package |
| Description: | Runs backup of all databases using ‘dump database’ command. |
| Long description: | Runs backup of all databases using ‘dump database’ command. |
| Version: | 1.0 |
| Default schedule: | 50 4 * * |
| Requires engine install: | Yes |
| Compatibility tag: | .[type=‘instance’ & databasetype=‘sybase’]/instance[maj_version > ‘14′ & hasengine=‘YES’] |
Parameters
| Name | Default value | Description |
|---|---|---|
| ignore databases | tempdb | List of databases (separated by comma) which will be ignored from being backed up. |
| with options | init | dump database options to add to “with” clause. |
| backup path | Backup path. | |
| device name | dump | device name |
| return status | 0 | Return status when dump failed. |
Job Summary
- Purpose: The purpose of this monitoring job is to automate the backup of databases in a Sybase environment excluding certain specified databases.
- Why: This job is essential for maintaining data integrity and availability by ensuring that all databases are periodically backed up. Automated backups help prevent data loss in case of system failures or data corruption.
Job Description
- General Description: The task performs backups for all databases using the “dump database” command, excluding any listed in the specified ignore list.
- Key Features:
- Automates the database backup process.
- Allows exclusion of specific databases from the backup process.
- Provides options to customize the backup command with additional SQL clauses.
- Tracks the backup process in a history table for monitoring and verification.
Job Configuration and Dependencies
Dependencies and Cleanup
- Main Dependencies:
- “dbw_dump_db_proc” – Main task procedure.
- “dbw_dump_db_hist” – Table that records backup history.
- “dbw_dump_ignore_db” – Table used to specify databases to be ignored during the backup.
- Cleanup on Failures:
- If the job fails, specific cleanup actions such as ‘drop procedure dbw_dump_db_proc’ and ‘drop table db_stat_hist’ are triggered to maintain a clean environment.
SQL Type Parameters
- “ignore databases” – Databases to be excluded are specified.
- “with options” – Contains SQL options to be appended in the backup command.
- “backup path” – Specifies the physical path for storing the backups.
- “device name” – Specifies or names the device to be used for backup.
- “return status” – Indicates the return status used to determine the outcome of the backup attempt.
Execution Details
- Uses cursors to iterate over databases to back up, excluding specified ones.
- Captures start and end times of backups to calculate duration.
- Handles errors and updates the backup history table accordingly.
Reporting and Monitoring
- Report Template Description: Reports on the most recent status of database backups, including the start and end times, duration, and error codes if any.
- Monitoring: Automatic monitoring and updating of execution details such as number of backups completed, ignored databases, and elapsed time.
Execution and Implementation Code
- The job includes comprehensive SQL scripts to:
- Create necessary historical and configuration tables.
- Implement a stored procedure for the backup logic, which includes error handling and detailed logging.
- Drop temporary structures and clean historical data over a year old.
Backup Strategy Execution Details
- The actual backup command is constructed dynamically based on provided parameters and iteratively executed for each eligible database.
- The results and errors from each dump operation are recorded in the “dbw_dump_db_hist” table, allowing for detailed follow-up and analysis.
Compatibility and Installation Requirements
- This job is particularly tailored for instances of Sybase databases of major version greater than 14 with a specific engine configuration. Installation forcibly updates existing setups due to its critical nature in maintaining data safety and compliance with operational standards.