VB script to check latest backup

,

This script checks the latest backup of all databases. Then it write a logfile with this timestamp. all backups older than one day will be marked as ERROR.

'==========================================================================
'
' VBScript Source File -- Created with SAPIEN Technologies PrimalScript 3.1
'
' NAME: JZ
'
' AUTHOR: Stadtverwaltung St.Gallen , Stadtverwaltung St.Gallen
' DATE  : 13.06.2005
'
' COMMENT: This script check all databases on a specific SQL Server On
'			time of last full backup. If the backup is older then one
'			day, it will report an error in the logfile.
'			It ist possible to exclude some unused databes from the check.
'			As result, the script generate a logfile with all checked
'			databeses an their last backup timestamp.
'			
' Start:	MSSQLBackup.vbs Server Logfile ExcludedDBs
'				Server: Instancename of the SQL Server to check
'				Logfile: Path an name of the logfile, wich will be generated
'				ExcludedDBs: Names of databases, wich sould not be checked. Each
'					separatet by a space
'
' Exitcode: 0  => All checkd databases are backuped within one day
'			1  => At least on backup is to old
'			2  => Wrong parameters for the script.
'
'
' History:	
'	V1.0	: 13.06.2005 JZ
'		Erste Version
'
'==========================================================================
Option Explicit

'temporary variables to use
Dim sTemp1
Dim i, sTemp2

'read start arguments
' 1 => servername
' 2 => path for logfile
' . => excluded databases
Dim objArgs
Set objArgs = WScript.Arguments
If objArgs.Count < 2 Then
	'Not enough parameters
	WScript.Quit 2		
end If

'instance name of checked SQL Server
Dim sServer
sServer = objArgs(0)	

'Filename and path of the resulting logfile
Dim sLogPath
sLogPath = objArgs(1) 

'all excluded databases
Dim asExcludeDB
If objArgs.Count > 2 then
	ReDim asExcludeDB(objArgs.Count-2)
	For i=2 To objArgs.Count-1
		asExcludeDB(i-2) = ucase(objArgs(i))
	Next
End If

Dim objCon			'object to connect SQL Server
Set objCon = WScript.CreateObject("ADODB.Connection") 		


Dim sConnStr		'connection string for SQL Server
sConnStr = "Provider=sqloledb;Data Source=" & sServer & ";Initial Catalog=MSDB;User Id=sa;Password=fill in password;"

'open connection
objCon.Open sConnStr

'setup SQL Query to list last backup of all databases in the SQL Server
Dim sSQL 			
sSQL = "select s.name as Name, b.backup_start_date as BackupStarted, b.backup_finish_date as BackupFinished " &_
		"from 	master..sysdatabases s " &_
		"Left OUTER JOIN	msdb..backupset b ON s.name = b.database_name " &_
		"AND b.backup_start_date = (SELECT MAX(backup_start_date) " &_
		"FROM msdb..backupset WHERE database_name = b.database_name AND type = 'D') " &_
		"WHERE	s.name <> 'tempdb' " &_
		"ORDER BY 	b.backup_start_date, s.name"
		

'execute query
Dim oRecordSet		
Set oRecordSet = objCon.Execute(sSQL)

'create object for file access
Dim oFso
Set oFso = WScript.CreateObject("Scripting.FileSystemObject") 

'create result logfile
Dim sLogFileName 
sLogFileName = sLogPath & "\MSSQLBackupLog.log"
Dim oLogFile
Set oLogFile = oFso.OpenTextFile( sLogFileName, 2, True )

'title of logfile
sTemp1 = "Logfile des SQL-Backups von " & sServer & " vom " & Now
oLogFile.WriteLine sTemp1
sTemp2 = ""
For i=0 To Len(sTemp1)
	sTemp2 = sTemp2 & "-" 
Next
oLogFile.WriteLine sTemp2
oLogFile.WriteLine ""

'print excluded databases
If IsArray(asExcludeDB) then
	sTemp1 = "Ausgeschlossene Datenbanken: " 
	For i=0 To UBound(asExcludeDB)-1
		sTemp1 = sTemp1 & asExcludeDB(i) & ","
	Next
	sTemp1 = Left( sTemp1, Len(sTemp1)-1 )
	oLogFile.WriteLine sTemp1
	oLogFile.WriteLine ""
End If

'variable to store errors found
Dim bError
bError = False

'write the result of the query in the logfile
Dim bExcluded
Do Until oRecordSet.EOF
	bExcluded = false
	sTemp1 = ucase(oRecordSet.Fields("Name"))
	If IsArray(asExcludeDB) Then
		For i=0 To UBound(asExcludeDB)-1
			If asExcludeDB(i) = sTemp1 Then
				bExcluded = True
				Exit For
			End if
		next
	End if
	If (not bExcluded) Then
		sTemp1 = sTemp1 & ": "
		For i=Len(sTemp1) To 30
			sTemp1 = sTemp1 & " "
		Next
		oLogFile.Write sTemp1
		if IsNull(oRecordSet.Fields("BackupStarted")) Then
			oLogFile.WriteLine "Error: no backup!"
			bError = true
		Else
			Dim dBackStartDate, dBackEndDate
			dBackStartDate = CDate(oRecordSet.Fields("BackupStarted"))
			dBackEndDate = CDate(oRecordSet.Fields("BackupFinished"))
			Dim dTestDate
			dTestDate = DateAdd( "d", -1, Now)
			If (dBackStartDate < dTestDate) Then
				oLogFile.WriteLine "Error: backup to old (" & oRecordSet.Fields("BackupStarted") & ")"
				bError = true
			Else
				oLogFile.WriteLine "last backup: " & dBackStartDate  & " bis " & dBackEndDate
			End if
		End If
	End if
	oRecordSet.MoveNext
Loop
oLogFile.WriteLine ""
oLogFile.WriteLine "Ende Logfile"

'close all existing objects
oLogFile.Close
Set oFso = Nothing
Set oRecordSet = Nothing
objCon.Close
Set objCon = Nothing

If bError Then
	WScript.Quit 1
Else
	WScript.Quit 0
End if

Rate

Share

Share

Rate