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:

Database Snapshot Stored Procedure

We've been trying to tighten up the database release process at work. This involves lots of big and small changes including things like using version control software better, documenting database code as well as putting together some default scripts that run with every release.

It occurred to me that even if you have a good process and set of check lists for the release cycle it is still quite easy to miss out on small but ultimately important steps. Things get hectic, last minute changes are made, etc, etc., and the next thing you know you've forgotten a basic, but important, step like creating a database snapshot or disabling scheduled jobs or revoking user access for the duration of the release. There are probably a dozen other small tasks that can be left out.