If you are maintaining a standby copy of your PRD DB enabled with log shipping mechanism, and it breaks after a data file was added to your Primary database, refer to the below to overcome this kind of situation.
You might see restore errors like these on your secondary DB server-
Error: Could not apply log backup file ‘\\File server Name\MSSQL\PRD\DBName_20151111199999.trn’ to secondary database ‘DBName’.(Microsoft.SqlServer.Management.LogShipping) *** 2015-11-12 13:30:11.98 *** Error: Directory lookup for the file “E:\Data\MSSQL10_50.PRD\MSSQL\DATA\DBName_2.ndf” failed with the operating system error 3(The system cannot find the path specified.). File ‘DBName_Data3’ cannot be restored to ‘E:\Data\MSSQL10_50.PRD\MSSQL\DATA\DBName_2.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) ***
Check which transaction log has been last applied on Secondary DB (Via T-SQL or Log Shipping Status Report).
You may use this t-sql to get the status:
[bs].[database_name] as [source_database_name],
[bmf].[physical_device_name] as [backup_file_used_for_restore]
FROM msdb..restorehistory rs
INNER JOIN msdb..backupset bs
ON [rs].[backup_set_id] = [bs].[backup_set_id]
INNER JOIN msdb..backupmediafamily bmf
ON [bs].[media_set_id] = [bmf].[media_set_id]
ORDER BY [rs].[restore_date] DESC
Now to validate which secondary file was lately added to your DB, use the below T-SQL:
RESTORE FILELISTONLY FROM DISK=’\\File server Name\MSSQL\PRD\DBName_20151111199999.trn’
And the final step would be to restore the transaction log with norecovery and move option:
RESTORE log DBName FROM Disk=’\\File server Name\MSSQL\PRD\DBName_20151111199999.trn’
Once DB is restored, you should be able to restart your log shipping restore automated jobs.