Backup maintenance rule

Purpose:

• To ensure that critical data is protected and can be recovered in case of data loss, corruption, hardware failure, or disaster.

Why It’s Important:

• Backups allow you to restore your database to a consistent state after an unexpected issue.
• Regular backups (full, differential, or transaction log) help achieve Recovery Point Objectives (RPOs) and Recovery Time Objectives (RTOs).
• Without a proper backup strategy, there is a significant risk of data loss that could be catastrophic to business operations.

Backup Summary Quick Access + Include Exclude

Backup Rule Configuration Summary Backup Rule Summary Displays an overview of the configured backup rule, including its key settings and status. Include/Exclude Backup Rule Tab Allows granular control over which databases or servers are included or excluded from the backup rule. Multi-Tab Backup Plan Navigation If the backup plan includes multiple backup types (e.g., DATA, LOG, DIFF), a multi-tab interface will be available to navigate between the individual backup rule configurations. Configure Rule Opens the Rule Wizard for additional configuration options and advanced settings. Cancel/Rollback Changes Reverts any unsaved changes made to the backup rule setting. Save Updated Options Commits the changes made to the backup rule configuration.

Include Exclude backup rule tab

Include Exclude Configuration Options

1. Close the Dialog
   Closes the configuration dialog, discarding any unsaved changes.
2. Toggle the Rule
   Enables or disables the rule. Disabling the rule will stop its execution and remove it from associated servers.
3. View Common Database Source Selection
   See the common database source selection settings to fine-tune the databases or database groups included in the rule.
4. Include/Exclude Settings:
   1. Template Level:
      The Include/Exclude section is used to include or exclude specific servers from the rule.
   2. Custom Server Level:
      – The Include/Exclude section allows for granular control, enabling the inclusion or exclusion of specific databases.
      – his functionality is unavailable at the template level due to the association of multiple servers with a single template.

Backup rule wizard step 1 – Backup type

Backup Plan Configuration

1. Select Backup Types
   1. Choose from three available backup types (Full Data Backup, Transaction Log Backup, and Differential Backup) to include in the backup plan.
   2. During the subsequent steps of the wizard, a dedicated tab will be created for each selected backup type, allowing detailed configuration of individual backup rules.

Backup rule wizard step 2 – Main options

Backup Rule Configuration

1. Backup Destination:
   1. Directory:
      The most common option. Specify a local or network directory for storing backup files.
   2. URL:
      Available only in Custom Server Rules. Enables uploading backups directly to Azure Cloud Backup Storage.
2. Backup Root Directories:
   1. Define custom directory to chose a specific directory on the destination server, this option only exists in the custom server rule.
   2. If instance default is specified, the SQL Server default backup directory is used.
   3. Select the Global Directory Setting to use the globally configured backup directory.
3. Backup File Retention Period:
   1. Specify the time (in days or hours) after which backup files should be deleted.
   2. If no time is specified, backup files will not be deleted.
   3. Database backups include a verification check to ensure that transaction log backups newer than the latest full or differential backup are not deleted.
4. Old Backup File Deletion:
   1. Choose when to delete old backup files:
      – Before Backup: Delete old backup files before performing the new backup.
      – After Backup: Delete old backup files after completing the backup and verification. If the backup or verification fails, no files are deleted (default behavior).
5. Specify Database Order:
   Define the order in which databases will be backed up:

   Value	Description
   NULL	Default: Databases are backed up in the specified order, then sorted by database name in ascending order.
   DATABASE_NAME_ASC	Sort by database name in ascending order.
   DATABASE_NAME_DESC	Sort by database name in descending order.
   DATABASE_SIZE_ASC	Sort by database size in ascending order.
   DATABASE_SIZE_DESC	Sort by database size in descending

6. Navigation Controls:
   Use navigation buttons to move between configuration steps or tabs for backup types.

Backup rule wizard step 3 – Additional options

Backup Rule Advanced Options

1. Toggle Options: Configure additional settings to customize the backup behavior:
   1. Init: Specify whether the backup file should overwrite existing files.
   2. Verify Backup: Verify the integrity of the backup after completion.
   3. Checksum: Enable backup checksums for error detection.
   4. Copy-Only: Perform a copy-only backup, which does not affect the sequence of conventional SQL Server backups.
   5. Compress: Compress the backup file. If unspecified, the backup compression default in sys.configurations will be used.
   6. Format: Specify whether a new media header should be created.
   7. Directory Check: Verify if the backup root directory exists before executing the backup.
   8. Read/Write Filegroups: Backup the primary filegroup and any read/ write filegroups.
