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:

Message
2014-01-09 08:10:18.12 *** Error: Could not apply log backup file 'C:\SQL\Logshipping\PartWindow_20140109080941.trn' to secondary database 'PartWindow_ls'.(Microsoft.SqlServer.Management.LogShipping) ***
2014-01-09 08:10:18.12 *** Error: The file 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\logshiptest.ndf' cannot be overwritten.  It is being used by database 'PartWindow'.
File 'logshiptest' cannot be restored to 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\logshiptest.ndf'. Use WITH MOVE to identify a valid location for the file.
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
RESTORE LOG is terminating abnormally.(.Net SqlClient Data Provider) ***
2014-01-09 08:10:18.16 *** Error: The log backup file 'C:\SQL\Logshipping\PartWindow_20140109080941.trn' was verified but could not be applied to secondary database 'PartWindow_ls'.(Microsoft.SqlServer.Management.LogShipping) ***
2014-01-09 08:10:18.16 Deleting old log backup files. Primary Database: 'PartWindow'
2014-01-09 08:10:18.16 The restore operation completed with errors. Secondary ID: '32ef56ad-d8ee-4437-b3a0-e5658f6d2d4b' 

Fixing this is not difficult, it is just a bit fiddly. There is a really good post on mssqltips.com explaining the steps necessary to fix the problem. Basically, I've gotten bored by it all and decided to write a stored procedure that can more easily fix the problem.

You need to pass the logshipped database name and the file path to where the transaction log backups are on the failover server. I imagine the bast place for this stored proc is either on the msdb database or an admin database on the failover server. You also need xp_cmdshell to be enabled.

The stored procedure performs the following steps:

• Identifies the last file that was successfully restored
• Identifies the subsequent file - the first file to fail
• Identifies the new logical file name
• Identifies the physical location of the most recent database file
• Creates and executes the restore log script with the appropriate WITH MOVE clause
• Will move any number of files that have been added to the source database

 Feel free to let me know if this has been helpful and how it has worked for you.