Database backup (SIMPLE)
Job details
Name: |
Database backup (SIMPLE) |
Platform: |
Sqlserver |
Category: |
Availability |
Description: |
This procedure analyzes the backup statistics (type D) from the msdb.dbo.backupset table for databases in SIMPLE recovery model (excluding the system databases: master, model and msdb). |
Long description: |
|
Version: |
2.1 |
Default schedule: |
50 5 * * |
Requires engine install: |
Yes |
Compatibility tag: |
.[type=‘instance’ & databasetype=‘sqlserver’]/instance[maj_version > ‘2000′ & hasengine=‘YES’ & alwayson_active=‘NO’ & eng_inst_priv = 0 & (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 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 ensure that all databases in the SIMPLE recovery model, excluding the system databases, are being backed up regularly. It checks for last backup dates from the msdb.dbo.backupset table and raises alerts based on configured thresholds.
- Why: This job is important to ensure data availability and protection against data loss. Monitoring backups, especially for databases in the SIMPLE recovery model, is crucial since these databases do not support point-in-time recovery. Failure to regularly backup could lead to significant data loss in case of a failure.
- Manual checking: You can check this manually in the data management studio by querying the backup history from the msdb.dbo.backupset table:
SELECT database_name, MAX(backup_finish_date) AS last_backup_date FROM msdb.dbo.backupset WHERE type = 'D' GROUP BY database_name;
Job Configuration Details
- “Database backup (SIMPLE)” task examines databases in a SIMPLE recovery model.
- It excludes system databases: master, model, and msdb.
- Alerts are generated based on the age of the last backup relative to a configurable threshold.
Task Execution Logic
- The procedure first determines databases to be excluded from checks based on user-defined parameters.
- Scheduled tasks check the last backup date for each database.
- Check includes a provision to ignore databases recently created (newer than a specified threshold).
- Databases without a recent backup (as per the threshold) are flagged, and details are compiled for reporting.
Alert Triggers
- An alarm or warning is triggered if the last backup date is older than the specified threshold.
- Separate statuses and detailed messages are prepared for databases with old backups and those missing backup records entirely.
Result Presentation
- The results include detailed listings of databases with their last backup date and duration since the last backup.
- Specific presentations include:
- Databases with over 48 hours old backup
- Databases missing backup records
- Most recent backup details for each database in a SIMPLE recovery model
Example of Output Table:
Database Name |
Last Backup |
Backup Age (hours) |
Database A |
2023-01-01 12:00:00 |
50 |
Database B |
2023-01-02 12:00:00 |
30 |
Dependencies and Additional Scripts
- The job leverages auxiliary tables and procedures for managing exclusions and handling data.
- Upgrades include validation and insertion routines for missing default values in parameters, ensuring backward compatibility and consistency.
Importance of Regular Monitoring
- Continuous monitoring of database backups is essential for operational security and compliance with data protection policies. Regular checks help identify potential issues before they escalate into significant problems, ensuring data integrity and accessibility.