Hey, Scripting Guy! Question

Hey, Scripting Guy! Each day we have a program that creates a file with a name similar to this: backup_20050607.bak. How can I delete the previous day’s file?

-- JC

SpacerHey, Scripting Guy! AnswerScript Center

Hey, JC. Hmmm, a script that goes out and automatically deletes whatever happened the day before. Boy, could we have used a script like that a few weeks ago!

Yes, we know. But that’s a story we probably shouldn’t tell.

Besides, we have a column to write here. You want a script that can delete a file that has a name like backup_20050607.bak, with the 20050607 representing the previous day (in this case, June 7, 2005)? All you had to do was ask:

dtmYesterday = Date - 1

strYear = Year(dtmYesterday)

strMonth = Month(dtmYesterday)
If Len(strMonth) = 1 Then
    strMonth = "0" & strMonth
End If

strDay = Day(dtmYesterday)
If Len(strDay) = 1 Then
    strDay = "0" & strDay
End If

strYesterday = strYear & strMonth & strDay

strFileName = "C:\Backups\backup_" & strYesterday & ".bak"

Set objFSO = CreateObject("Scripting.FileSystemObject")
objFSO.DeleteFile(strFileName)

As you probably guessed, the tricky part here is constructing the file name; once we have that, deleting the file is a piece of cake. So then how do we construct that file name?

Well, the one part of the file name that varies from day-to-day is the part that represents the date the backup file was generated. That means all we have to do is construct that portion of the file name; the rest can then be hard-coded in. Therefore, we start off by determining the date for the previous day; we do that by subtracting one day from the current date and then storing that value in a variable named dtmYesterday:

dtmYesterday = Date - 1

Next we use the Year function to grab the four-digit year value (e.g., 2005) from dtmYesterday; that value gets stored in the variable strYear. Following that, we use the Month function to grab the month value from dtmYesterday.

Note, however, that we have to do a little extra coding when it comes to the month (and to the day, too). Why? Well, suppose we’re dealing with June. In that case, the Month function returns the value 6. That’s fine, except in our file name two digits are allocated for the month; the month needs to be listed as 06. Therefore, we have to determine whether we have a one-digit month or a two-digit month. If it’s one-digit month, we then have to place a leading zero in front of the number. That’s what we do here:

If Len(strMonth) = 1 Then
    strMonth = "0" & strMonth
End If

This is actually pretty straightforward code. The Len (length) function tells us how many digits are in the variable strMonth. If it’s just 1, then we add a leading 0:

strMonth = "0" & strMonth

If the length is equal to anything other than 1, then we leave well enough alone.

After grabbing the month value we repeat this exact same process with the Day function, enabling us to retrieve the day portion of the date. That gets stashed in a variable cleverly-named strDay.

And that gives us all the pieces we need to construct the file name. To do so, we first jam together the year, month, and day (giving us a string like 20050607) using this line of code:

strYesterday = strYear & strMonth & strDay

Then we simply tack on the rest of the path information (in this example, we’re assuming that the file is stored in the folder C:\Backups):

strFileName = "C:\Backups\backup_" & strYesterday & ".bak"

As you can see, we just combine the strings C:\Backups\backup_, the date we constructed (20050607), and .bak. Put them all together and you’ll end up with something like this:

C:\Backups\backup_20050607.bak

Cool.

We then use these two lines of code to create an instance of the FileSystemObject and delete the file:

Set objFSO = CreateObject("Scripting.FileSystemObject")
objFSO.DeleteFile(strFileName)

Because we used the FileSystemObject, this particular script will work only on the local machine. But what if the backup file is located on a remote computer? No problem; in that case we can use WMI to locate and remove the file. So then why didn’t we use WMI in the first place? Well, the FileSystemObject can locate and delete the file in less than a second; WMI will take a little longer. We decided to go with speed and efficiency.

But, like we said, that speed and efficiency won’t do you much good if the file is on a remote computer. Fortunately, in a situation like that you can use a WMI script to delete the file. This script (which we won’t discuss today) deletes the backup file from a remote computer named atl-fs-01:

dtmYesterday = Date - 1

strYear = Year(dtmYesterday)

strMonth = Month(dtmYesterday)
If Len(strMonth) = 1 Then
    strMonth = "0" & strMonth
End If

strDay = Day(dtmYesterday)
If Len(strDay) = 1 Then
    strDay = "0" & strDay
End If

strYesterday = strYear & strMonth & strDay

strFileName = "C:\\Backups\\backup_" & strYesterday & ".bak"

strComputer = "."
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")

Set colFiles = objWMIService.ExecQuery _
    ("Select * from CIM_DataFile where Name = '" & strFileName & "'")

For Each objFile in colFiles
    objFile.Delete
Next

Just like that, yesterday’s gone, and we can all pretend that it never happened. Right, boss?

Note. Couldn’t we have saved ourselves all this trouble and simply deleted all the files with a creation/modification date equivalent to the previous day’s date? Yes, provided that there would never be any other files stored in that folder that would have the same date and might inadvertently be deleted. All things considered, this seemed like the safest approach.