| Downloads |
|---|
| 96094.zip |
Q: To keep track of customer details, I use a Microsoft Access database, which has grown to about 8.9MB. I wrote a batch file that uses Robocopy to run daily backups of the database, which is on the D drive of a domain controller (DC) running Windows Server 2003. The backups are stored on a NAS device.
The batch file uses some code (weekday.com) I found on the Web to return a day of the week. As Listing 1 shows, after the batch file finds the day of the week, it uses a series of If commands to jump to code that executes Robocopy and copies the backup file to a folder named after the day of the week. The batch file then uses another script (AnalyzeRobocopyLog.vbs) I found on the Web to go through the log files generated by Robocopy and return a consolidated version. Finally, the batch file uses the Blat utility to email the backup log file to the appropriate person.
This batch file is working okay, but it's a kludge. I want to go with a different approach. I'd like the new script to rename the backup file so that it includes the current date and do away with the weekday folders. That way, there is only one backup folder. I'd also like the script to monitor the amount of free space in the backup folder and overwrite the oldest file if the size of the backup folder is at the specified size limit. I'm trying to write this new script, but I'm just learning the art of scripting. I'm interested in seeing how a scripting pro would approach writing this script.
—Emile Mercier
A: To make your script less of a kludge, we suggest that you use Windows Script Host (WSH) as your scripting environment instead of Cmd.exe. Through WSH, you can use the FileSystemObject object, which is part of the Microsoft Scripting Runtime Library. This object has many methods and properties that let you easily work with files and folders. In addition, with WSH, you can more easily add code that handles errors and deals with exceptions (such as running the script twice in the same day, which would mean you'd have duplicate filenames). Your batch file calls a .vbs script, so your server must already have WSH installed.
Listing 2 shows an example of how you might create a .vbs script to back up your Access database. The BackupFile.vbs script backs up the specified file to a target folder, renaming the file to include the current date. To keep the target folder's size in check, the script monitors the folder size and deletes the oldest files until the folder size is less than the specified threshold or until there is only one file left in the folder.
Let's take a detailed look at BackupFile.vbs, which Listing 2 shows, so that you can learn how it works. The script begins with the Option Explicit statement, which means you must explicitly declare all the variables in a script, which the script does with Dim statements. Using a variable that isn't declared generates an error. Thus, including Option Explicit in your scripts is a good idea because it can help catch such scripting mistakes as misspelled variable names.
Besides declaring variables, the script declares three constants:
- SOURCE_FILE, which is the file to copy
- TARGET_DIR, which is the path to folder in which to copy file
- MAX_TARGET_DIR_SIZE, which is the maximum size (in megabytes) of the target folder
Before you use BackupFile.vbs, you'll need to customize the values of these constants. You don't need to make any other modifications to the code. However, you must use the CScript host when executing the script.
Next, the script creates an instance of the FileSystemObject object, which it assigns to the declared fso variable. As we mentioned previously, this object has many methods and properties that let you manipulate files and folders. In this instance, the script is using the object's FileExists method to check whether the file specified in the SOURCE_FILE constant exists. If the file doesn't exist, the script displays an error message and quits. If the file exists, the script uses the FileSystemObject object's FolderExists method to check whether the folder specified in the TARGET_DIR constant exists. If the folder doesn't exist, the script displays an error message and quits. If the folder exists, the script continues on. Like using Option Explicit, checking for the existence of specified files and folders is a good practice to follow because it's another way to help error-proof your scripts.
You mentioned that you want the script to rename the backup file so that the name includes the current date. The code at callout A in Listing 2 does just that. This code calls the GetNewFileName function into action. This function generates a new filename from a given filename by appending the current date in the format YYYYMMDD. So, the new filename will be old_filenameYYYYMMDD.ext. Note that the function takes two parameters. The first parameter specifies the filename to change—in this case, SOURCE_FILE. The second parameter is a suffix. Normally, the suffix parameter is set to 0, which means there isn't a suffix in the new filename. However, there's one exception (which we'll explain shortly) in which the new filename will have a suffix that's a non-zero numeric value. When this exception occurs, the filename follows the format old_filenameYYYYMMDD-Suffix.ext.
You'll find the code for the GetNewFileName function in callout D in Listing 2. Let's look at how the function gets the various pieces for the new filename. To get the name of the old file, GetNewFileName uses the FileSystemObject object's GetBaseName method to extract just the filename (not including the extension) from the pathname in the SOURCE_FILE constant.
To get the date to append to the old filename, GetNewFileName uses VBScript's Now, Day, Month, and Year functions. The Now function returns the current date and time. So, by using the Day function with the Now function as the parameter, you'll get the current day. Similarly, you can get the current month (in numerical format) and year by using the Now function as the parameter in the Month and Year functions, respectively. So, if the date is April 25, 2007, the statements
Month(Now)
Day(Now)
Year(Now)
will return 4, 25, 2007, respectively. As we mentioned previously, the date appended to the filename is in the format YYYYMMDD . So, if either the month or day is less than 10, GetNewFileName prefixes that number with a 0 so that a two-digit month and a two-digit day are always used (e.g., uses 04 instead of just 4).
If the suffix parameter is a non-zero value, GetNewFileName obtains that value and prefixes it with a hyphen. If the suffix parameter is the default 0, the filename doesn't include a suffix. Finally, to get the file's extension, the GetNewFileName function uses the FileSystemObject object's GetExtensionName method to extract the extension from the pathname in the SOURCE_FILE constant.
After the GetNewFileName function creates the new filename, the script checks to see whether that filename already exists (e.g., you ran the script earlier in the day). As the code in callout A shows, if that filename exists, the script increments the i variable by 1, then again calls the GetNewFileName function into action. This time, it sets the suffix parameter to the i variable's value, which prompts the GetNewFileName function to create a filename that includes the -1 suffix.
For example, let's say the first run of the script creates the new filename myfile20070425.mdb. If you run the script a second time that day, the script discovers that the myfile20070425.mdb filename already exists, so it creates the new filename of myfile20070425-1.mdb. If you happen to run the script a third time that day, the i variable will increment to 2, so the newest filename will be myfile20070425-2.mdb.
After creating a unique filename, the script uses the FileSystemObject object's CopyFile method to perform the actual backup operation. The CopyFile method takes two mandatory parameters. The first parameter specifies the file to copy (in this case, SOURCE_FILE). The second parameter specifies the destination pathname (in this case, TARGET_DIR\strNewFileName, where strNewFileName is the new filename created by the GetNewFileName function).
The script's next task is check the amount of free space in the backup folder and delete files, if necessary, to get the folder to the desired size. The code at callout B in Listing 2 performs this important task. This code begins by using the FileSystemObject object's GetFolder method to create a Folder object (oFolder). Because information about the folder's files is needed, the code uses the Folder object's Files property to obtain a collection that contains all the files in that folder. One of the properties available to this collection is the Count property, which returns the number of items in that collection. So, the code
oFolder.Files.Count
returns the number of files in the backup folder. If there is more than one file in the folder, the code uses the Folder object's Size property to obtain the combined size of all the files and subfolders in the folder. The number returned is in bytes, so the code divides that number by 1,048,576 (1024 * 1024) to get the size in megabytes.
When the folder's size is greater than the size specified in MAX_TARGET_DIR_SIZE, the code calls the FindOldestFile function into action. This function, which callout C in Listing 2 shows, finds the oldest file in the folder.
The FindOldestFile function begins by declaring and assigning values to the oldestFileName and oldestFileDate variables. The function sets the oldestFileName variable to the string UNKNOWN, whereas it sets the oldestFileDate variable to the current date via the Now function.
Next, the FindOldestFile function uses a For Each…Next statement to iterate through all the files in the backup folder. For each file, the function applies VBScript's DateDiff function. The DateDiff function calculates how much time has elapsed (in terms of seconds, minutes, hours, days, or some other time interval) between two given dates (date1 and date2). If date1 refers to a more recent point in time than date2, DateDiff returns a negative number.
In BackupFile.vbs, the FindOldestFile function uses DateDiff to calculate the number of seconds (specified by the "s" parameter) between a file's Last Modified date (which it obtains with the DateLastModified property) and the date in the oldestFileDate variable. If the difference is greater than zero, the function assign's the file's name to the oldestFileName variable and that file's Last Modified date to the oldestFileDate variable.
For example, suppose that the current date is April 25, 2007. Exactly 24, 48, and 72 hours ago, the myfile20070424.mdb, myfile20070423.mdb, and myfile20070422.mdb files were created, respectively, and stored in the backup folder. In the first loop through the For Each…Next statement, the FindOldestFile function calculates that the difference between myfile20070423.mdb's modification date and the date in the oldestFileDate variable (which, at this point, is the current date returned by the Now function) is 169,200 seconds. This number is greater than zero, so FindOldestFile assigns myfile20070423.mdb to the oldestFileName variable and that file's Last Modified date to the oldestFileDate variable.
In the second loop, FindOldestFile calculates that the difference between myfile20070422.mdb's Last Modified date and the date in the oldestFileDate variable (which, at this point, is myfile20070423.mdb's Last Modified date) is 86,400 seconds. Again, t his number is greater than zero, so the function assigns myfile20070422.mdb to the oldestFileName variable and that file's Last Modified date to the oldestFileDate variable.
In the last loop, FindOldestFile calculates that the difference between myfile20070424.mdb's Last Modified date and the date in the oldestFileDate variable (which, at this point, is myfile20070422.mdb's Last Modified date) is -169,200 seconds. This number isn't greater than zero, so the function doesn't change the values in the oldestFileName and oldestFileDate variables. As this example shows, the order in which the files are processed through the For Each…Next loop doesn't matter.
The value that's left remaining in the oldestFileName variable is returned to the code at callout B. This code then uses the FileSystemObject object's DeleteFile method to delete that file. After it's deleted, the code again checks the backup folder's size. If the folder's new size is still greater than the size specified in MAX_TARGET_DIR_SIZE, the code calls the FindOldestFile function back into action, assuming there's more than one file left in the folder. This process repeats until the folder size is less than the specified threshold or until there is only one file left in the folder.
That's basically how the BackupFile.vbs script works. To get more information about how any of the mentioned objects, methods, properties, and built-in functions work, you can check out the VBScript and WSH resources listed in the article "How to Get Started Writing Scripts" (September 2006, InstantDoc ID 92751) or the VBScript and WSH resources listed in the Microsoft TechNet Script Center (http://www.microsoft.com/technet/scriptcenter/default.mspx).
