0

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

transaction backup old error

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?

2
  • Normally you just keep restoring the log backups in the chain. The ones that are too old will give you this error. – David Browne - Microsoft Apr 17 '20 at 13:49
  • It would've been nice if I could select say 30 .trn files and SSMS UI showed me which ones are eligible. I can only do one file at a time from the UI. I have to first find the correct file manually by trial and error, then prepare scripts by copy pasting all the file names to each query. It seems so tedious. Isn't there a way to select multiple .trn files in one go and have SSMS manage the order of execution since it already knows which one is correct based on LSN? – Prashanth Subramanian Apr 17 '20 at 14:23
3

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'
2
  • This is great, is there an easy way to find the LSN on the backup file without a manual trial and error? – Prashanth Subramanian Apr 17 '20 at 14:29
  • Yes, you will need to run restore "headerOnly" and you will get the first and last LSN in this backup... Try : Restore headeronly from disk=N'your log backup path' – Dominique Boucher Apr 17 '20 at 15:22
0

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;

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.