Enterprise wide Backup Audit
This script written in vbscript generates a csv file with backup report (full/diff/tran log) for all databases on all servers in the company(server list to be provided in a text file)
' Author - Pradeep Singh
' email - pradeep_singh37@yahoo.com
'Prerequisites:
'1. c:\Workfolder - this folder should be created manually
'2. a file named servers.txt in that folder containing list of servers
'3. osql should be present on the system
'4. tested on 2000 and 2005 servers
'5. a csv file will be created in c:\workfolder with backup details
<HTML>
<HEAD>
<title>Backup Audit Report</title>
<body >
<script language="vbscript">
<!--
'msgbox "Please create a folder c:\WorkFolder and put the list of servers in a file with the name servers.txt and then hit OK to continue!!"
dim servers, filesys ,objFile
set objFSO=CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile("C:\WorkFolder\Servers.txt", 1)
Do Until objFile.AtEndOfStream
Redim Preserve arrFileLines(i)
arrFileLines(i) = objFile.ReadLine
'document.write (arrFileLines(i))
i = i + 1
Loop
objFile.Close
For l = LBound(arrFileLines) to UBound(arrFileLines) Step 1
'Document.write arrfilelines(l)
' 1. check for servers connectivity
Set WshShell = CreateObject("WScript.Shell")
ostr = "osql -Q " & """select name from sysdatabases where dbid=1 """ & " -E -S" & arrfilelines(l) & " -o " & "c:\Workfolder\output_" & Replace(arrfilelines(l), "\", "-") & ".txt "
WshShell.Run ostr
Next
For i = 1 To 1000000
For j = 1 To 100
next
Next
' Create a file for output here!!
Dim Bkp
Set Bkp=objfso.createTextfile ("C:\WorkFolder\BackupReport.csv",True)
str= "Instance Name,Database Name,Size,Owner,Status,UserAccess,Recovery Model,Full Backup Path,Last Full Backup Start, Last Full Backup End,Delay in days, Full backup duration,2nd Last Full Backup Start ,2nd Last Full Backup End, Diff Backup Path,Last Diff Backup start,Last Diff Backup End,Delay in Hours, Diff backup duration,2nd Last Diff Backup Start,2nd Last Diff Backup End, Tran Log Backup Path,Last Tran log Backup Start,Last Tran log Backup End,Hours since last Tran Log Backup, Tran Log Backup Duration,2nd Last Tran log Backup Start,2nd Last Tran log Backup End, "
bkp.writeline str
Dim objConnection,found1,x
Set objConnection = CreateObject("ADODB.Connection")
For l = LBound(arrFileLines) to uBound(arrFileLines) Step 1
' read the files one by one
' open only if it has master in it.
Dim objServers, fln
fln="c:\Workfolder\output_" & Replace(arrfilelines(l), "\", "-") & ".txt "
'Document.write fln
Set objservers=objFSO.OpenTextFile(fln, 1)
found1="a"
Do Until objservers.AtEndOfStream
x= objservers.ReadLine
If InStr(1, x, "master")>1 Then found1="b"
Loop
If found1="b" then
If objConnection.state=1 Then objConnection.close
objConnection.ConnectionString = "Provider=SQLOLEDB; Data Source=" & arrfilelines(l) & ";Initial Catalog=master;Integrated Security=SSPI;"
objConnection.ConnectionTimeout = 0
objConnection.Open
objConnection.Execute "create table #DB_Info (name varchar(255),db_size varchar(20), Owner varchar(255),db_id varchar(4),created datetime,Status varchar(500),compatibility varchar(10))"
objconnection.execute "insert into #DB_Info exec sp_helpdb"
Dim rs, rs2, sql, dt
Set rs=CreateObject("ADODB.Recordset")
Set rs2=CreateObject("ADODB.Recordset")
sql="select * from #DB_Info"
rs.open sql, objconnection, 3,3
'Document.write "<BR>"
While Not rs.eof
'Document.write rs.fields(1)
str=arrfilelines(l)+ ","
str=str+Trim(rs(0))+ ","
str=str+Trim(rs(1))+ ","
str=str+Trim(rs(2))+ ","
str=str+Mid(rs(5), InStr(1, rs(5), "Status=") + 7, (InStr(1, rs(5), "Update")) - (InStr(1, rs(5), "Status")) - 9)+ ","
str=str+ Mid(rs(5), InStr(1, rs(5), "UserAccess=") + 11, (InStr(1, rs(5), "UserAccess")) - (InStr(1, rs(5), "Updateability")) - 16)+ ","
str=str+ Mid(rs(5), InStr(1, rs(5), "Recovery=") + 9, (InStr(1, rs(5), "Version")) - (InStr(1, rs(5), "Recovery")) - 10)
sql="select top 2 physical_device_name, backup_start_date, backup_finish_date, cast(datediff(day, backup_start_date, getdate())as varchar(20)) + ' Days' Delay,cast(datediff(hour, backup_start_date, backup_finish_date) as varchar(20))+':'+cast(datediff(minute, backup_start_date, backup_finish_date) as varchar(20))+':'+cast(datediff(second, backup_start_date, backup_finish_date) as varchar(20)) Duration,type from msdb..backupmediafamily,msdb..backupset where msdb..backupmediafamily.media_set_id=msdb..backupset.media_set_id and type='D' and database_name='" & rs(0) & "' order by backup_set_id desc"
'sql = "select top 2 backup_finish_date from msdb..backupset where database_name='" & rs(0) & "' and type='D' order by backup_set_id desc"
'Document.write sql
'rs.close
If rs2.state=1 Then rs2.close
rs2.open sql, objconnection, 3,3
If rs2.RecordCount > 0 Then dt = CStr(rs2(0))+","+CStr(rs2(1))+","+CStr(rs2(2))+","+CStr(rs2(3))+","+CStr(rs2(4)) Else dt = "NULL,NULL,NULL,NULL,NULL"
str=str+ dt+ ","
If rs2.RecordCount > 1 Then rs2.movenext:dt = CStr(rs2(1))+","+CStr(rs2(2)) Else dt = "NULL,NULL"
str=str+ dt+ ", "
sql="select top 2 physical_device_name, backup_start_date, backup_finish_date, cast(datediff(hour, backup_start_date, getdate())as varchar(20)) + ' Hours' Delay,cast(datediff(hour, backup_start_date, backup_finish_date) as varchar(20))+':'+cast(datediff(minute, backup_start_date, backup_finish_date) as varchar(20))+':'+cast(datediff(second, backup_start_date, backup_finish_date) as varchar(20)) Duration,type from msdb..backupmediafamily,msdb..backupset where msdb..backupmediafamily.media_set_id=msdb..backupset.media_set_id and type='I' and database_name='" & rs(0) & "' order by backup_set_id desc"
'sql = "select top 2 backup_finish_date from msdb..backupset where database_name='" & rs(0) & "' and type='I' order by backup_set_id desc"
'Document.write sql
'rs.close
If rs2.state=1 Then rs2.close
rs2.open sql, objconnection, 3,3
If rs2.RecordCount > 0 Then dt = CStr(rs2(0))+","+CStr(rs2(1))+","+CStr(rs2(2))+","+CStr(rs2(3))+","+CStr(rs2(4)) Else dt = "NULL,NULL,NULL,NULL,NULL"
str=str+ dt+ ","
If rs2.RecordCount > 1 Then rs2.movenext:dt = CStr(rs2(1))+","+CStr(rs2(2)) Else dt = "NULL,NULL"
str=str+ dt+ ", "
'If rs2.RecordCount > 0 Then dt = CStr(rs2(0)) Else dt = "NULL"
'str=str+ dt+ ","
'
' If rs2.RecordCount > 1 Then rs2.movenext:dt = CStr(rs2(0)) Else dt = "NULL"
' str=str+ dt+ ", "
sql="select top 2 physical_device_name, backup_start_date, backup_finish_date, cast(datediff(hour, backup_start_date, getdate())as varchar(20)) + ' Hours' Delay,cast(datediff(hour, backup_start_date, backup_finish_date) as varchar(20))+':'+cast(datediff(minute, backup_start_date, backup_finish_date) as varchar(20))+':'+cast(datediff(second, backup_start_date, backup_finish_date) as varchar(20)) Duration,type from msdb..backupmediafamily,msdb..backupset where msdb..backupmediafamily.media_set_id=msdb..backupset.media_set_id and type='L' and database_name='" & rs(0) & "' order by backup_set_id desc"
'sql = "select top 2 backup_finish_date from msdb..backupset where database_name='" & rs(0) & "' and type='L' order by backup_set_id desc"
'Document.write sql
'rs.close
If rs2.state=1 Then rs2.close
rs2.open sql, objconnection, 3,3
If rs2.RecordCount > 0 Then dt = CStr(rs2(0))+","+CStr(rs2(1))+","+CStr(rs2(2))+","+CStr(rs2(3))+","+CStr(rs2(4)) Else dt = "NULL,NULL,NULL,NULL,NULL"
str=str+ dt+ ","
If rs2.RecordCount > 1 Then rs2.movenext:dt = CStr(rs2(1))+","+CStr(rs2(2)) Else dt = "NULL,NULL"
str=str+ dt+ ", "
'If rs2.RecordCount > 0 Then dt = CStr(rs2(0)) Else dt = "NULL"
'str=str+ dt+ ","
' If rs2.RecordCount > 1 Then rs2.movenext:dt = CStr(rs2(0)) Else dt = "NULL"
' str=str+ dt+ ", "
document.write str
bkp.WriteLine str
document.write "<br>"
'WshShell.Run ostr 'run batch file, Use your absolute path here
rs.movenext
Wend
End if
Next
-->
Function ConnectToServer()
End Function
</script>
</HEAD>
</HTML> 
