Database backup (system dbs)
Job details
| Name: | Database backup (system dbs) |
| Platform: | Sqlserver |
| Category: | Availability |
| Description: | This procedure analyzed the system database backups statistics from msdb.dbo.backupset table. |
| Long description: | |
| Version: | 1.1 |
| Default schedule: | 05 6 * * |
| Requires engine install: | Yes |
| Compatibility tag: | .[type=‘instance’ & databasetype=‘sqlserver’]/instance[maj_version = ‘2000′ & hasengine=‘YES’ & alwayson_active=‘NO’ & engine_edition = ‘Microsoft SQL Server’] |
Parameters
| Name | Default value | Description |
|---|---|---|
| warning threshold | 168 | A warning is triggered if the amount of hours (default 168 hours – 1 week), since the system database have been backed up, |
Job Summary
- Purpose: The job is designed to monitor and report on the backup status of system databases (master, model, and msdb) in Microsoft SQL Server instances specifically those with a major version of 2000. It ensures these critical databases are backed up regularly, and flags any backups that are older than a specified threshold or missing.
- Why: Regular backup of system databases is crucial for disaster recovery and maintaining the integrity of SQL Server environments. This job helps in identifying any potential backup issues early, allowing for timely remediation to avoid data loss or downtime.
- Manual checking: You can manually check the backup status by running the following SQL commands:
SELECT msdb.dbo.backupset.database_name, MAX(msdb.dbo.backupset.backup_finish_date) AS last_db_backup_date FROM msdb.dbo.backupset WHERE msdb.dbo.backupset.type = 'D' and database_name in ('master','model','msdb') GROUP BY msdb.dbo.backupset.database_name;Implementation Details
The monitoring job utilizes a stored procedure named “dbw_sys_backup_proc” that executes several checks:
- Check for databases (master, model, msdb) that have not been backed up within a specified number of hours (warning threshold).
- List any system databases that do not have any backup history.
The procedure includes logic to perform these checks and update the monitoring system with details about any issues found. The outputs include:
- Number of databases not backed up since the last set hours.
- Names of databases without any backup information.
Additionally, the procedure records the status (backed up/not backed up) and provides a detailed message describing either the last backup time or the absence of backup.
Reporting
The job generates reports showing:
- “System databases backup statistics”
- Details and statistics for last backups of the system databases.
- Highlighting system databases with backups that are older than 48 hours.
- Any system database missing in backup records.
The reports include tables and visual presentations to easily identify and understand the backup status of each system database. Columns in these reports display various information such as database name, last backup date, and backup age.
The provided implementations and reporting ensure ongoing surveillance of the backup health of key databases, essential for maintaining corporate standards of data aversion and database management efficiency.
Dependencies and Scheduling
- The job has a self-dependency with the “dbw_sys_backup_proc” ensuring that it can update its status based on internal logic.
- Scheduled to run daily at 6 am (default), ensuring that backup status is checked regularly every 24 hours.
Maintaining a routine check via this job aids in preempting potential risks associated with the lack of timely database backups, reinforcing data protection strategies for the organization.