Check database recovery mode
Job details
| Name: | Check database recovery mode | |
| Platform: | Sqlserver | |
| Category: | Availability | |
| Description: | Checks if all databases are running in FULL or SIMPLE recovery mode. | |
| Long description: | Checks if all databases are running in FULL or SIMPLE recovery mode. This procedure can be configured to change the recovery mode for a database to a value defined by the configuration parameter ‘allow recovery mode’ (and then switch back to status OK). Only ONLINE databases are checked. | |
| Version: | 1.61 | |
| Default schedule: | 5 5,19 * * | |
| Requires engine install: | Yes | |
| Compatibility tag: | .[type=‘instance’ & databasetype=‘sqlserver’]/instance[maj_version > ‘2000′ & hasengine=‘YES’ & (engine_edition = ‘Microsoft SQL Server’ | engine_edition = ‘Azure SQL Managed Instance’)] |
Parameters
| Name | Default value | Description |
|---|---|---|
| ignore databases | model,msdb,master,tempdb,dbwatch | List of databases (separated by comma) which will be ignored from being checked (% sign can be used). |
| allow recovery mode | FULL | Returns warning if the database is not in the mode defined by this parameter. |
| change recovery mode | NO | If set to “YES“, the dbWatch alert will change the recovery mode for the database to the value defined by the configuration parameter “allow recovery mode”, and then switch back to status OK. |
Job Summary
- Purpose: The purpose of this job is to monitor and ensure that all online databases within the specified SQL Server or Azure SQL Managed Instance have their recovery modes set to either FULL or SIMPLE as defined by the configuration parameters.
- Why: This job is crucial because it ensures database recovery configurations adhere to specified standards, aiding in data recovery and compliance with backup and disaster recovery policies. Incorrect settings can jeopardize data recoverability and cause failure to meet audit or compliance regulations.
- Manual checking: You can manually check the recovery mode status by using SQL Server Management Studio or by executing the following SQL command:
SELECT name, recovery_model_desc FROM sys.databases WHERE name NOT IN ('model', 'msdb', 'master', 'tempdb', 'dbwatch');Implementation Details
This job:
- Excludes specific system and utility databases from checks.
- Can automatically adjust the recovery mode of databases to the configured state if enabled by the settings.
- Logs each operation in a history table for audit and review.
- Provides detailed feedback on each database’s compliance with the recovery mode setting.
Parameters Overview
- “ignore databases” — Specifies a comma-separated list of databases to exclude from the check.
- “allow recovery mode” — Defines the acceptable recovery mode (FULL or SIMPLE) for the job to enforce or verify.
- “change recovery mode” — Determines whether the job should automatically correct the recovery mode of non-compliant databases.
Dependencies and Operations
The key procedure “dbw_db_recovery_mode” and its associated history table “dbw_db_recovery_mode_histr” are crucial for the execution and logging of this task. Their operations involve:
- Gathering current configuration settings.
- Filtering databases based on the specified exclusions and checking their recovery modes.
- Optionally changing the recovery mode of databases and logging this change.
- Updating the system with results to reflect compliance status.
Scheduling
This job is scheduled to run at specific times twice daily, as indicated by the setting “5 5,19 * *”, ensuring regularly updated checks and adjustments if required.
Technical Implementation
The underlying SQL scripts involve:
- Creating a history tracking table.
- Defining and executing stored procedures that handle the checking of recovery modes against set parameters and make changes if configured to do so.
- Utilizing cursors for database iteration and system tables to fetch property statuses.
Reporting
The system generates reports indicating any changes made, current settings, and non-compliance instances. These reports help in auditing and tracking the consistency of database configurations against set recovery standards. Each entry in the report captures details such as database name, ID, the status before and after changes, and the exact timing of alterations.
Upgrades and Adjustments
Updates to this job involve modifications in the SQL implementation scripts to accommodate changes in database engine features or enhancements in dbWatch functionality. These changes ensure that the job remains compatible with new SQL Server or Azure Managed Instance versions, reflecting advancements in technology or changes in compliance demands.