Automate Restoration of Log Shipping Databases for Failover in SQL Server

By:   |   Comments (5)   |   Related: 1 | 2 | 3 | 4 | 5 | > Log Shipping


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?


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
@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_dirdestination_databaselast_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
@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 

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 objShell

Set fso CreateObject("Scripting.FileSystemObject"

'Parameter values passed by TSQL script

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
="FROM DISK='" strDestinationDir &"\" strLastFileLoaded "'"

objMyFile.WriteLine (str1)
objMyFile.WriteLine (str2)
objMyFile.WriteLine (str3)


objFSO = Nothing
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
folder = Nothing
files = Nothing
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

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Edwin Sarmiento Edwin M Sarmiento is a Microsoft SQL Server MVP and Microsoft Certified Master from Ottawa, Canada specializing in high availability, disaster recovery and system infrastructures.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Friday, August 31, 2012 - 11:50:50 AM - lino Back To Top (19348)

Thank you kindly for that Link ;-)


Friday, August 31, 2012 - 10:46:41 AM - bass_player Back To Top (19343)

In SQL Server 2008, you can use PowerShell for this purpose. You can check out the script in here. Just change osql.exe to sqlcmd

Friday, August 31, 2012 - 6:40:33 AM - lino Back To Top (19338)

Hi guys,


Is there a SQL command for 2008 Server as this one above says for 2000, will the same apply...

I know this post is old but uselful ;-)


Tuesday, July 15, 2008 - 5:57:57 PM - bass_player Back To Top (1436)

Hi Vic,

Thanks for your query. I initially thought about that approach but that is quite a bit unreliable.  There are cases when during the copy process, the transaction log backup file is already on the folder where the transaction logs are stored on the standby server but the file copy is not yet complete. I've seen this happened when trying to copy over a large transaction log backup file over a slow network as what I've found out while implementing this. Besides, the goal in disaster recovery is to bring up the standby databases as quickly as possible and the surest way to do this is to restore and recover the last successful transaction log backup though it may mean losing a few minutes of data depending on your log shipping configuration.

You could, however improve upon the process by retrieving the files older than the latest successful restore and restoring them while writing the results to a log. You can then read the log if it's successful or not and proceed with the process as necessary 

Tuesday, July 15, 2008 - 3:01:28 AM - Vic Back To Top (1432)

Hi, in the second step, shouldn't you be iterating through the files in the folder that are older than strLastFileLoaded and create a restore command for each of these files? Instead you restore only the last log file loaded which has already been loaded of course. What is the logic behind this?

Thank you,


get free sql tips
agree to terms