3

I have a test instance configured as always-on go down today due to a restart of the primary node and the file-share going down together. I had a bunch of problems in order to get the secondary functioning again during which I had to remove it from the AG and add it again. During this time (the time when it went down till now) the log backup was running on the primary and when I tried to apply these backups to the secondary I got the following error:

This backup set cannot be applied because it is on a recovery path that is inconsistent with the database. The recovery path is the sequence of data and log backups that have brought the database to a particular recovery point. Find a compatible backup to restore, or restore the rest of the database to match a recovery point within this backup set, which will restore the database to a different point in time. For more information about recovery paths, see SQL Server Books Online.

When I looked at sys.dm_hadr_database_replica_states:

  • last_hardened_lsn: 43000043815500001

When I looked at msdb.dbo.backupset (backup time):

  • backup time: 9:50 am
  • first_lsn: 43000043783000001
  • last_lsn : 43000043887200001

My question is, would there be any way to start applying this log backup starting from LSN 43000043815500001?

I am ok with restoring everything and then connecting my AGs since this is a very small instance, just want to know if there was an easier way as opposed to doing everything from scratch. I looked up online and did see the option for stopbeforemark or stopatmark but I am looking for something like stopaftermark.

Improve this question
6
  • Are you sure that you are attempting to restore the correct log backups to the secondary replica? Or are you writing multiple log backups to the same file (device)? – Nic Jul 19 '17 at 2:37
  • Pretty sure I am attempting to restore the correct log backup. I get the error (i forgot) saying that the log backup has the sequence older than the current hardened lsn and if I try to apply the log backup that is just after this one it tells me to apply the log backup with the lsn or something like that. I forgot the exact errors, I can get it when I get to the office tomorrw. – croxfade Jul 19 '17 at 3:09
  • Are you potentially missing a backup there somewhere? – Nic Jul 19 '17 at 13:12
  • I am absolutely not. Checked the msdb backup sets table and checked the job history and also checked the location where the backups go just to be sure. – croxfade Jul 19 '17 at 17:05
  • and the lsn's on the log backups are consistent? – Nic Jul 19 '17 at 17:13
0

You have 2 options :

  • Easy one - since this is test instance and its secondary.

    • Stop the log backup on primary.
    • Remove the db/s from AG.
    • Take a differential backup or full backup if dbs are small.
    • restore then on secondary with norecovery.
    • take an additional log backup on primary.
    • restore the additional log backup taken on secondary with norecovery.
    • join the dbs to AG.

  • A little bit harder one -

    • install sp_restoregene on primary.
    • Remove the db/s from AG.
    • Run it for the db/s in question and it will generate the full and log backup sequence with stopat.
    • Restore the sequence on secondary.
    • Join back to AG.

(I am telling above is a little bit harder since if you have not done a point-in-time recovery and you are doing it for the first time, test it first to be comfortable).

Improve this answer
-1

Your AG should be configured as automatic failover in case primary goes down. It will do the magic; there's no need to do anything like you had to do in the future.

Also you need to configure backup preferences to run on primary only.

Improve this answer

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.