Directory lookup failed during log shipping

All we need is an easy explanation of the problem, so here it is.

I have configured log shipping for all our SharePoint 2010 databases. All worked well since Friday and today Monday I ran this query:

SELECT * 
FROM [msdb].[dbo].[sysjobhistory]
WHERE [message] like '%Operating system error%'

On the secondary server and got the error below. Basically it is SharePoint’s WebAnalyticsServiceApplication_ReportingDB which creates an extra database weekly and it seems this latest copy could not be found. What I’m not sure of is 2 things.

Why is it this database when viewed/backed up on the primary server shows as 1 db, but when copied/restored to another server, it shows up with its weekly breakdown.

My default sql installation and data folder is in in the H Drive, why is .Net SqlClient Data Provider looking in C drive for this one newly created SharePoint file?

2013-05-13 11:45:57.91 * Error: Could not apply log backup file
‘H:\Program Files\Microsoft SQL
Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\WebAnalyticsServiceApplication_ReportingDB##…_20130513061518.trn’
to secondary database
‘WebAnalyticsServiceApplication_ReportingDB##…’.(Microsoft.SqlServer.Management.LogShipping)
2013-05-13 11:45:57.91 Error: Directory lookup for the file
“C:\Program Files\Microsoft SQL
Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\WebAnalyticsServiceApplication_ReportingDB##…Aggregation20130519.ndf”
failed with the operating system error 3(The system cannot find the
path specified.). File
‘WebAnalyticsServiceApplication_ReportingDB##…Aggregation20130519’
cannot be restored to ‘C:\Program Files\Microsoft SQL
Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\WebAnalyticsServiceApplication_ReportingDB##…Aggregation20130519.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) *

Other than this one error, my log shipping works well. Any help?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

The file C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\WebAnalyticsServiceApplication_ReportingDB##...Aggregation20130519.ndf was probably created recently (looks like it was in the default location) and this path doesn’t exist on the log shipping target, so the RESTORE is failing because it doesn’t have a valid path to create this new file.

You will need to find the first log backup from when the file and restore WITH MOVE and create the new file in a valid location:

RESTORE LOG [foo]
FROM DISK='..._20130513061518.trn'
WITH MOVE 'NewFileLogical' to 'NewFilePhysical'
, STANDBY= '...StandbyFile'

Alternatively, you can reset the log shipping from scratch (so, full backup from the source, reapply and restart log shipping), which will require you to restore with the new file accounted for, but this is a “howitzer” style approach.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply