'********************************************************************** 
'** 
'** Last SQL database backup check
'** Author: Pete Zerger, AKOS Technology Services
'** Date: 12/11/2006
'** Notes: This script runs one a day to identify SQL databases that 
'**        have not been backed up in the last X days determined
'**        determined by the user-defined value in the MaxDays attribute.
'**
'** Revision History: ver 1.0
'** 

' PARAMTERS: 
' 	MaxDays - integer defining max number of days a SQL database should
'             go without being backed up (a FULL backup) in your environment. I would suggest 5 
'             as a suitable default if you are unsure.
'
'   LogSuccessEvent - boolean 1-logs event for each successful run 0-disables success
'                     logging. WARNING - this will log 1 event for each SQL database on 
'                     every SQL server it runs against!
'********************************************************************** 

'on Error Resume Next 

Const MOM_SCRIPT_EVENT_ID = 5000 
Const POLL_INTERVAL_IN_HOURS = -1 '***Note: This must be a negative number as it's going back in time!!!  

Const EVENT_TYPE_SUCCESS = 0 
Const EVENT_TYPE_ERROR = 1 
Const EVENT_TYPE_WARNING = 2 
Const EVENTLOG_INFORMATION_TYPE = 4 
Const EVENTLOG_AUDIT_SUCCESS = 8 
Const EVENTLOG_AUDIT_FAILURE = 16 
Const SCRIPT_FAILURE_EVENT = 91001 


Dim cn, rs, strSQLQuery, strSQLServer, intMaxDays


strSQLServer = "localhost"
intLogSuccessEvent = ScriptContext.Parameters.Get("LogSuccessEvent")
intMaxDays = ScriptContext.Parameters.Get("MaxDays")

InitSQL() 
getBackupDates() 

if err.number <> 0 Then 
CreateAlert 
end If 

set cn = Nothing 
set rs = nothing 


'*********************************************** 
'InitSQL() Create connection 
'*********************************************** 

Sub InitSQL() 
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 & ""
End Sub 


'***********************************************************
'Get last backup date for each database on the target server
'***********************************************************

sub getBackupDates() 
strSQLQuery = "select max(isnull(datediff(dd,b.backup_start_date,getdate()),0)) as 'NumDays', d.name as 'DBName'" & _
"from  master..sysdatabases d with (nolock)left join msdb..backupset b  with (nolock)"  & _
"on d.name = b.database_name and b.backup_start_date = (select max(backup_start_date)"  & _
"from msdb..backupset b2 where b.database_name = b2.database_name and b2.type = 'D')" & _
"where d.name != 'tempdb'group by d.name, b.type, b.backup_size"
' wscript.echo strSQLQuery "

Set rs = cn.execute(strSQLQuery) 

If RS.EOF Then 
	CreateAlert  30,"SQL Last Database Backup", "Query returned a null result set or terminated prematurely."
	Exit Sub 
end if 

While not rs.eof 
	strDBName = rs("DBName") 
	strDays = rs("NumDays") 


	If strDays > Int(intMaxDays) Then 
		CreateAlert  30,"SQL Last Database Backup", "last full backup on " & strDBName & " has not occurred for " & strDays & " days."
	Else

			If intLogSuccessEvent = 1 Then
				CreateEvent 59102, EVENT_TYPE_INFORMATION, "", "last full backup on " & strDBName & " has not occurred for " & strDays & " days."
			End If
	End If 

	rs.MoveNext 
Wend 

end sub 


'******************
'Subs and Functions 
'******************


'******************************************************************************
' Name:		CreatePerfData
' 
' Purpose:	Creates a performance object to display response time in Performance View
'******************************************************************************

Sub CreatePerfData(strObjectName,strCounterName,strInstanceName,numValue)
	Set objPerfData = ScriptContext.CreatePerfData
	objPerfData.ObjectName = strObjectName
	objPerfData.CounterName =strCounterName
	objPerfData.InstanceName = strInstanceName
	objPerfData.Value = numValue
	ScriptContext.Submit objPerfData
End Sub


'******************************************************************************
' Name:		CreateEvent
' 
' Purpose:	Logs an event
'******************************************************************************

 Sub CreateEvent(intEventNumber,intEventType,strEventSource,strEventMessage)
 	Set objEvent = ScriptContext.CreateEvent()
 	objEvent.EventNumber = intEventNumber
 	objEvent.EventType = intEventType 
 	objEvent.EventSource = strEventSource
 	objEvent.Message = strEventMessage
 	ScriptContext.Submit objEvent
 End Sub
 
 
'******************************************************************************
' Name:		CreateAlert
' 
' Purpose:	Raises an alert
'
' Parameters:
'	iSeverity - The severity of the alert.
'	strName - The name of the alert.
'	strDescription - The alert description.
'******************************************************************************

Function CreateAlert(iSeverity, strName, strDescription)
	Dim oAlert
	Set oAlert = ScriptContext.CreateAlert()
	oAlert.Name = strName
	'oAlert.AlertSource = strSource
	oAlert.Description = strDescription
	oAlert.AlertLevel = iSeverity
	ScriptContext.Submit oAlert
End Function