Hey, Scripting Guy! Question

Hey, Scripting Guy! How can I delete all the .BAK files in a folder that are more than 7 days old?

-- KT

SpacerHey, Scripting Guy! AnswerScript Center

Hey, KT. You know, on his way to work this morning the Scripting Guy who writes this column heard an advertisement for a local seafood restaurant. Now, this Scripting Guy doesn’t like seafood, so he normally wouldn’t pay any attention to an ad for a restaurant like this. However, his interest was piqued when the ad pointed out that this was the only restaurant in the Seattle area that served crab ice cream. As you might expect, the Scripting Guy who writes this column was shocked: Seattle considers itself a world-class city, yet it only has one restaurant that serves crab ice cream?!? That’s an outrage, pure and simple.

And we agree with you: someone should be fired over that. Those of you who live in other cities have no idea how good you have it. After all, any time you get a craving for crab ice cream you can probably just walk outside your front door and see half-a-dozen places where you can satisfy that craving. Sadly, that’s not true for those of us stuck in the Seattle area.

Note. If you’ve ever eaten crab ice cream, drop us a line and let us know; we’d love to hear about it. We’d also be interested in knowing how big the guy was who forced you to eat the stuff.

Admittedly we’re still a little shaken by all this, but we’ll try to answer your question anyway, KT. Not that this doesn’t represent something of an outrage itself: after all, deleting a specific type of file based on the file creation date is harder than it really should be. OK, we should qualify that a bit: doing this on the local computer isn’t too bad. But in this day and age, who wants to do everything on the local computer? If you want to perform this kind of task remotely – and we know that many of you do – things can get a little tricky. But that’s all right. Here’s a reasonably straightforward way to delete all the .BAK files in a folder that are more than 7 days old, and on either the local machine or a remote machine to boot:

dtmDate = Date - 7

strDay = Day(dtmDate)

If Len(strDay) < 2 Then
    strDay = "0" & strDay
End If

strMonth = Month(dtmDate)

If Len(strMonth) < 2 Then
    strMonth = "0" & strMonth
End If

strYear = Year(dtmDate)

strTargetDate = strYear & strMonth & strDay

strComputer = "."

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

Set FileList = objWMIService.ExecQuery _
    ("ASSOCIATORS OF {Win32_Directory.Name='C:\Scripts'} Where " _
        & "ResultClass = CIM_DataFile")

For Each objFile In FileList
    strDate = Left(objFile.CreationDate, 8)
    If strDate < strTargetDate Then
        If objFile.Extension = "bak" Then
            objFile.Delete
        End If
    End If
Next

And don’t worry; as soon as we finish this dish of crab ice cream we’ll explain how this all works.

OK. The first thing we need to do is calculate our target date: in this case, that’s the current date minus 7 days. That’s what we do in this first line of code:

dtmDate = Date - 7

Because we’re going to use WMI we next need to convert dtmDate to a UTC-like date time value. We won’t discuss the UTC date-time format today; for more information see the Microsoft Windows 2000 Scripting Guide. For now all we need to know is that we must convert a date like 11/16/2006 to this, with the first four digits representing the year, the next two digits representing the month, and the last two digits representing the day:

20061116

It’s pretty easy to extract the individual pieces of a date-time value; we can do that using the Day, Month, and Year functions. The one tricky part occurs when we have a date like 6/5/2006. In that case both the month (6) and the day (5) have only one digit. Why is that a problem? That’s a problem because, in the UTC format, the month and the day must be two digits longs. To deal with that problem we’ve included code like this for both the day and the month:

strDay = Day(dtmDate)

If Len(strDay) < 2 Then
    strDay = "0" & strDay
End If

What are we doing here? Well, we’re first using the Day function to grab the day portion of the target date and store it in a variable named strDay. We then use the Len function to check the number of characters in strDay. If strDay has less than 2 characters we use this line of code to tack a leading zero onto the value:

strDay = "0" & strDay

In other words, if strDay equals 5, the preceding block of code will add a leading zero, making strDay equal to this:

05

Sometimes we Scripting Guys are so clever it’s scary, isn’t it? (Editor’s Note: Never as scary as crab ice cream though.)

After assuring ourselves that both the day and month have two digits, we then use this line of code to combine the year, month, and day into a UTC-like value:

strTargetDate = strYear & strMonth & strDay

Our next step is to connect to the WMI service on the local computer (although, again, we could just as easily run this script against a remote machine). We then use this crazy-looking query to return a collection of all the files found in the folder C:\Scripts:

Set FileList = objWMIService.ExecQuery _
    ("ASSOCIATORS OF {Win32_Directory.Name='C:\Scripts'} Where " _
        & "ResultClass = CIM_DataFile")

Good point: we aren’t interested in all the files in C:\Scripts, are we? Instead, we’re interested only in .BAK files older than our target date. Don’t worry; we haven’t forgotten that. However, while it’s theoretically possible to write a WMI query that returns just those files, writing a query like that is probably more trouble that it’s worth. (Just like going all the way to the ice cream shop and finding out that all they have is crab ice cream.) We found it easier to instead return all the files, then simply going through the collection one-by-one , deleting those files that meet our criteria.

So how do we do that? Well, to begin with, we set up a For Each loop to loop through all the files in the collection. Inside that loop we use the Left function to grab the first 8 characters of the CreationDate property (a value that will be in UTC format) and store those characters in a variable named strDate:

strDate = Left(objFile.CreationDate, 8)

Why do we do that? Well, let’s say that this particular file was created on 10/30/2006. If that’s the case, that makes strDate equal to 20061030, a UTC-like value if we ever saw one. That also means that we can compare strDate to our target date by using code like this:

If strDate < strTargetDate Then

What if strDate is less than our target date? That means that this file is potentially a candidate for deletion. Why “potentially?” Well, remember, we want to delete only files that have a .BAK file extension. Therefore, we need to make a second check, one that looks at the file extension to see if it is equal to bak (note that, in WMI, you don’t include the period in the file extension):

If objFile.Extension = "bak" Then

If we do have a .BAK file older than the target date we then use this line of code to delete the file:

objFile.Delete

Otherwise we simply loop around and repeat the process with the next file in the collection. When we’re all done each and every .BAK file in the C:\Scripts folder (or at least those .BAK files older than our target date) will be gone.

Interestingly enough, in the course of researching this column we not only figured out how to delete old .BAK files, but we also discovered a way to enjoy crab ice cream. Here’s the secret: get a big glob of chocolate syrup. (Hot fudge is best, but any chocolate syrup will do.) Next, get a can of whipped cream and some chopped nuts (we’d recommend walnuts). Pour the syrup into a bowl, then top with the nuts and whipped cream. Finally, throw the crab ice cream away and just eat the syrup, nuts, and whipped cream. Delicious!