Problem
When failing over to a standby server in a log shipping pair, we need to restore the latest transaction log backup to make the standby databases ready for access. This process may be cumbersome if it involves manually restoring more than five database in a single server while at the same time getting the standby server available as fast as we can. We need to automate the process of identifying the log shipping databases on the standby server, location of the transaction log backups being shipped from the primary server, and the latest transaction log backup that was restored so we can use that to recover the databases. How do we do it?
Solution
This solution reads the msdb database from the standby server and generates a script to restore all the databases that are in a log shipping pair. For this solution, we need to create several folders to store the scripts that will be generated. It will be safer to store the generated scripts in the file system just in case the automated process fails. In this example, I have created a folder which will contain the all the scripts – the VBScript in Step #2 and the TSQL script that will be generated by the VBScript
Step 1 – Create the TSQL script
The TSQL script below will generate a set of TSQL scripts containing RESTORE LOG commands for all the databases in a log shipping pair. The number of scripts created will depend on the number of databases in a log shipping pair
/*
This script is used to restore the transaction log database backups in a log shipping pair
on the standby server for failover. This is useful for failover of a SQL Server 2000 instance
with more than 5 databases
*/
USE msdb
SET NOCOUNT OFF
DECLARE @strSQL NVARCHAR(200) –variable for dynamic SQL statement – variable size should change depending on the location of the backup directory
DECLARE @strDestinationDir NVARCHAR(50) –variable for destination directory
DECLARE @strDestinationDB NVARCHAR(50) –variable for destination database
DECLARE @strLastFileLoaded NVARCHAR (255) –variable for last TRN backup restored
DECLARE MyCursor CURSOR FOR –used for cursor allocation
SELECT destination_dir, destination_database, last_file_loaded
FROM log_shipping_plans a INNER JOIN log_shipping_plan_databases b
ON a.plan_id=b.plan_id
OPEN MyCursor
FETCH Next FROM MyCursor INTO @strDestinationDir, @strDestinationDB, @strLastFileLoaded
WHILE @@Fetch_Status = 0
BEGIN
SET @strSQL = ‘master.dbo.xp_cmdshell ”E:\scripts\RESTORE_LOG_RECOVERY.vbs ‘ + @strDestinationDir + ‘ ‘ + @strDestinationDB + ‘ ‘ + @strLastFileLoaded + ””
EXEC sp_executesql @strSQL
FETCH Next FROM MyCursor INTO @strDestinationDir, @strDestinationDB, @strLastFileLoaded
END
CLOSE MyCursor
DEALLOCATE MyCursor Step 2 – Create a VBScript file
The VBScript file will be responsible to generate the TSQL scripts. This VBScript will be called by the TSQL script in Step #1 and accepts three parameters – the name of the database, the location of the transaction log backups and the latest transaction log backup that was restored. Save this script as RESTORE_LOG_RECOVERY.vbs inside the E:\scripts folder. You also need to create a sub-folder named SQL where the generated TSQL scripts will be stored. This is just for file management purposes.
On Error Resume Next
Dim fso, folder, files, sFolder, strDestinationDir, strDestinationDB
Dim objShell
Set fso = CreateObject(“Scripting.FileSystemObject”)
‘Parameter values passed by TSQL script
strDestinationDir=Wscript.Arguments.Item(0)
strDestinationDB=Wscript.Arguments.Item(1)
strLastFileLoaded=Wscript.Arguments.Item(2)
sFolder = strDestinationDir & strDestinationDB
Set folder = fso.GetFolder(sFolder)
Set files = folder.Files
Set objShell = CreateObject(“Wscript.Shell”)
Set objFSO = CreateObject(“Scripting.FileSystemObject”)
‘Create the file to contain the SQL script
If (objFSO.FileExists(“E:\scripts\SQL\” & strDestinationDB & “.sql”)) Then
objFSO.DeleteFile (“E:\scripts\SQL\” & strDestinationDB & “.sql”)
End If
‘Create SQL file to store the TSQL RESTORE LOG script
Set objMyFile = objFSO.CreateTextFile(“E:\scripts\SQL\” & strDestinationDB & “.sql”, True)
str1=“RESTORE LOG ” & strDestinationDB
str2=“FROM DISK='” & strDestinationDir &“\” & strLastFileLoaded & “‘”
str3=“WITH RECOVERY”
objMyFile.WriteLine (str1)
objMyFile.WriteLine (str2)
objMyFile.WriteLine (str3)
objMyFile.Close
Set objFSO = Nothing
Set objMyFile = Nothing
‘Run an OSQL command that uses a RESTORE LOG WITH RECOVERY and store results in a TXT file
objShell.Run(“osql -S<instancename> -E -iE:\scripts\SQL\” & strDestinationDB & “.sql -oE:\scripts\SQL\” & strDestinationDB & “_results.txt”)
‘Cleanup objects
Set fso = Nothing
Set folder = Nothing
Set files = Nothing
Set objShell = Nothing
After the corresponding folders and scripts have been created, run the T-SQL script in Step #1 using either Query Analyzer or create a SQL file which you can run using osql. This will be helpful if an emergency failover is necessary and the database administrator is not available
Next Steps
- Review your disaster recovery process and include this automation procedure as part of your plan
- Take a look at other backup and recovery tips
Edwin M. Sarmiento is the Managing Director of 15C, a consulting and training company that specializes on designing, implementing and supporting SQL Server infrastructures. He is a Microsoft SQL Server MVP and Microsoft Certified Master from Ottawa, Canada specializing in high availability, disaster recovery and system infrastructures running on the Microsoft server technology stack ranging from Active Directory to SharePoint and anything in between. He is very passionate about technology but has interests in music, professional and organizational development, and leadership and management matters when not working with databases. Edwin lives up to his primary mission statement: “To help people and organizations grow and develop their full potential.”
- MSSQLTips Awards: Champion Award (100+ tips) – 2017 | Author of the Year Contender – 2020, 2017
