Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Automate Restoration of Log Shipping Databases for Failover in SQL Server


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

Attend these FREE MSSQLTips webcasts >> click to register


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_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
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 
fsofolderfilessFolderstrDestinationDirstrDestinationDB 
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


Last Update:


signup button

next tip button



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.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



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

Thank you kindly for that Link ;-)

 


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

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

http://bassplayerdoc.wordpress.com/2010/05/01/your-poor-mans-sql-server-log-shipping-powershell-version/


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

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

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

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,

Victor


Learn more about SQL Server tools