I received an alert from one of my two secondary servers (all servers are running 2012 SP1):

File 'E:\SQL\MS SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\MyDatabaseName_DateTime.tuf' is not a valid undo file for database 'MyDatabaseName (database ID 8). Verify the file path, and specify the correct file.

The detail in the job step shows this additional information:

*** Error: Could not apply log backup file 'MyDatabaseName_DateTime.trn' to secondary database 'MyDatabaseName'.(Microsoft.SqlServer.Management.LogShipping)
*** Error: Table error: Page (0:0). Test (m_headerVersion == HEADER_7_0) failed. Values are 0 and 1.
Table error: Page (0:0). Test ((m_type >= DATA_PAGE && m_type <= UNDOFILE_HEADER_PAGE) || (m_type == UNKNOWN_PAGE && level == BASIC_HEADER)) failed. Values are 0 and 0.
Table error: Page (0:0). Test (m_freeData >= PageHeaderOverhead () && m_freeData <= (UINT)PAGESIZE - m_slotCnt * sizeof (Slot)) failed. Values are 0 and 8192.

Starting a few minutes later, the Agent Job named LSRestore_MyServerName_MyDatabaseName fails every time it runs. The generated log backup, copy, and restore jobs run every 15 minutes.

I fixed the immediate problem by running a copy-only full backup on the primary, deleting the database on the secondary and restoring the new backup on the secondary with NORECOVERY. The restore job now succeeds and all seems fine. The secondaries only exists for DR purposes - no one runs reports against them or uses them at all. I had a similar problem last weekend on a different database that is also replicated between the same servers. I've been here for over a year, and these are the first instances of this problem that I've seen. However, I've now seen it twice in a week on the same server.

I'd love to know what caused the problems. Any ideas?

Reconfigure log shipping
https://social.msdn.microsoft.com/forums/sqlserver/en-US/c9bd9b7a-77bc-4663-8d6f-ed5c5ba1e075/logshipping-restore-log-failing-missing-tuf-file

Please check the Log shipping monitor someone might have changed the secondary restore options to standby , if this is the case when the next log restores on the secondary the DB will go to standby mode.

Keep the option to with norecovery as required.

I strongly suggest you to apply SP2 for SQL Server 2012 if this issue occurs frequently.

So when the restore job fails and you manually try to restore it what error you get can you also make sure there is no corruption in underlying disk system

Can you give more information about error which gets generated on failed restore you can refer to Job history

@Cheer08: The tuf file is not missing (nor am I on SQL 2000), so that link has no relevance. Thanks for trying though.

• Edited by m60freeman 15 hours 17 minutes ago

@PradyothanaDP: No one has made any changes to the configuration of anything on the secondary for months.

• Edited by m60freeman 15 hours 16 minutes ago

@Shanky_621: Can you point to an article that mentions a fix in SP2 that might be relevant to this issue? I did not attempt to manually restore the trn. The (automatically created) job that does so runs every 15 minutes and was continually failing until I did a restore from a new full backup. The error from the job history is in my original post.

• Edited by m60freeman 15 hours 16 minutes ago

@Shanky_621: Can you point to an article that mentions a fix in SP2 that might be relevant to this issue? I did not attempt to manually restore the trn. The (automatically created) job that does so runs every 15 minutes and was continually failing until I did a restore from a new full backup. The error from the job history is in my original post.

I am not sure whether this exactly applies to your scenario but please refer to below support article

https://support.microsoft.com/en-us/kb/2685132?wa=wsignin1.0

@Shanky_621: Thanks for the link. Unfortunately, that is a fix for when an instance fails over before the log shipping backup job finishes and produces a corrupt file. In my case, no fail over ever took place.

@Shanky_621: Thanks for the link. Unfortunately, that is a fix for when an instance fails over before the log shipping backup job finishes and produces a corrupt file. In my case, no fail over ever took place.

Well there is no harm in applying SP2, SP1 will soon but out of support