Modify Logshipping Restore Job

In my previous post I shared a stored procedure that dynamically creates and executes a restore log script in order to ease the recovery of logshipping after one or more files have been added to a logshipped database.

As I described in that post I was running into this problem on a monthly basis due to a sliding partition window scenario I maintain on several databases. After I wrote the stored procedure it was a lot easier to fix the problem after new files were added. But I still needed to wait for the logshipping restore job to fail and then intervene and execute the SP manually to recover from the failure. Being busy, not to mention forgetful, there were times when logshipping would be broken for several hours at a time.

Unfortunately, you cannot pro-actively inform the failover server that new files are on their way. But what finally did occur to me was to modify the job that restores the transaction log to include the stored procedure. In short, I added a second step that is run only if the first step fails. It works a treat.


You can find the stored procedure I wrote in my post An SP to Fix Logshipping After Adding a New Database File. See the screenshots below for details on adding the step to the logshipping restore job.

• In SSMS expand the SQL Server Agent Jobs tree and double-click on the LSRestore_xxxx job

• Click on 'Steps' and then click 'New'

• Add a title, select the database the stored procedure has been deployed to and then enter the EXEC statementinto the 'Command' box, for example: EXEC dbo.LogShippingAutoRestore @DBname = 'AdventureWorks',@Filepath = 'J:\Logshipping\AdventureWorks\'

• Click on the 'Advanced' tab and modify the 'On success action' to 'Quit the job reporting success' and click 'Ok'

• Select step 1 and click on 'Edit'

• Click on the 'Advanced' tab and modify the 'On failure action' to 'Go to step: [2] exec LogshippingAutoRestore' or whatever you named that step and click 'Ok'

• Now finally, click ok to save the job