Restore Log Shipping without full backup when added data\log file at Primary server

Hi SQL Server Folks – Before some time I faced a scenario when Log Shipping was setup between two servers (say Primary and Secondary Server). For some reason we added one more file in a database at Primary Server side and after that Log Shipping start breaking and we got below error message :

 Log Shipping Failure By Gaurav ShrivastavIn this scenario you need not to restore full backup from the primary, you can simply restore peculiar transaction log file with move option. You will get all desired information in your Log-Shipping restore job, same as you can see in above error.  I’m explaining all the errors and its meaning here step by step :

Message 2013-01-20 03:00:01.38 *** Error: Could not apply log backup file ‘H:\ProdDB_eWork_Copy\eWorkBackup\Tran\ eWork_20130119080001.trn’ to secondary database ‘eWork_ProdCopy’.(Microsoft.SqlServer.Management.LogShipping) *** 

  • This is the transaction log file which was not able to apply at Secondary server side because we added one data file at primary server and not at secondary server.

2013-01-20 03:00:01.38 *** Error: Directory lookup for the file ” h:\Mount16\eWork\CDCFileGroup.ndf” failed with the operating system error 3(The system cannot find the path specified.).

  • This is the data file name and its location which got added at Primary server and not at secondary server, we need to specify that file in our Restore Log option. Don’t create this file manually, Restore Log option will auto. take care of it.

File ‘ CDCFileGroup.ndf ‘ cannot be restored to ‘h:\Mount16\eWork\CDCFileGroup.ndf”. Use WITH MOVE to identify a valid location for the file.

  • This is the Logical file name of newly added data file at Primary server side.

Problems were identified while planning for the RESTORE statement. Previous messages provide details. RESTORE LOG is terminating abnormally.(.Net SqlClient Data Provider) ***

Now Open Query Analyzer and fire below query.

 RESTORE log eWork_ProdCopy FROM Disk=’H:\ProdDB_eWork_Copy\eWorkBackup\Tran\eWork_20130119080001.trn’ WITH MOVE ‘CDCFiles ‘TO’ H:\MSSQL10_50.SSIS\MSSQL\eWork_ProdCopy\Data\CDCFiles’,

 — this is the location in secondary server that you need to define.


After that restore command completion, you can manually kick of Log-Shipping copy and restore Job one by one at Secondary server. It’ll restore all remaining files.  To see the sync up status of Log-Shipping with primary server run below commands.

select * from log_shipping_secondary_databases

select * from log_shipping_monitor_secondary


Hope this info. will be useful for you.


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 )

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s

%d bloggers like this: