-
Title
Log shipping restore failed after adding new data file on the Publisher:use WITH MOVE -
Description
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. -
Cause
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.
-
Resolution
WORKAROUND:
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.