Fixing SQL Server Log Shipping after new DB file has been added to Primary

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:

SELECT [rs].[destination_database_name],
[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’
with norecovery,
MOVE ‘DBName_Data3’
TO ‘E:\Data\MSSQL10_50.PRD\MSSQL\DATA\DBName_2.ndf’

Once DB is restored, you should be able to restart your log shipping restore automated jobs.

Happy learning!

Aman Kharbanda


Author: sqlserverposts

Database Consultant by profession. I am a geek and a avid sports lover too. "Progress informed by the past, inspired by future".

1 thought on “Fixing SQL Server Log Shipping after new DB file has been added to Primary”

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s