2. Database Selection – READ_ONLY / READ_WRITE:
   Choose which databases to include based on their access mode:
   1. All Databases: Include both READ_ONLY and READ_WRITE databases (default).
   2. Read-Only Databases: Include only databases marked as READ_ ONLY.
   3. Read/Write Databases: Include only databases marked as READ_ WRITE.
   4. The is_read_only column in sys.databases is used to determine the database type.
3. Number of Backup Files:
   Specify the number of backup files to create. The default is equal to the number of backup directories specified, with a maximum limit of 64 files.
4. Backup Description:
   Provide a description for the backup to document its purpose or any relevant details.
5. Navigation Controls:
   Use navigation buttons to move between configuration steps or review backup settings.

Backup rule wizard step 4 – Directory & File Structure

File Format Name Options

1. Custom File Name Format:
   Define a unique file name format for backup files. Options include:
   1. Custom Format: Create a personalized naming convention.
   2. Availability Group File Name Format: Use standardized formats for availability groups.
   3. Directory-Based Format: Apply naming conventions based on the directory structure.
2. Directory Structure Control:
   To manage the directory structure for backups, configure settings in Global Settings.
3. Format Customization:
   1. Selecting any file name format option will open a popup dialog.
   2. Use predefined constants or create custom constants to modify the file name format as required.

File Name Format Customization

1. Available Constant List
   Provides a list of predefined constants that can be used to structure the file name format (e.g., <ServiceName>, <ClusterName>, <Year>).
2. Selected Constant List
   Displays the constants currently chosen for the file name format. These constants will determine the structure of the backup file names.
3. Free-Form Edit of Final Structure
   1. Allows manual editing of the file name format by combining selected constants with custom text or separators.
   2. The final structure preview will update dynamically based on your inputs.

Backup rule wizard step 5 – Mirror

Use this option to upload the backup file to another site.

Backup Type Configuration

1. Backup Type Tab Navigation
   Navigate between different backup types (e.g., Full, Log, Differential) using the tab interface for easy configuration of individual backup rules.
2. Mirror Site Backup Destination Options
   Configure the backup destination for a mirror site. These options are similar to the primary options in Step 2. Backup Destinations, allowing selection of a custom local directory, instance default directory or the global setting’s master backup directory.
3. Mirror Site Directory Options
   Specify the directory for the mirror site backup. These settings mirror the Step 2 Directory Options.
4. Cleanup Time
   Define the retention period for mirror site backups. These options are consistent with the Step 2 Cleanup Time, allowing you to specify the time in days or hours after which old backup files are deleted.
5. Cleanup Mode
   Determine whether old backup files are deleted before or after the backup operation. These settings are identical to those in Step 2 Cleanup Mode, ensuring consistency in cleanup behavior.

Backup rule wizard step 6 – Schedules

See Scheduling options in UI for details on how to use the schedule.

Differences between template backup rule to custom server backup rule

Maintenance Templates vs. Custom Server Configuration

A Maintenance Template is associated with one or more servers and is designed to provide a general framework for maintenance rules. It allows for efficient management of multiple servers by applying consistent configurations across them.

At the Custom Server Level, you can override template settings and define more specific configurations tailored to the unique requirements of an individual server. This level of customization ensures flexibility while maintaining the foundational structure provided by the template.

Custom server – Include Exclude backup rule tab

Backup Rule Custom Server Include / Exclude Configuration Options

1. Toggle Rule Enable/Disable
   Activate or deactivate the selected rule for the associated server.
2. Select Availability Groups
   Choose from a list of availability groups configured on the server to apply the rule to specific groups only.
3. Include/Exclude Databases
   Customize the rule by including or excluding specific databases on the server based on the requirements.
4. Final Database List
   A comprehensive list of databases is generated based on the availability group selection and inclusion/exclusion criteria, providing a clear overview of the databases affected by the rule.

In Step 2 of the backup wizard under custom server configuration, the ‘custom directory’ option will exist to allow the user to select a specific directory.

Database Backup Encryption Options (only in custom server)

1. Enable/Disable Encryption
   Toggle the encryption feature for database backups.
2. Select Encryption Algorithm
   Choose an encryption algorithm from the list of supported options (e.g., AES-128, AES-256, Triple DES).
3. Select Server Certificate
   Choose a server certificate for encryption. The list of available certificates is retrieved from sys.certificates on the server.