Awhile back I was tasked with designing and implementing a disaster recovery solution for one of our production application’s databases. I decided to take a very simple approach for a couple of reasons. I chose to simply create SQLAgent backup jobs on the production box that backup to a share on the DR box. Why not use mirroring or log shipping? I took this route for the following reasons:
- We have very large pipes between our data centers, so keeping the data in small chunks with mirroring or log shipping is not necessary. Also the DBs are small.
- This gives me point in time (full recovery model) backups in a remote location. Another group handles server and application backups and the last time I needed a restore they “forgot” to setup the backups.
- Security over the wire is not an issue since our security team is like a cast iron chastity belt. Seriously….we got bought 5 years ago and they still block traffic from the company that bought us even though that company has opened its firewalls to us. Hey guys we are all the same company, let’s play nice.
- The required uptime for this application allows some leniency. Besides, I can have the DBs restored long before the developer can change his code to point to the new location and DNS to replicate.
I’m a big believer in automation, because less is more. I was also itching to write some VBScript, so I decided to script the restoration of the DBs. This script makes some assumptions on how you design your backup job schedules so it will not work in every situation. This script assumes that you take a full backup sometime in the AM and a differential sometime in the PM. We are restoring these backups onto a different server than where the backups were taken. This means we do not have local access to the SQL MSDB for backup information unless we remotely query the source server. Since the backup files are local and SQL can’t tell us the difference between a full and differential backup (Please comment and correct me if you know a way) there is no need to query MSDB for any information anyway. I bring this up because if we could query SQL to find the last full and differential backups, then it would not matter how we scheduled our backups and no assumptions would need to be made. As far as I have ever known SQL only shows backups as one of two types, either a data file backup or a log file backup. Since full and differentials are both data file backups there does not appear to be a way to differentiate between the two. The last assumption is that you do not backup multiple databases in a single folder. Things get messy if you do, so I suggest avoiding that as a general rule. Here is where you can specify separate folders for each database while creating a maintenance plan within the “Backup Database Task”.
Backup Database Task
We start off declaring some variables and then initialize the ones that define the name of the DB we are restoring, the local drive letter where the backups are located, and the full path to the backup folder on that drive. Now we create a connection to the local SQL server that will use the credentials under which the script is run. We also create a connection to WMI and create a log file to record the name of each DB file that got restored. Note that the log file will get overwritten every time the script is run. You can change this to simply append, but make sure you also insert the date and time. Alternately you could also build a log table and store it all in SQL.
Dim strComputer, strDrive, strPath, strDBName, colDBFilePath, colLogFilePath
Dim colDBName, strSQLRestore, colLogName, strSQLFixUser
strDBName = “MyRestoreDB”
‘This is the drive letter where your back files are located on the local machine
strDrive = “D:”
‘This is the path where your back files are located on the local machine
strPath = “\\MyBackupFolder\\” & strDBName & “\\” ‘WMI requires double backslashes in its path names
strComputer = “.”
‘=========================== SQL Connection =====================================================
Set objSQLConnection = CreateObject(“ADODB.Connection”)
objSQLConnection.Open _
“Driver={SQL Server};Server=localhost;” & _
“Trusted_Connection=No;database=master;”
‘================================================================================================
‘=========================== WMI Connection =====================================================
Set objWMIService = GetObject(“winmgmts:” _
& “{impersonationLevel=impersonate}!\\” & strComputer & “\root\cimv2″)
‘================================================================================================
‘=========================== Output Log Creation ================================================
Set objFSO = CreateObject(“Scripting.FileSystemObject”)
Set objTextFile = objFSO.CreateTextFile(“c:\Scripts\DBRestoreLog.txt”,True)
‘================================================================================================
Now we need to get the file name and location where SQL tells us it is storing the DB, so we have that for the restore. We’ll also need a list of all the backup “BAK” files in the backup directory to determine which full backup is the most recent and if there was a differential taken after it. In order to do all that we query WMI for the list of files and store them in a recordset where we can sort them by their last modified date. Since we know we do a full in the AM and a differential in the PM, we know if the last modified date of the most recent file is in the AM then all we have to restore is that full backup. If the most recent backup file is in the PM then we know it was a differential and we have to restore the file before it since that will be the most recent full and then restore the differential.
‘=============================================================================================================================
‘FULL AND DIFFERENTIAL FILES
‘=============================================================================================================================
colDBFilePath = objSQLConnection.Execute(“Select filename from sys.sysaltfiles where dbid = db_id(‘” & strDBName & “‘) and fileid=’1′”)
colLogFilePath = objSQLConnection.Execute(“Select filename from sys.sysaltfiles where dbid = db_id(‘” & strDBName & “‘) and fileid=’2′”)
colDBName = objSQLConnection.Execute(“Select name from sys.sysaltfiles where dbid = db_id(‘” & strDBName & “‘) and fileid=’1′”)
colLogName = objSQLConnection.Execute(“Select name from sys.sysaltfiles where dbid = db_id(‘” & strDBName & “‘) and fileid=’2′”)
Set colFiles = objWMIService.execquery _
(“Select * from CIM_DataFile where Path = ‘” & strPath & “‘” _
& “And Drive = ‘” & strDrive & “‘ and Extension = ‘bak’”)
Set rsBackup = CreateObject(“ADODB.Recordset”)
rsBackup.Fields.Append “strFileName”, 200, 255
rsBackup.Fields.Append “strFileDate”, 7
rsBackup.Open
For Each objFile in colFiles
rsBackup.AddNew
rsBackup(“strFileName”) = objFile.Name
rsBackup(“strFileDate”) = WMIDateStringToDate(objFile.lastmodified)
rsBackup.Update
Next
rsBackup.Sort=”strfiledate”
rsBackup.MoveLast
If ucase(Right(rsBackup.Fields.Item(“strFileDate”),2)) = “AM” Then
’Restore Full
objTextFile.WriteLine(rsBackup.Fields.Item(“strfilename”) & vbTab & rsBackup.Fields.Item(“strfiledate”) & VbCrLf)
strStartDate = rsBackup.Fields.Item(“strfiledate”)
strSQLRestore = “RESTORE DATABASE [" & strDBName & "] FROM DISK = N’” & rsBackup.Fields.Item(“strfilename”) & _
”‘ WITH FILE = 1, MOVE N’” & colDBName(0) & “‘ TO N’” & colDBFilePath(0) & “‘, MOVE N’” & colLogName(0) & “‘ TO N’” & colLogFilePath(0) & “‘, NORECOVERY, REPLACE”
objSQLConnection.Execute(strSQLRestore)
Else
’Restore Full
rsBackup.MovePrevious
objTextFile.WriteLine(rsBackup.Fields.Item(“strfilename”) & vbTab & rsBackup.Fields.Item(“strfiledate”))
strSQLRestore = “RESTORE DATABASE [" & strDBName & "] FROM DISK = N’” & rsBackup.Fields.Item(“strfilename”) & _
”‘ WITH FILE = 1, MOVE N’” & colDBName(0) & “‘ TO N’” & colDBFilePath(0) & “‘, MOVE N’” & colLogName(0) & “‘ TO N’” & colLogFilePath(0) & “‘, NORECOVERY, REPLACE”
objSQLConnection.Execute(strSQLRestore)
’Restore Differential
rsBackup.MoveNext
objTextFile.Writeline(rsBackup.Fields.Item(“strfilename”) & vbTab & rsBackup.Fields.Item(“strfiledate”) & VbCrLf)
strSQLRestore = “RESTORE DATABASE [" & strDBName & "] FROM DISK = N’” & rsBackup.Fields.Item(“strfilename”) & _
”‘ WITH FILE = 1, MOVE N’” & colDBName(0) & “‘ TO N’” & colDBFilePath(0) & “‘, MOVE N’” & colLogName(0) & “‘ TO N’” & colLogFilePath(0) & “‘, NORECOVERY, REPLACE”
objSQLConnection.Execute(strSQLRestore)
strStartDate = rsBackup.Fields.Item(“strfiledate”)
End If
The last thing we need to do is find and restore all the transaction log files. We use the same method of querying WMI for all the files in our backup folder that have an extension of “TRN”. We then save them in a recordset so we can sort them by date. In the previous section of code you’ll notice we stored the date of that last backup file we restored in a variable called “strStartDate”. That allows us to compare it to the current transaction log date and skip restoring any log backups that were taken prior to the last full or differential backup we just restored. As we iterate through the files in the recordset the other condition we have to check for is whether or not it is the last record in the set. We do this because we have to specify “norecovery” in our TSQL restore command until we restore the last log file. At that point we remove ”norecovery” from our TSQL restore command (or you can change it to “recovery”, which is the default) so SQL can rollback any uncommitted transactions and make the DB available again.
‘=============================================================================================================================
‘TRANSACTION FILES
‘=============================================================================================================================
Set colFiles = objWMIService.ExecQuery _
(“Select * from CIM_DataFile where Path = ‘” & strPath & “‘” _
& “And Drive = ‘” & strDrive & “‘ and Extension = ‘trn’”)
Set rsTran = CreateObject(“ADODB.Recordset”)
rsTran.Fields.Append “strFileName”, 200, 255
rsTran.Fields.Append “strFileDate”, 7
rsTran.Open
For Each objFile in colFiles
rsTran.AddNew
rsTran(“strFileName”) = objFile.Name
rsTran(“strFileDate”) = WMIDateStringToDate(objFile.lastmodified)
rsTran.Update
Next
rsTran.Sort=”strfiledate”
rsTran.MoveFirst
Do Until rsTran.EOF
If rsTran.Fields.Item(“strfiledate”) < strStartDate Then
rsTran.MoveNext
Else
objTextFile.WriteLine(rsTran.Fields.Item(“strfilename”) & vbTab & rsTran.Fields.Item(“strfiledate”))
If rsTran.AbsolutePosition <> rsTran.RecordCount Then
strSQLRestore = “RESTORE LOG [" & strDBName & "] FROM DISK = N’” & rsTran.Fields.Item(“strfilename”) & _
”‘ WITH FILE = 1, NORECOVERY”
objSQLConnection.Execute(strSQLRestore)
Else
strSQLRestore = “RESTORE LOG [" & strDBName & "] FROM DISK = N’” & rsTran.Fields.Item(“strfilename”) & _
”‘ WITH FILE = 1″
objSQLConnection.Execute(strSQLRestore)
End If
rsTran.MoveNext
End If
Loop
This last piece of the script just closes the text file we opened to log our actions and includes a function that converts the WMI date format to something more familiar. For more details on the WMI date conversion function, visit this article in the Microsoft Script Center that was written by the ScriptingGuys. Now we have successfully restored our database on our DR server and hopefully the crisis is averted. Of course now you have to go fix the production system…..Good luck!
objTextFile.Close
Function WMIDateStringToDate(dtmInstallDate)
WMIDateStringToDate = CDate(Mid(dtmInstallDate, 5, 2) & “/” & _
Mid(dtmInstallDate, 7, 2) & “/” & Left(dtmInstallDate, 4) _
& ” ” & Mid (dtmInstallDate, 9, 2) & “:” & _
Mid(dtmInstallDate, 11, 2) & “:” & Mid(dtmInstallDate, _
13, 2))
End Function
