VB script to check latest backup

'==========================================================================
'
' 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 JOINmsdb..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') " &_
"WHEREs.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