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.

An SP to Fix Logshipping After Adding a New Database File

UPDATE: 22 Jan 2014 - Fixed some bugs, added multi-file support and added a few notes

Every month I need to maintain the various sliding partition windows on several databases. If you've read some of my previous posts you may remember that this involves adding new files for the upcoming partitions. My company's databases are also logshipped as part of the disaster recovery strategy.

What invariably happens every month is I add new files make the necessary partition changes and then wonder why, a little later in the day, I'm receiving alerts for failed transaction log restores. The logshipped database obviously doesn't "know" about the new file and I end up with an error message like this: