After adding a new data file on the Publisher server database, the Log Shipping restore job fails to apply transaction logs on the Subscriber.
The error from the LiteSpeedLocal.dbo.LSTrackingLog table is:
File 'filename' cannot be restored to 'file_location\filename'. Use WITH MOVE to identify a valid location for the file.
Log shipping does not allow database file operations such as adding a data file and using different disk drive in publisher, if that same location is not found in subscriber.
1. Disable all the logshipping jobs in the subscriber server
2. Determine which T-log file is the next to be restored but failed. Restore the file manually using a similar script:
exec master.dbo.xp_restore_log @database = N'logshiptest', @filename = N'E:\QUEST_logshiptest_1251265440_FE0BC084-5C24-4F9D-82C5-01B19D1D004E_TLOG.trn', @filenumber = 1, @with = N'STANDBY = N''E:\data\undo''', @with = N'NOUNLOAD', @with = N'STATS = 10', @with = N'REPLACE', @with = N'MOVE N''VOS_EIG_custom_indexes'' TO N'''E:\QUEST_logshiptest_custom_indexes.ndf''''
In the example above, you need to add the MOVE command to tell where to place the QUEST_logshiptest_custom_indexes data file.
3. Enable the logshipping jobs and they will catch up.