Keeping QA Up to Date Part 2 - Finding the latest backup
In the first article in this series I looked at the basic process for keeping your QA machine up to date. Here I'll delve further into the first step in this process, getting the latest backup from your production machine.
Finding the Latest Backup
In my environment, we're always looking to refresh QA from the most recent backup of production. There might be some reason why you don't want to use the most recent backup, and if so you'll have to modify the scripting shown in this article to deal with that.
In my world, however, since this is a refresh for testing, we just take the most recent full backup and use that. My method for finding the most recent backup is a simple VBScript. I chose VBScript for a few reasons:
- Installed by default - At least the version of VBScript I need is on all W2K and W2k3 servers. Perl, etc. are not.
- Better file handling - VBScript does a better job, simpler, cleaner, than T-SQL
- Simple - VBScript is simple and easy to read and pass along to friends, like you fine folks 🙂
I place this in a DTS ActiveX script to make execution easy. I could easily run this as a scheduled task, a command execution from a job with cscript.exe, etc., but this is the simplest and easiest to me. Plus I can use a few global variables to make this a more configurable script. In my DTS script, I typically load the variables using the Dynamic Properties task and query a standard set of tables on each server. It makes deployment of the package very easy.
The script is shown below and then I'll describe how it works with the FileSystemObject.
Function Main() 'On Error Resume Next Dim strDestPath, fNewFile, strSourcePath, fOldFile, sBackup Dim fso, f, f1, fc, fcreated, fname, fldrItem, fldrName Dim objTxtFile, baseDate Dim SourceFile, DestFile Dim DebugFileName, ServerName ' Initialize the variables strSourcePath = DTSGlobalVariables("SourcePath").value strDestPath = DTSGlobalVariables("DestPath").value DebugFileName = DTSGlobalVariables("DebugFileName").value ServerName = DTSGlobalVariables("ServerName").value BaseDate = CDate("1/1/1900") Set fso = CreateObject("Scripting.FileSystemObject") ' Create the Text File Set objTxtFile = fso.CreateTextFile( strSourcePath & "\" & DebugFileName, TRUE) ' Write the Header objTxtFile.writeline( "Log for " & ServerName & " Find Last Backup" ) objTxtFile.writeline( "-------------------------------------------------------" ) objTxtFile.writeline( "Current Date: " & CDate(date) ) objTxtFile.writeline( "SourcePath: " & strSourcePath ) objTxtFile.writeline( "DestPath: " & strDestPath ) objTxtFile.writeline( "" ) Set f = fso.GetFolder(strSourcePath) ' Loop through all subfolders For Each fldrItem in f.SubFolders Set fc = fldrItem.Files fldrName = fldrItem.name fdate = BaseDate ' Loop through each file For Each f1 in fc If f1.DateLastModified > fDate and ucase(right(f1.name,3)) = "BAK" Then SourceFile = strSourcePath & "\" & fldrname & "\" & f1.name SourceFolder = fldrname DestFile = strDestPath & "\" & fldrname & "\" & f1.name if not fso.folderexists( DestFolder) then objTxtFile.writeline( " Creating " & DestFolder & "\" & fldrname & " (" & fso.folderexists( DestFolder) & ")" ) 'fso.CreateFolder( DestFolder) end if fDate = f1.DateLastModified End If Next objTxtFile.writeline("Most Recent for " & SourceFolder & " : " & SourceFile ) 'fso.CopyFile( SourceFile, DestFile, TRUE) Next ' Write closing to log file and close objTxtFile.writeline( "" ) objTxtFile.writeline( " End " ) objTxtFile.Close ' Clean up objects Set objTxtFile = Nothing Set fso = Nothing Set fc = Nothing Main = DTSTaskExecResult_Success End Function
There are various configuration parameters to tell the script where to look for the files. These are stored as global variables. I have chosen the following:
|SourcePath||The top level folder containing sub folders for each database. This is also the place where the log file is stored. This can be a local folder or a UNC path to a remote system|
|DestPath||Destination folder under which you want the files transferred. Used in a more advanced version where the actual file is copied to the new server. The copy command is commented out in the this script.|
|DebugFileName||Name of the text file that logs all the things the script does.|
|ServerName||SQL Server name used for logging. Used in a more advanced version of this script.|
|LastXfer||Last date a transfer occurs. Used in a more advanced version of this script that logs to a SQL Server.|
We have a standard backup folder, but this is one of those configurable parameters, in this case, using a global variable. Since I always backup each database into its own folder, I loop through the subfolders to find the latest backup in each. If you happen to want the backup in only one folder, remove the outer loop that finds the list of subfolders and set the fldrName variable to the folder you need, preferably using a global variable.
From there the script runs in a loop, looking for all files that end in .BAK, the extension that I use for backups. If you use a different extension, .DMP for example, you can modify the script to search for these.
As each file is found, the filedate is compared to the most recent filedate stored in the fDate variable. This variable is set to 1/1/1900 as a starting point and to date I've yet to find a backup before this date :). If it's later than this date, we store this filename and change the date to be the date of this file. When we have completed the loop, this will ensure that the most recent file, which corresponds to the most recent backup, is stored in the FileName variable.
From there it's an easy step to perform the copy of this file to the target directory, return it to the calling program, write to a SQL database, whatever. The script above shows a copy file that is commented out.
That's it. Pretty simple and straightforward, at least I think it is. If you feel differently, please feel free to post a comment below and I'll try to explain it better. In the next installment of this series, we'll look at the process of getting this file restored. You can also easily add more steps to this script to get the latest differential and all the logs that are available if needed as well. That would allow you to implement you own method of log shipping.
©dkRanch.net February 2004