Push Backup to Shared Devices
Job details
| Name: | Push Backup to Shared Devices |
| Platform: | Sqlserver |
| Category: | Maintenance |
| Premium package: | Maintenance package |
| Description: | Performs scheduled backups of selected databases to a shared network backup device, making them available for remote restore by other SQL Server instances. |
| Long description: | Performs scheduled backups of selected databases to a shared network backup device, making them available for remote restore by other SQL Server instances. |
| Version: | 2 |
| Default schedule: | 0 * * * |
| Requires engine install: | Yes |
| Compatibility tag: | .[type=‘instance’ & databasetype=‘sqlserver’]/instance[maj_version > ‘2005′ & hasengine=‘YES’]/.[engine_edition = ‘Microsoft SQL Server’ & eng_inst_priv = 0] |
Parameters
| Name | Default value | Description |
|---|---|---|
| device location | null | Shared location path for backup device files. Exp. \\Backup_db\. |
| ignore databases | A comma-separated list of databases to be excluded from the backup. | |
| enable | NO | A safety toggle that controls whether the job is allowed to create backup devices and perform backups. Set to YES to activate the job. If set to NO, the job will skip execution, preventing unintended device creation or backups. |
| compress backup | YES | If set to “YES“ the “WITH COMPRESSION“ option will be added to “BACKUP DATABASE“ DDL command. COMPRESSION is not supported on Express Edition and MS SQL Server 2005. |
| include databases | % | A comma-separated list of databases to include in the backup. |
| return status | 1 | Return status value (ALARM – 2, WARNING – 1, or OK – 0) when the procedure returns errors from one or more databases. |
Job Summary
- Purpose: The purpose of this monitoring job is to automate the process of backing up databases to a shared network device, enabling the databases to be restored remotely on other SQL Server instances.
- Why: This job is critical because consistent and regular backups are essential for disaster recovery plans and to ensure data continuity. Automating this task reduces the risk of human error, ensures backups are performed at scheduled intervals, and maintains backup consistency.
- Manual checking: You can manually check the backup status in the database by issuing SQL commands that pertain to the backup history or the job’s execution history.
Job Details
Dependencies
- Main Procedure: dbw_push_backup_to_dev
- Error Table: dbw_push_backup_err_tab
- Backup Information: dbw_push_backup_info
- Database File Information: dbw_push_dbfile_info
- Login and DB User Info: dbw_push_login_info
- Job Execution History: dbw_push_backup_exec_histr
All dependencies listed above include a cleanup action upon failure, ensuring that erroneous or partial data does not remain that could affect subsequent job executions.
Database Backup Logic Implementation
| Step | Description | SQL Commands |
|---|---|---|
| 1 | Initialization of backup configurations | @device_location specified. @include_db and @ignore_db determine databases to back up or exclude. |
| 2 | Backup Execution | For each database not in the ignore list and in the include list, backups are performed using the SQL command “BACKUP DATABASE [DBName] TO [Device] WITH [Options]” where Options can include compression settings depending on server capabilities. |
| 3 | Error Handling | Errors during the backup process are captured in the dbw_push_backup_err_tab table along with details like database name and the error message. |
| 4 | Post-backup Reporting | Updates and logs details about the job execution, databases backed up, and any exceptions encountered in the dbw_push_backup_exec_histr table. |
Post-execution Details
Details based on backup execution are stored in various designated tables:
- dbw_push_backup_err_tab for any errors.
- dbw_push_backup_info for metadata regarding each backup.
- dbw_push_dbfile_info for the details of database files.
- dbw_push_login_info which holds login details relevant for the database in context.
The job execution stores summary records in dbw_push_backup_exec_histr, which includes the number of databases backed up, execution start and end times, along with other execution details.
Scheduled Execution
This job is scheduled to execute at:
bc. 0 * * * *
which implies it runs hourly, ensuring backups are frequently updated thus reducing potential data loss.
Upgrade Considerations
When upgrading from a previous version:
- Ensure consistency in the database table structure and stored procedures to align with new requirements of the job.
Infrastructure Conditions
The job filters instances to determine eligibility:
- SQL Server versions above 2005.
- Server editions other than ‘Express Edition’ due to limitations in backup compression.
This ensures that the job only attempts to execute in environments that meet the required criteria, avoiding potential errors or failures in unsupported setups.
Additional Tools
While this is an automated job, manual checks and scripts may be required occasionally to verify the integrity and successful execution of backups or to perform maintenance tasks related to the job’s operations. Using SQL Server Management Studio (SSMS) or equivalent database tools will be essential in such cases.