Restoring secondary backup files on primary server in sql server

Question

I want to restore log backup file on copy-only full backup in restoring mode on primary replica server these backups were taken from secondary replica . I took copy only full back up on secondary replica then failover after that I took .trn backup file on primary replica(which took full backup ). both full and trn file backup from one server but full backup took when server in secondary mode and trn backup took when server be in primary backup and I want to restored both of them in restoring mode on secondary replica after failover. I wanted to add database in cluster with my hand because our databases are big

it was error when they are restored on primary server the error is

The log in this backup set begins at LSN 38202000004450500001, which is too recent to apply to the database. An earlier log backup that includes LSN 38167000000015500001 can be restored.

Answer 1

On the server you took backup (full and log) you can execute below query to trace backup chain.

Database_backup_lsn is the lsn of database to which log backup belong to.

First_lsn must mach with last_lsn.

If you are doing backup using SQL, in the physical_device_name you will find the patch to the backup file. Maybe you have additional backup job and you have two location of backup file.

SELECT   
   database_name
   ,name
   ,backup_size
   ,first_lsn
   ,last_lsn
   ,checkpoint_lsn
   ,database_backup_lsn
   ,backup_start_date
   ,backup_finish_date
   ,msdb.dbo.backupmediafamily.physical_device_name
FROM msdb.dbo.backupset
INNER JOIN msdb.dbo.backupmediafamily ON msdb.dbo.backupset.media_set_id = msdb.dbo.backupmediafamily.media_set_id

Answer 2

Reviewing the error message you're receiving...

The log in this backup set begins at LSN 38202000004450500001, which is too recent to apply to the database. An earlier log backup that includes LSN 38167000000015500001 can be restored.

This indicates there is an earlier log backup that was taken prior to the log backup you're attempting to restore. SQL Server is expecting LSN 38167000000015500001. However, you're attempting to restore 38202000004450500001. This log backup contains a higher LSN than what SQL Server is expecting. Log backups are sequential, and you must restore all of them in the order they were taken. If you're missing any log backup in the set, the last contiguous log backup you're able to successfully restore becomes the most current point in time you can restore to.

Additionally, if you're taking log backups on more than one replica in the Availability Group, be aware that log backup chains are persisted across all replicas in the Availability group. In this situation, you'll need to gather all log backups taken on all replicas since your full backup, and restore them in the proper order.

The most common issue I see with in your scenario is where someone takes a manual log backup in the attempt to restore to a secondary replica, but the leave their log backup job running. Then the job takes a log backup just before or after the one they took manually.

The other issue you may be running into is that you're taking log backups on more than one replica at the same time.

Only you would know where all of your log backups were taken, or how often they are taken. Try disabling any log backup jobs while you're trying to seed a secondary replica. Then take a full backup and a log backup, and see if you run into the same error.