Use this Knowledge Script to monitor SQL Server backup jobs. Using this script, administrators can track data backup activities optimally.
This Knowledge Script uses the SQL Server error logs (Errorlog, Errorlog.* in the SQL Server log folder). Ensure that the user has read permission to this files/folders.
On the first job iteration, this script sets a starting point for future log scanning and does not scan the existing entries in the logs. Therefore, it does not return any results on the first iteration. As it continues to run at the interval specified in the Schedule tab, this script scans the logs for any new entries created since the last time it checked.
This script raises an event if the number of successful backup records exceeds the threshold you specify, and if the backup fails for any reason.
SQL Server instance
The default interval for this script is Every hour.
Set the following parameters as needed:
Description |
How to Set It |
---|---|
General Settings |
|
Job Failure Notification |
|
Raise event if job fails unexpectedly? |
Select Yes to raise an event if the SQLServer_BackupJob fails unexpectedly. The default is Yes. |
Event severity when job fails unexpectedly |
Set the event severity level, from 1 to 40, to indicate the importance of an event that is raised when the job fails unexpectedly. The default is 5. |
Additional Settings |
|
Event Details |
|
Event detail format |
Select the format in which you want to display the event detail. You can select from HTML Table or Plain Text. The default is HTML Table. |
Monitor Backup Jobs |
|
Event Notification |
|
Raise event if backup job fails? |
Select Yes to raise an event if a backup job fails. The default is Yes. |
Event severity when backup job fails |
Set the event severity level, from 1 to 40, to indicate the importance of an event that is raised when the backup job fails. The default is 5. |
Threshold -- Maximum failed backup jobs |
Specify the number of backup jobs that should get failed before raising an event. The default is 0. |
Raise event if successful backup jobs exceed threshold? |
Select Yes to raise an event if the number of successful backup jobs exceeds the threshold. The default is unselected. |
Event severity when successful backup jobs exceed threshold |
Set the event severity level, from 1 to 40, to indicate the importance of an event in which the number of successful backup jobs exceeds the threshold. The default is 15. |
Threshold -- Maximum successful backup jobs |
Specify the number of backup jobs that should be successful before raising an event. The default is 10. |
Data Collection |
|
Collect data for failed backup jobs? |
Select Yes to collect backup jobs data for charts and reports. If enabled, data collection returns the number of failed backup jobs. The default is unselected. |
Collect data for successful backup jobs? |
Select Yes to collect backup jobs data for charts and reports. If enabled, data collection returns the number of successful backup jobs. The default is unselected. |