Home
  •  

Database Backup Status

Microsoft.SQLServer.2008.Database.DBBackupStatusMonitor (UnitMonitor)

This monitor checks the status of the database backup as reported by Microsoft® SQL Server™.

Knowledge Base article:

Summary

This monitor checks the existence and age of database backup as reported by Microsoft® SQL Server™. This is performed by running a query against the master database of the SQL instance and returning the age of the database backup.

Causes

Database has not been backed up for too long.

Resolutions

You should perform a database backup to resolve the issue.

To eliminate this issue in future it is recommended to schedule an automated backup according to the needs of the application and business requirements.

Additional

Database Backup Status monitor has no logic to track whether the database is a secondary replica or not.

Since AOG has an advanced backup logic which requires a backup for at least one of the databases involved, the monitor may generate false positive alerts.

The monitor is disabled by default and if you want to enable the monitoring scenario for your environment, it is recommended to keep the monitor disabled for all servers which are not used for storing the database backup.

External

Best Practice recommendations for SQL Server Database Backups

Overrideable Parameters

Name

Description

Default Value

Alert Priority

Defines Alert Priority.

Normal

Alert Severity

Defines Alert Severity.

Error

Backup Period (days)

Backup Period

7

Enabled

Enables or disables the workflow.

No

Generates Alerts

Defines whether the workflow generates an Alert.

Yes

Interval (seconds)

The recurring interval of time in seconds in which to run the workflow.

86400

Synchronization Time

The synchronization time specified by using a 24-hour format. May be omitted.

 

Timeout (seconds)

Specifies the time the workflow is allowed to run before being closed and marked as failed.

300

Element properties:

TargetMicrosoft.SQLServer.2008.Database
Parent MonitorSystem.Health.AvailabilityState
CategoryAvailabilityHealth
EnabledFalse
Alert GenerateTrue
Alert SeverityError
Alert PriorityNormal
Alert Auto ResolveTrue
Monitor TypeMicrosoft.SQLServer.2008.DBBackupStatus
RemotableTrue
AccessibilityPublic
Alert Message
Database backup should be performed
Database '{2}' in SQL Server instance '{1}' on computer '{0}' has not been backed up for {3} days.
RunAsDefault

Source Code:

<UnitMonitor ID="Microsoft.SQLServer.2008.Database.DBBackupStatusMonitor" Accessibility="Public" Enabled="false" Target="SQL2008Core!Microsoft.SQLServer.2008.Database" ParentMonitorID="SystemHealth!System.Health.AvailabilityState" Remotable="true" Priority="Normal" TypeID="Microsoft.SQLServer.2008.DBBackupStatus" ConfirmDelivery="false">

  <Category>AvailabilityHealth</Category>

  <AlertSettings AlertMessage="Microsoft.SQLServer.2008.Database.DBBackupStatusMonitor.AlertMessage">

    <AlertOnState>Error</AlertOnState>

    <AutoResolve>true</AutoResolve>

    <AlertPriority>Normal</AlertPriority>

    <AlertSeverity>Error</AlertSeverity>

    <AlertParameters>

      <AlertParameter1>$Target/Host/Host/Property[Type="Windows!Microsoft.Windows.Computer"]/NetworkName$</AlertParameter1>

      <AlertParameter2>$Target/Host/Property[Type="SQL!Microsoft.SQLServer.ServerRole"]/InstanceName$</AlertParameter2>

      <AlertParameter3>$Target/Property[Type="SQL!Microsoft.SQLServer.Database"]/DatabaseName$</AlertParameter3>

      <AlertParameter4>$Data/Context/Property[@Name='$Target/Property[Type="SQL!Microsoft.SQLServer.Database"]/DatabaseName$']$</AlertParameter4>

    </AlertParameters>

  </AlertSettings>

  <OperationalStates>

    <OperationalState ID="Good" MonitorTypeStateID="Good" HealthState="Success"/>

    <OperationalState ID="Bad" MonitorTypeStateID="Bad" HealthState="Error"/>

  </OperationalStates>

  <Configuration>

    <ConnectionString>$Target/Host/Property[Type="SQL!Microsoft.SQLServer.DBEngine"]/ConnectionString$</ConnectionString>

    <DatabaseName>$Target/Property[Type="SQL!Microsoft.SQLServer.Database"]/DatabaseName$</DatabaseName>

    <Threshold>7</Threshold>

    <IntervalSeconds>86400</IntervalSeconds>

    <SyncTime/>

    <TimeoutSeconds>300</TimeoutSeconds>

  </Configuration>

</UnitMonitor>