Pull Backup from Shared Devices
Job details
| Name: | Pull Backup from Shared Devices |
| Platform: | Sqlserver |
| Category: | Maintenance |
| Premium package: | Maintenance package |
| Description: | Restores selected databases from a shared network backup device created by another SQL Server instance, supporting cross-instance recovery workflows. |
| Long description: | Restores selected databases from a shared network backup device created by another SQL Server instance, supporting cross-instance recovery workflows. |
| Version: | 2 |
| Default schedule: | 0 * * * |
| Requires engine install: | Yes |
| Compatibility tag: | .[type=‘instance’ & databasetype=‘sqlserver’]/instance[maj_version > ‘2005′ & hasengine=‘YES’]/.[engine_edition = ‘Microsoft SQL Server’ & eng_inst_priv = 0] |
Parameters
| Name | Default value | Description |
|---|---|---|
| ignore databases | A comma-separated list of databases to be excluded from the backup. | |
| enable | NO | If set to “YES“ the task will run “restore database” command from every shared device. |
| include databases | % | A comma-separated list of databases to include in the backup. |
| return status | 1 | Return status value (ALARM – 2, WARNING – 1, or OK – 0) when the procedure returns errors from one or more databases. |
| return status when old backup | 1 | Return status value (ALARM – 2, WARNING – 1, or OK – 0) when database backup on shared device is older then parameter value “backup time threshold”. |
| backup time threshold | 24 | Defines the maximum allowed age (in hours) of the backup to be restored. If the backup is older than this threshold, the job can trigger a warning or error depending on the “return status when old backup” setting. |
| data file location | Specifies the target folder path where the database data files (e.g., .mdf, .ndf) should be restored. The job will move the data files to this location during restore. | |
| log file location | Specifies the target folder path where the transaction log file (.ldf) should be restored. The job will move the log file to this location during restore. | |
| dbWatch instances | A comma-separated list of dbWatch-registered SQL Server instance names. The job will attempt to restore databases from these instances, using backups found on the shared device pushed by the corresponding “Push Backup to Shared Devices” job. | |
| adjust compatibility on restore | NO | If enabled (value “YES“), after restoring a database, its compatibility level will be automatically adjusted to match the default compatibility level of the new instance (the same as the master database). |
| create missing logins | NO | If set to YES, the job will attempt to create logins associated with the restored databases. |
Job Summary
- Purpose: This job facilitates the restoration of databases from shared backup devices across different SQL Server instances, additionally managing related logins to avoid orphan users. It enables the restoration process or environment switch for databases within an extensive corporate setup where backups are centralized.
- Why: This job is crucial to ensure data redundancy, disaster recovery compliance, and timely recovery in the event of data loss. Restoring databases from a shared backup device minimizes downtime and loss of productivity by providing a quick switch to a backup system. It also ensures logins and user access rights are consistent across different environments, preventing access issues post-restore.
- Manual checking: You can check the restored databases and associated logins manually through the admin panel of the dbWatch Control Center or directly querying the specific SQL tables to verify details like recovery status, backup used for recovery, etc.
Technical Details and Dependencies
- Core Procedure: “dbw_pull_backup_from_dev” serves as the main driver that orchestrates the entire backup restoration process including validation, actual restore and post-restore cleanup.
- Dependencies: Includes several critical housekeeping tasks and checks, ensuring no traces of failed restore attempts exist.
- Cleanup Strategy: Any failed restoration attempts will ensure a cleanup to maintain system integrity and prevent cluttering or misguidance due to incorrect or partial data.
Parameters Involved (No specific descriptions provided)
- Include: Database identifiers or patterns which determine what databases to consider for backup restoration.
- Exclude: Database identifiers that are to be ignored during the backup restoration, even if they exist within the backup set.
- Return Conditions: Various status codes representing different outcomes of the restore process such as successful, warning (issues encountered but not critical), and failure.
- Time Thresholds: Parameters setting limits on the age of backups that can be considered valid for restoration attempts.
- Data and Log Locations: Configuration specifying where the database and log files need to be placed post-restoration.
- dbWatch Instances: Specific dbWatch-managed SQL Server instances to interact with for pulling backups or managing logins.
- Compatibility and Logins Settings: Parameters that determine whether to adjust database compatibility to match the environment and whether to actively manage SQL logins post-restoration.
Usage Scenarios
- Routine Disaster Recovery Drills: Ensuring the backups are not only taken but are also valid and can be restored.
- Migrating Databases Across Servers: Useful in scenarios where databases need to be transitioned between servers, possibly across geographical locations.
- Restoration Validation: After a backup routine, validating if the backups taken are valid and restorable, often part of compliance requirements.
Impact and Reporting
- Monitoring and Alerts: The job setup in dbWatch will ensure administrators are alerted based on severity codes driven by the defined parameters.
- Reporting: Includes detailed logs of each attempt, whether successful or not, and provides a comprehensive summary including which databases were restored, the source of the restore, associated login handling, and errors, if any.
- Optimization: Continuous refinements based on log reviews which help in tuning the backup strategies and restoration processes.
Conclusion
Utilizing “Pull Backup from Shared Devices,” DBAs ensure business continuity by enforcing and verifying backup strategies across SQL Server instances managed under dbWatch, making it a pivotal part of the IT infrastructure management in large-scale environments.