Keeping QA Up to Date Part 2 - Finding the latest backup

Introduction

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:

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.

Steve Jones

©dkRanch.net February 2004

Return to Steve Jones Home