Database backup
Job details
| Name: | Database backup | |
| Platform: | Sqlserver | |
| Category: | Availability | |
| Description: | This procedure analyzes the backup statistics (type D and I) from the msdb.dbo.backupset table (excluding the system databases: master, model and msdb). | |
| Long description: | This procedure analyzes the backup statistics (type D and I) from the msdb.dbo.backupset table (excluding the system databases: master, model and msdb). | |
| Version: | 2.1 | |
| Default schedule: | 10 6 * * | |
| Requires engine install: | Yes | |
| Compatibility tag: | .[type=‘instance’ & databasetype=‘sqlserver’]/instance[maj_version > ‘2000′ & maj_version < ‘2014′ & hasengine=‘YES’ & (engine_edition = ‘Microsoft SQL Server’ | engine_edition = ‘Azure SQL Managed Instance’)] |
Parameters
| Name | Default value | Description |
|---|---|---|
| ignore databases | dbwatch% | The list of databases to be ignored (separated by comma). |
| backup date threshold | 48 | A warning/alarm is triggered if the number of hours since the database was last backed up is greater than the parameter value. |
| return status when missing backup | 2 | Return status value (ALARM – 2, WARNING – 1, or OK – 0) when missing backup of a database. |
| return status when old backup | 2 | Return status value (ALARM – 2, WARNING – 1, or OK – 0) when the last backup date is older than the “backup date threshold” parameter value. |
| exclude databases in SIMPLE recovery mode | NO | If set to YES the alert will exclude all databases configured with SIMPLE recovery mode. |
| exclude databases if new | 24 | If the database has been created within the last 24 hours (default value), no warning/alarm will be triggered. |
Job Summary
- Purpose: The purpose of this job is to monitor and manage the backup process of SQL Server instances, specifically targeting those running on versions between 2000 and 2014.
- Why: This job is critical to ensure data availability and protect against data loss. It automates the monitoring of backup status and alerts the administration if backups are missing or outdated, which are essential for effective disaster recovery plans.
- Manual checking: You can check the backup status manually by executing specific queries on the msdb.dbo.backupset table to get the latest backup information for each database excluding the system databases.
Job Description and Detail
The task primarily focuses on databases from instances running SQL Server or Azure SQL Managed Instances, within the specified version range. It checks the last backup time against predetermined thresholds and provides alerts based on the configuration. The `dbw_backup_proc` procedure is used for this automation.
Exclusions in Monitoring
- Ignored databases: The job allows setting specific databases to be excluded from monitoring. This can include databases running operational workloads that do not store critical business information, or temporary databases.
- Databases with SIMPLE recovery model can optionally be excluded, depending on the parameter setting.
- Newly created databases: Databases created within a specified duration (default 24 hours) can be exempted from immediate backup requirement checks.
Technical Implementation
The procedure (`dbw_backup_proc`) performs series of checks:
- It identifies databases that have not been backed up within the given threshold (default 48 hours) and sets an alert status accordingly.
- It checks for databases with no available backup information in the msdb.dbo.backupset table.
- The procedure outputs details about databases that have outdated backups or those lacking backup records entirely.
- Conditions for exclusions, such as ignoring databases or excluding databases based on recovery models or recent creation, are also checked.
Dependency and Error Handling
- Depends on the existence of the `dbw_ignore_db` table which records the databases to be ignored during the backup checks.
- Contains elaborate error handling mechanisms to capture and log exceptions during the monitoring process.
Reporting and Visualization
A comprehensive report template is included:
- Database names and their backup status are logged.
- Specific presentations for:
- Old backups – Details databases with backups older than the specified threshold.
The job is scheduled to run daily at a specified time (default: 06:10), but this can be adjusted as needed. Notifications, either via DATABASE_MAIL or other configured alert mechanisms, are triggered based on the result of these checks, particularly when databases fail to meet the backup policies set in place.