December 24, 2010 at 2:51 pm
Hi All: I have a similar problem with SQL2005 transaction logs.
I had a log-shipping setup that was running fine. At one point, the primary server went offline, but I was able to reboot it and bring it back online after a couple of hours.
Before the primary server came back online, I took the dB out of restore mode on the secondary, and did an immediate full backup, thinking I might need to fail over to the secondary server dB. However I never failed over, because the primary came back online right after that.
Then I forgot to put the secondary back into "restoring", so no .TRNs were being restored to it. When I finally realized the error, I was not able to restore any additional TRNs to the secondary...I'm getting the dreaded "This backup set cannot be applied because it is on a recovery path that is inconsistent with the database."
The primary server just went down again, but I have all the transaction logs since the last time it failed saved on the secondary server. I just can't restore them to the secondary dB.
I gather that backing up the secondary database after taking it out of recovery mode broke the chain of transaction log backups. But the dB was not modified beyond backing it up. Is there any way to "force" it to restore the new TRN logs from the primary server? Or perhaps restore it to a point in time right before backing it up?
December 25, 2010 at 1:37 pm
Backups write log entries as does recovering the database, so that counts as a change. There's no way to force (you'd be messing with DB integrity if you could), and you can't just put the secondary back into recovery and have log backups restore. That requires a restore of a backup of the primary.
I would suggest that you get hold of the latest backup of the log shipping primary, drop the secondary and restore the backup of the primary with norecovery (or standby as applicable), then the log backups should restore without error, plus you can use the same route to restore the primary.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply