Now VB Scripting Help!

Thanks to all of you for supporting this blog I am now expanding to include detailed VB Scripting help for all database developers and database adminstrators that need to do file administration and manipulation.

The other day I needed to implement some proactive file maintenance on my production systems that contained full database backups.  The requirements for file maintenance were backup files that are older than 2 days and had an extension of ‘.bak’.  After developing, testing and implementing the script it now runs proactively in my production envirnoment on a daily basis and without issue.  Here is the script that I wrote.

Option Explicit
on error resume next
     Dim oFSO
     Dim sDirectoryPath
     Dim oFolder
     Dim oFileCollection
     Dim oFile
     Dim iDaysOld

‘Customize values here to fit your needs
     iDaysOld = 2
     Set oFSO = CreateObject(“Scripting.FileSystemObject”)
     sDirectoryPath = “C:\test”
     set oFolder = oFSO.GetFolder(sDirectoryPath)
     set oFileCollection = oFolder.Files

‘Walk through each file in this folder collection.
‘If it is older than (2) days, then delete it.
     For each oFile in oFileCollection
           If oFile.DateLastModified < (Date() - iDaysOld) And (Right oFile.Name,3)) = “bak” Then
              oFile.Delete(True)
           End If
     Next

‘Clean up
     Set oFSO = Nothing
     Set oFolder = Nothing
     Set oFileCollection = Nothing
     Set oFile = Nothing

I hope this script helps and modify it for your own use!… If you have any suggestions to make it better post here and let us know!

How To Parse A Text File With VBScript!

I thought today we would switch gears from SQL Server and T-SQL and do something a little more exciting. Today I am going to introduce you to VBScript and what it can do for a DBA. Now some of you may already know VBScript and some of you may not which is ok. I have found VBScript useful in splitting text files, creating text files, interacting with files on the OS level, etc. Today’s example will be splitting a text file into 2 separate files.

You can write VBScript in any type of text editor and save the file with a .vbs extension. I have downloaded VbsEdit from here. The interface is straight forward and it is easy to use. Below is the script that parses one main work file:

‘ ****************************************
‘ **
‘ ** WORK FILE SPLITTER
‘ ** Brett Davis
‘ ** 2009.06.27
‘ **
‘ ****************************************

‘ Declare variables.
Dim fileDate
Dim objFSO
Dim objFile
Dim objFileA
Dim objFileD
Dim strDate
Dim strLine
Dim strCharacter

Set objFSO = CreateObject(“Scripting.FileSystemObject”)

‘ Get Work File.
Set objFile = objFSO.OpenTextFile(“C:\WORK\WORKFILE.WRK”, 1)

‘ Verify if first work file WORKFILEA.txt exists.
If objFSO.FileExists(“C:\WORK\WORKFILEA.txt”) Then
Set objFileA = objFSO.GetFile(“C:\WORK\WORKFILEA.txt”)
fileDate = objFileA.DateLastModified

strDate = DatePart(“yyyy”, fileDate) & Right(“0″ & _
DatePart(“m”, fileDate), 2) & Right(“0″ & DatePart(“d”, fileDate), 2)

‘ Append date to file if it already exists.
 “WORKFILEA_” & strDate & “.txt”

Set objFileA = objFSO.CreateTextFile(“C:\WORK\WORKFILEA.txt”,1)
Else
Set objFileA = objFSO.CreateTextFile(“C:\WORK\WORKFILEA.txt”,1)
End If

‘Verify if second work file WORKFILED.txt exists.
If objFSO.FileExists(“C:\WORK\WORKFILED.txt”) Then
Set objFileD = objFSO.GetFile(“C:\WORK\WORKFILED.txt”)
fileDate = objFileD.DateLastModified

strDate = DatePart(“yyyy”, fileDate) & Right(“0″ & _
DatePart(“m”, fileDate), 2) & Right(“0″ & DatePart(“d”, fileDate), 2)

‘ Append date to file if it already exists.
 “WORKFILED_” & strDate & “.txt”

Set objFileD = objFSO.CreateTextFile(“C:\WORK\WORKFILED.txt”,1)
Else
Set objFileD = objFSO.CreateTextFile(“C:\WORK\WORKFILED.txt”,1)
End If

‘ Start iterating through the main file and begin split. Look for the first character to be either an ‘A’ or a ‘D’.
Do Until objFile.AtEndOfStream
strCharacter = objFile.Read(1)

If strCharacter = “A” Then
strLine = strCharacter + objFile.ReadLine
‘WScript.Echo strLine
objFileA.WriteLine strLine
End If

If strCharacter = “D” Then
strLine = strCharacter + objFile.ReadLine
‘WScript.Echo strLine
objFileD.WriteLine strLine
End If
Loop

‘ Destroy objects.
objFile.Close
objFileA.Close
objFileD.Close

MsgBox “Files have been processed.”

I hope this helps and please feel to contribute to this script and let us know how you are using
this script!