Sql-server – Is it Possible to perform Restore on Secondary Replica using Copy_Only Backup that was taken on the same node

sql server

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 "AGTest" 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 "AGTest" 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.

Best Answer

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 "AGTest" 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).

Related Solutions

Sql-server – Database mirroring, “WITH NORECOVERY”

Create endpoints on all servers:

CREATE ENDPOINT EndPointName 
STATE=STARTED AS TCP(LISTENER_PORT = PortNumber, LISTENER_IP = ALL) 
FOR DATA_MIRRORING(ROLE = PARTNER, AUTHENTICATION = WINDOWS NEGOTIATE, 
                   ENCRYPTION = REQUIRED ALGORITHM RC4)

For a witness server, change ROLE = PARTNER to ROLE = WITNESS in the witness's endpoint.

Run the first SET PARTNER command on the mirrored server:

ALTER DATABASE DatabaseName SET PARTNER = N'TCP://PrincipalServer:PortNumber'

Notice that it is pointing to the principal server in the command, but you are running it on the mirror.

Then run the next two commands on the principal server, pointing the SET PARTNER at the mirror:

ALTER DATABASE DatabaseName SET PARTNER = N'TCP://MirrorServer:PortNumber'
ALTER DATABASE DatabaseName SET SAFETY OFF --OFF=async, FULL=sync

If you are setting up a witness for automatic failovers, use SET SAFETY FULL above instead of SET SAFETY OFF and then run this command on the principal:

ALTER DATABASE DatabaseName SET WITNESS = N'TCP://WitnessServer:PortNumber'

Sql-server – SQL Server High Availability restore copy_only backups from secondary replica problem

First things first, get rid of the differential backup. If you are performing full, copy only backups off of a secondary these will do nothing for you.

The differential backup works to backup changes from a full backup (not copy only). When a normal full backup runs it sets all of the pages in the database as being backed up. Going forward from that point any change to a page gets marked, and then the differential would come along and backup any page marked as changed. As time goes along the differential will get larger and larger. A copy only backup does not reset those flags, so things will just get worse.

At this point decide how you want to proceed with your backup strategy. If you want to continue to take differential backups, then also take full backups on your primary. If you no longer want to take the differentials for the database that are in the AG then set the copy_only flag to N in the Ola script. This will allow the differential job to run and take backups of databases not in the AG, but will ignore those that are in the AG (so long as the backup preference marks a secondary replica as the machine to backup from).

As regards the benefit of offloading backups to a secondary, this can help reduce resource contention on your primary replica, which is where most of your traffic can be. It can also be useful as a way to take database backups in a DR site, where you may have a secondary replica or two.

Best Answer

Related Solutions

Sql-server – Database mirroring, “WITH NORECOVERY”

Sql-server – SQL Server High Availability restore copy_only backups from secondary replica problem

Related Question