Is it Possible to perform Restore on Secondary Replica using Copy_Only Backup that was taken on the same node? I'm looking to use copy_only backups taken on a secondary replica, remove that DB from the Availability Group, then restore the DB using the copy_only backup and add it back into the Availability Group. That all works fine but when I try to join the DB back to the Availability Group I get an error.
'Database &quot;AGTest&quot; is not in a recovering state which is required for a mirror database or secondary database. The remote database must be restored using WITH NORECOVERY.'
This seems like a straightforward error so I do what it says, Perform the same restore but with NORECOVERY and try to add to AG again. Another Error.
'The remote copy of database &quot;AGTest&quot; has not been rolled forward to a point in time that is encompassed in the local copy of the database log.'
This is the part that confuses me, I've tried to restore it with the latest log backups from the primary. No Dice.
Any help or suggestions is greatly appreciated.
When trying to add a secondary replica to an AG, it needs to be sufficiently close to the primary that whatever transactions are in the active portion of the primary's transaction log are enough to bring the secondary up to current.
Let's say for the sake of argument that your copy only backup is taken once a day at midnight (00:00) and you're starting the process of adding the replica the next day at 09:00 (i.e. nine hours have elapsed since the backup was taken). Let's say for the sake of argument that the restore process was instantaneous (it won't be, but we'll get to that in a moment). Once that restore is complete, you have a database that is consistent with the primary as it was at 00:00. You now need to apply transaction log backups in order to cover the intervening nine hours. Let's say for the sake of argument that those also take no time to restore. Now the primary and secondary are consistent with each other and you should be able to join the secondary replica to the AG.
In the above scenario, all of the restores took zero time. In reality, that's not the case. As you're doing restores on the secondary, your primary database is also taking traffic. It's not a big deal, you just need to apply more transaction log to cover the intervening time. Making the above scenario slightly more realistic, let's say that the restore of the full backup takes 2h. So now it's 11:00 and you start restoring all the transaction logs you have from midnight onward. Let's say that that process takes an hour. Now it's 12:00 and your secondary is restored up to 11:00. You kick off another round of transaction log restores to cover 11:00-12:00. Et cetera until your secondary is sufficiently close to the primary that it can make up the difference with the active portion of the log.
How will you know? Two things. First, if you try to add the secondary and it's not close enough, you'll get the error message you got in re: The remote copy of database &quot;AGTest&quot; has not been rolled forward to a point in time that is encompassed in the local copy of the database log.'. Second, if there are any t-log backups of the primary that you haven't restored to the secondary, you'll need to restore them.
Clearing up one last thing - the reason you need to do all of the above restores (not just the full backup, the t-log backups as well) using the with norecovery clause is that that's what allows more restores to be done on top of the resulting database. That clause does exactly what it says - it skips the portion of the restore process that runs through so-called crash recovery (which is the process that does roll forwards and roll backs of transactions so that the resulting database is transactionally consistent with the backup).