-1

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?

Improve this question
0

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.

Improve this answer
7
  • Thanks Mike Fal. Quick questions, The first approach you suggested, would that be a one time fix? And the second one does seem like if i went that way, i might need to do it weekly since that file gets created weekly(and if thats the case, i do delete saved files older than 24hrs, and once i reset LS, would i need to cleanup the old logs and data files for this sharepoint db WebAnalyticsServiceApplication_ReportingDB? thanks. – shilezi May 14 '13 at 18:27
  • Both approaches are a one time fix. Once you restore with move, the engine will know where the new file goes and will apply all new changes appropriately. This, of course, will only work until another new file is added. Ultimately, this is why you want to have identical path structures for your database files on both log shipping source and target. – Mike Fal May 14 '13 at 19:02
  • Thanks again @Mike-Fal. Now, I just need to get these names right and heres what i have RESTORE LOG [WebAnalyticsServiceApplication_ReportingDB##...] FROM DISK=N'H:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\WebAnalyticsServiceApplication_ReportingDB##..._20130513061518.trn' WITH MOVE 'NewFileLogical' to 'NewFilePhysical' , STANDBY= '...StandbyFile' – shilezi May 14 '13 at 21:04
  • And I just got confused a little bit more now. When I configured LS, the H drive is the location for data files(MDF and LDF) on the SECONDARY server and I also configured the backup files(BAK and TRN) to be shipped to the share location on another drive I called BkLS. Now I'm seeing transactional logs(TRN) at both locations(local H as well as on the shared drive) and my understanding while setting up LS is that backups and log files are shipped to I drive(BKLS) which could be local to primary or remote(shared on secondary) or however and in the event of a restore, be applied. – shilezi May 14 '13 at 21:12
  • Now, which TRN file is applied and what do these 3 parameters represent (NewFileLogical, NewFilePhysical & StandbyFile)? Thanks again! – shilezi May 14 '13 at 21:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.