Database backup (2000)
Job details
| Name: | Database backup (2000) |
| Platform: | Sqlserver |
| Category: | Availability |
| Description: | This procedure analyzed the backup statistics from msdb.dbo.backupset table (excluding system databases: master, model and msdb). |
| Long description: | |
| Version: | 1.2 |
| Default schedule: | 10 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 |
|---|---|---|
| ignore databases | dbwatch | The list of databases to be ignored from being checked (separated by comma). |
| warning threshold | 48 | A warning is triggered if the amount of hours, since the database have been backed up, |
Job Summary
- Purpose: The purpose of this job is to monitor database backups on Microsoft SQL Server instances, specifically those running version 2000. It checks the recency of backups and identifies databases without any backup history.
- Why: This job is crucial for ensuring data recovery capabilities by monitoring the age of backups and spotting any missed backups, especially for critical databases excluding the system databases (master, model, msdb). If backups are not recent or missing, it could lead to significant data loss in case of system failure.
- Manual checking: You can check this manually in the database by issuing this SQL command:
SELECT msdb.dbo.backupset.database_name "Database name", MAX(msdb.dbo.backupset.backup_finish_date) AS "Last backup", DATEDIFF(hh, MAX(msdb.dbo.backupset.backup_finish_date), GETDATE()) AS "Backup Age" FROM msdb.dbo.backupset WHERE msdb.dbo.backupset.type = 'D' and msdb.dbo.backupset.database_name not in ('master','model','msdb') GROUP BY msdb.dbo.backupset.database_name HAVING (MAX(msdb.dbo.backupset.backup_finish_date) < DATEADD(hh, -@parameter_wt, GETDATE()))Implementation Details
This monitoring job involves several SQL procedures to:
- Identify databases that have not been backed up within a certain threshold (default 48 hours).
- Track which databases have never been backed up.
The SQL code provided sets up various checks and updates that monitor database backup statuses by querying internal SQL Server tables such as msdb.dbo.backupset and master.dbo.sysdatabases.
Dependency Configuration
The job has dependencies, specifically:
- dbw_backup_proc – the main procedure that conducts backup checks.
- dbw_ignore_db – a table utilized to track databases that should be excluded from checks.
Reporting
The configuration sets up several reports:
- Old backups – Lists databases with backups older than a specified threshold.
- Databases missing backup – Show databases which have no backup records.
- Database backups – Detail recent backup information for each database, including duration, size, and device locations.
These report configurations aim to present the data in a visually accessible format, incorporating tables and lists that outline database names, backup timings, and the elapsed time since the last backup, among other details.
Maintenance and Upgrades
Upgrades from previous versions (1.0 to 1.2) focus on alterations in the SQL procedure handling backup monitoring, reflecting changes in logic that might involve parameter enhancements or procedural optimizations for efficiency.
Conclusion
The dbWatch Control Center Job for monitoring SQL Server 2000 database backups is designed to ensure reliable, up-to-date backup data across managed databases, helping to safeguard against data loss and maintain operational continuity efficiently through regular monitoring, reporting, and alert management.