Backup All databases
Job details
| Name: |
Backup All databases |
| Platform: |
Sqlserver |
| Category: |
Maintenance |
| Premium package: |
Maintenance package |
| Description: |
Takes backup of all application and system databases. |
| Long description: |
Takes backup of all application and system databases. |
| Version: |
2 |
| Default schedule: |
0 * * * |
| Requires engine install: |
Yes |
| Compatibility tag: |
.[type=‘instance’ & databasetype=‘sqlserver’]/instance[maj_version > ‘2000′ & hasengine=‘YES’]/.[engine_edition = ‘Microsoft SQL Server’ & eng_inst_priv = 0] |
Parameters
| Name |
Default value |
Description |
| backup directory |
|
Directory path for backup files. Exp. D:\Backup_db\ |
| delete old backup files (in hours) |
48 |
The task deletes backup files older than the parameter value (in hours). |
| ignore databases |
|
List over the database (separated by comma) which will be ignored from being backuped. |
| enable |
NO |
If set to “YES“ the task will run “backup database” command for every online database. This task is meant for Desktop editions of SQL Server where SQL Agent service is not running. Default value is “NO”. |
| backup device |
null |
Name of the backup device. If this parameter is configured (set to value “null”), the “backup directory” and “delete old backup files (in hours)” parameters will be ignored. Only DISK type devices are supported. |
| compress backup |
YES |
If set to “YES“ the “WITH COMPRESSION“ option will be added to “BACKUP DATABASE“ DDL command. COMPRESSION is not supported on Express Edition and MS SQL Server 2005. |
| create subdir |
YES |
Creates the sub directory (if it does not exist) for each database that is backed up. The xp_create_subdir procedure is used. |
Job Summary
- Purpose: The purpose of this job is to ensure the consistent backup of databases on specific SQL Server installations, managing both execution and any issues during the process.
- Why: Regular backups are crucial for disaster recovery and ensuring data integrity. This job helps automate the backup process to reduce manual interventions and errors, and it also maintains the hygiene of backup storage by cleaning up old backup files.
- Manual Checking: You can check the backup activity manually through SQL Server management tools or querying system tables to verify the existence and status of backups:
SELECT * FROM msdb.dbo.backupset
Major Elements of the Job
- “dbw_backup_databases” and “dbw_backup_databases_err_tab”: These are key database objects involved in the job. The former is likely a stored procedure executing the backups while the latter seems to be a logging table for backup errors.
- Parameters
- Backup directory changes, compression settings, and device-specific parameters handle where and how backups are saved.
Monitoring and Reporting
- Reports on backup details such as start time, duration, size, and device used which are vital for auditing and troubleshooting.
- Tracks backup failures specifically, capturing error messages and specifics about the failed operations which helps in immediate rectification.
Implementation Notes
- The stored procedures and commands involved in backup processes ensure flexibility in job execution with parameters influencing behavior like compression and target backup paths.
- Capabilities to ignore certain databases or adjust delete schedules demonstrate the adaptability of the job setup.
- Procedural upgrades from prior versions suggest enhancements like recognition of backup compression status and directory management improvements.
Technical Overview
- Dependency on “dbw_backup_databases” for executing backups and “dbw_backup_databases_err_tab” for error tracking shows a setup where both performance and error handling are prioritized.
- Dynamic SQL within stored procedures logged in dbw_backup_databases_err_tab supports real-time error management and logging, facilitating quick fixes.
- The job setup ensures only online, non-system databases are backed up, respecting database status and properties for selective processing.
System Requirements and Considerations
- Applicable to Microsoft SQL Server installations post-2000 versions.
- Engine non-privileged instances are included suggesting a focus on limited access environments like lower environments or specific secure installations.
Overall, the dbWatch task configures an in-depth approach to database backups, tailored specifically for SQL Server environments with detailed control over backup logistics, error handling, and automated system maintenance.