How to find the current Log Sequence Number (LSN) of a database that is in Restoring state?

Question

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?

Answer 1

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.

Select redo_start_lsn from sys.master_files where database_id=DB_ID('yourDB') and type_desc = 'LOG'

Answer 2

With this query you can run the RESTORE Statements - HEADERONLY for all backups of a database at once:

DECLARE @location nvarchar(520);
DECLARE backup_location CURSOR  
    FOR SELECT DISTINCT
            bmf.physical_device_name
        FROM sys.master_files mf
            INNER JOIN msdb.dbo.backupfile bf
            ON mf.file_guid = BF.file_guid
            INNER JOIN msdb.dbo.backupmediafamily bmf
            ON bmf.media_set_id = bf.backup_set_id
        WHERE database_id = DB_ID('YourDatabaseName');

OPEN backup_location;  
FETCH NEXT FROM backup_location INTO @location;

WHILE @@FETCH_STATUS = 0  
    BEGIN  
        SELECT @location;
        RESTORE HEADERONLY FROM disk = @location;
        FETCH NEXT FROM backup_location INTO @location; 
    END  

CLOSE backup_location;  
DEALLOCATE backup_location;