I removed one of my databases from the availability group on my secondary replica in my AlwaysOn group for an experiment. I want to add it back, but I cannot do so because the databases needs to be rolled forward to match what is on the primary (there was a transaction log backup taken on the primary, I guess that clears the log on the primary and that makes it impossible to join the database back to the availability group on the secondary without restoring from the transaction log).
The database is now in the "Restoring" state. I have the transaction backup files on the Primary replica and I can copy over the files needed to perform a transaction backup restore on the secondary to roll it forward. I tried a couple of files and I get the error below
I understand that this means my log back up is older than the current state of the database. The current state of the database seems to have the last LSN to be the second number in the error message.
Question: How can I query the database to find out the second number LSN 956952100000057? Is this error message the only place where I can see it and Follow up: Is trial and error of the only way of figuring out which log backup file the correct one?
Best Answer
If you restore the log backup with "HeaderOnly", you will have the first and last LSN include in this backup.
You want to find the log backup that includes the LSN mentioned in your error message (where the DB is at).
Now if you want to find this LSN, you will have to query the sys.master_files DMV and look at the redo_start_lsn column.