I also tried COPY_ONLY Full Backup and COPY_ONLY TLog backup and restored with NORECOVERY.
I am still getting error:
mirror database has insufficient transaction log data to preserve the log backup chain of the principal database. This may happen if a log backup from the principal database has not been taken or has not been restored on the mirror database.
The error is telling you the problem - one or more transaction log backups have occurred since you started the restore process on the secondary.
One way to avoid this issue is to stop the transaction log backups on the primary during the restore process on the secondary. This way you are assured that no further transaction log backups have occurred.
There is no reason to perform COPY_ONLY backups unless you also perform differential backups as part of your normal maintenance.
Thanks Jeff for your help and guidance.
I agreed with you that error is telling clearly but when i do the fresh Backup and even though also i tried with TLog backup and restore both the backup but still throwing same error.
So i tried fresh Full backup and Full Backup plus TLog backup. Only difference is i tried with COPY_ONLY (to avoid not to break the chain as we are taking nightly full backup and every hour TLog backup) backup and restore with NORECOVERY.
Earlier i also tried with Full backup and following multiple required TLog backup.
I have also disable the TLog backup job too.
I have deleted AG group and recreated during this process to start the fresh but no luck.
From your response:
one or more transaction log backups have occurred since you started the restore process on the secondary - I am disabling TLog backup job and doing Full Copy_Only backup and restoring
One way to avoid this issue is to stop the transaction log backups on the primary during the restore process on the secondary ==> Already tried few times to disable the TLog job
There is no reason to perform COPY_ONLY backups unless you also perform differential backups as part of your normal maintenance ==> We are doing hourly TLog Backup so don't want to break the LSN chain
The LSN chain is not dependent on a full backup - the only thing affected by the full backup is a differential backup which is dependent on the latest full backup.
The only way that error occurs is if you have not restored all available transaction log backups up to the current point in time since the full backup you restored to the secondary. It is highly likely that either you are not restoring all available transaction log backups - or some other process is performing additional transaction log backups that you are not aware of...
check the backup history in MSDB to see if there are any other processes performing backups.
Thanks once again Jeff.
I am completely agreed with you and i will check again after my regular Sunday Full backup.
But biggest questions is that We have Daily Full backup and hourly TLog backup, so i am trying to avoid all this puzzle of missing Tlog, I have shutdown the Tlog backup and taken Full backup and restored into secondary still complaining. I have also tried both Full backup and Tlog backup after that i have disable the Tlog backup and then restored it still same issue.
most of the time i am deleting database on secondary and restoring after that too.
This seems to indicate a problem with your backups...
Can you confirm the schedule of full, differential and transaction log backups for this system?
We are not taking differential backup but you are asking to take fresh, diff and TLog backup, right?
That will be COPY_ONLY backup right?
Because we are only taking Nightly Full backup and Every hour TLog backup and which are running fine.
No - that is not what I am recommending.
If you are performing a nightly full backup - and hourly transaction log backups then you don't need to worry about COPY_ONLY backups at all. Again - COPY_ONLY is used to maintain the differential chain so that a differential backup taken after the COPY_ONLY backup will still tie to the normal full backup.
In your situation - one or more transaction log backups have taken place between the time you took your full backup and restored it to the secondary. In order to join the new database to the availability group you must restore all transaction log backups from the full backup through current point in time - if you miss one then you will get the error you are receiving.
So - either some other process is also backing up the transaction log or you do not have a full log chain and are missing one or more transaction log backups.
Thanks Jeff for your prompt response. It worked Finally, I was able to Restore Nightly backup and all TLog backup.
I was using Point in Time Recovery so i was using STOP AT but i removed it and it got restored and able to join the database.
Now both database (Primary and Secondary) shows Synchronized.
I also failed over and Secondary become Primary. I have also failed over again so now Primary DB is Primary back to and Secondary is Secondary back to.
During my BAG set up, I have selected PRIMARY as backup so it will be pk, right?
On my Primary and Secondary Instance Replica, next to Database shows Synchronized lo that's the normal right?
Like:
SERVER1
DB1 (Synchronized)
SERVER2
DB1 (Synchronized)
My regular nightly backup for Primary DB will run as it is, right? or it will effect because of Synchronized?
What's the best way to check secondary that up to date with primary or not?
Any other things i have to set up or check as part of post configurations?
I haven't set up Listener so i t will be ok, right?
I have Shared folder set up.
Thanks for your kind support and help to resolve the issue and for better guidance.
Availability group dashboard will shows you the health of your AG.
Thanks Ahmed for all you help which helps me to move forward.
Right now my database shows Synchronized in both the server next to database name, this is normal or Primary shouldn't show Synchronized and just normal like other database name only?
The reason, i am asking because in Primary, My regular Database backup didn't happened and this was happened earlier too as soon as i removed Availability group and it started to show normal not Synchronized and database nightly backup works fine so i am just curious what i have to do for any AG Group database Primary DB backup work?
Thanks for your help!
Synchronized means everything is up to date - synchronizing would tell you that it is catching up and may or may not be an issue.
To answer some of your questions:
Primary as backup is ok - unless you license the secondary node then performing backups on that node is not supported and I don't believe a BAG would allow that anyways.
The backups will not affect the status - however, other maintenance can and will have an impact. For example, rebuilding an index will need to be transferred to the secondary and could cause the send queue and redo queues to back up. This is normal but something you should be aware of...
The easiest way to check is to look at the dashboard (you have to add columns to the report to see the send queue, redo queue and other related fields). The report would identify any issues with any databases in the availability group.
A listener is not required - but the listener allows your applications to connect to the node currently hosting the database. Without a listener you have to modify your connections when you fail over.
As for your issue with the backup - that depends on how you have implemented them. Agent jobs do not automatically get created or moved to the secondary, so if the database is on the secondary node those agent jobs on the primary node will not execute successfully.
Thanks Jeff.
Somehow my Primary database didn't get back up (Full backup - nightly job) and that also happened previously.
I didn't see any error or message into log.
Last time when i deleted AG group then started to take the FULL backup.
TLog backup running fine that we are using via Sql Agent job.
Full nightly backup we are using 3rd party tool.
Any idea?
Thanks for your time and great help!
What is the preferred replica for backup?
why are you using 2 different ways to backup full and logs?
I would suggest perform both Full and Log backups from either SQL agent or your third party tool.
Thanks Ahmed.
We have set up Primary Replica for backup while i was setting up AG.
I am new here, we have set up this way to take FULL backup through 3rd party backup software and TLog backup we set up through Ola script using Sql Agent.
Only issue when the database is in Synchronized state for Primary then i think it's not taking backup.
You need to follow up with the 3rd party vendor to find out why they are not taking backups when the database is in an availability group. This has nothing to do with SQL Server - which doesn't have a problem running backups on either the primary or secondary.
Jeff,
Ok, Thanks for your prompt response.
I am doing 1st time so just checking if anything with sql related.
Do i have to check with FileSahre Qurom my OPs team configured during Cluster configuration?
I would verify the cluster configuration myself - but that really depends on how well your system team understands setting up and configuring windows clusters. As long as you have enough quorum votes for the cluster then I would not worry about it.
Ok, Thanks
Thanks everyone for your help!