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.4 |
| Default schedule: |
10 6 * * |
| Requires engine install: |
Yes |
| Compatibility tag: |
.[type=‘instance’ & databasetype=‘sqlserver’]/instance[maj_version > ‘2012′ & 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. |
| exclude databases in Always On availability groups |
YES |
If set to YES the alert will exclude all databases in Always On availability groups. |
| exclude log shipping databases |
YES |
If set to YES the alert will exclude all databases in a log shipping configuration (databases in msdb.dbo.log_shipping_secondary_databases table). |
| exclude standby databases |
YES |
If set to YES the alert will exclude all databases in state online as read-only, with restore log allowed (IsInStandBy property set to TRUE). |
Job Summary
- Purpose: The purpose of this job is to monitor the backup status of databases in SQL Server, focusing on the timeliness and existence of backups.
- Why: This job is vital to ensure that all critical databases are regularly backed up. It helps mitigate risks associated with data loss due to system failures or other disasters by ensuring that recent backups are available. If the backups are not up to date or missing, it may result in significant data loss and affect business continuity.
- Manual checking: You can check this manually in the database by issuing these SQL commands:
SELECT database_name, MAX(backup_finish_date) AS last_backup_date FROM msdb.dbo.backupset WHERE type IN ('D','I') AND database_name NOT IN ('master', 'model', 'msdb') GROUP BY database_name;
Report Templates and Presentations
- There are multiple report presentations defined to show different aspects of backups such as databases with old backups, missing backups, and the most recent backups.
Old Backups
| Database Name |
Status |
Last Backup |
Backup Age |
| ExampleDB |
Online |
2023-01-01 23:59 |
48 |
Missing Backups
| Database Name |
Created |
| NewDB |
2023-01-20 |
Most Recent Backups
| Database Name |
Backup Finish |
Backup Start |
Elapsed Time |
Expiration Date |
Size (MB) |
Device Name |
Physical Location |
Set Name |
Description |
| ExampleDB |
2023-01-01 23:59 |
2023-01-01 22:00 |
119 |
2023-06-01 |
5120 |
Device001 |
C:\backups\file.bak |
Set001 |
Full backup |
Implementation Overview
This job includes creating or altering necessary stored procedures and tables to facilitate this checking. Specific details are:
- A table called “dbw_ignore_db” is used to keep a list of databases that should be ignored in the backup checks.
- A stored procedure “dbw_backup_proc” is created to handle the process of checking each database against the criteria set by the parameters and generating appropriate reports based on the results.
- Multiple cursors are used within the procedure to fetch and process backup data from the “msdb.dbo.backupset” and other relevant tables to determine the backup status of each database.
Dependencies and Cleanup
The task has dependencies on its own previously defined procedures, and in cases of a failure during task execution, a cleanup is performed by truncating the “dbw_ignore_db” table to reset the state.