Monitor recovery mode

This script will check recovery mode of a MS SQL database. Its pretty simple to modify to check other databases or all databases. This example will check if the Onepoint database is running with SIMPLE recovery mode. If it does, a local event will be generated. You can then create a event rule to collect that event and generate an alert in MOM.

set cn = Nothing
set rs = nothing
strSQLServer = "localhost"
Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
cn.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=master;Data Source=" & strSQLServer & ""
strSQLQuery = "select name, databasepropertyex(name, 'recovery') as RECOVERY from sysdatabases WHERE NAME = 'OnePoint'"

Set rs = cn.execute(strSQLQuery)
strDBName = rs("Name")
strMode = rs("Recovery")

If strMode = "SIMPLE" then
 Const EVENT_WARNING = 2
 Set objShell = CreateObject("Wscript.Shell")
 objShell.LogEvent 2, "The " & strDBName & " database is running in simple recovery mode"
End If

  1. Create a new script and paste this source
  2. Create a rule that run the script every X minute
  3. Create a rule that collects and generate alerts on event ID 2 from WSH in the application log.

About

Microsoft