I ran into an interesting problem recently. There is a 4TB database that is log shipped to a DR site, and some new data files needed to be added to the database.

Mistake #1: The drive volumes on the DR server are not configured the same as the primary site.

Mistake #2: This mismatch was forgotten about when the new files were added.

So, what went wrong? When the new files were added, this was stored in the transaction log to be replayed, including the file path. Since the path that was specified on the primary site wasn’t valid on the DR site, when the transaction log backup was being restored on the DR site, the restore failed. Along with every transaction log backup that was attempted to be restored after that.

If the drives on the DR site had been configured identically to the primary site, it would have been a simple matter of just ensuring that the necessary volumes / paths existed, and the restore would have created the file. But, alas, this is not the case.

At this point, I’m thinking… jeez, I’ve done it now. This has broken log shipping. (Do you have any idea of how long it takes to bring a log shipping secondary online with a 4TB database? The fastest way is to make a full backup, copy it to an external drive, overnight it to the DR location, copy it back to a local disk, and do the restore. Meanwhile, start saving those transaction log backups. We’re talking multiple days here… days without a DR site in place.) Not acceptable.

Of course, it’s now after midnight. You know how that goes… the part of the brain that’s not in panic mode is barely working. Caffeine doesn’t even help at this stage. You have beads of sweat building on your forehead and your heart is pounding. You HAVE to fix this, and the DR site has to stay up.

And with all of this adrenalin pumping, it’s important to take a step back and see the big picture. If a full backup was to be sent to the DR site, what would have to be done with it to restore it? Why, the restore needs to be performed with the MOVE FILE option. Hmm, would that work for only some of the files – specifically just the new file(s)?

Long story short (too late!), this in fact does work.

The first step is to identify which transaction log backup file is throwing the error (this would be the first transaction log backup file generated after the files were added).  Once identified, pause the log shipping restore job, leaving the other jobs running.

The second step is to perform a manual restore of the transaction log. You need to specify all of the options that you would be using for the log shipping restore (NORECOVERY or STANDBY), and then specify the MOVE FILE clause(s) that you would need to put the proper logical file name(s) in the proper physical location(s). You should only specify the new file(s) that were added.

The third step is to resume the other log shipping restores (because by this point, several have accumulated while you figured out what to do). When this catches up, you can proceed to the last step.

Which is to let the appropriate folks know that all is well, and to go home. Maybe even grab a beverage. You know… to celebrate.