|
|||||||||||||||||
|
# available only in SQL Backup 8.0 and newer SQL Backup 8 adds support for high-availability (HA) groups with the introduction of 2 new keywords: CHECK_PREFERRED_AG_REPLICA and SECONDARY_REPLICA_COPY_ONLY. CHECK_PREFERRED_AG_REPLICA When you want to back up a database only from a replica that's the preferred instance in SQL Server, you would typically use the sys.fn_hadr_backup_is_preferred_replica function e.g.
In SQL Backup 8, you can use the CHECK_PREFERRED_AG_REPLICA option to perform the same checks e.g.
Depending on your backup replica settings,
the backup will either run,
or not run and raise a message indicating that the current instance is not the preferred backup replica.
This option also works for multi-database backups. This means you no longer need to create separate backup jobs for regular databases and HA replica databases. E.g.
In the command above, the transaction log for the regular databases will be backed up normally, and transaction logs for replica databases will only be backed up if the instance is the preferred backup replica. SECONDARY_REPLICA_COPY_ONLY For a HA replica database, SQL Server allows you to perform only a copy-only full database backup e.g.
You can still do the same thing in SQL Backup, but on an instance where you have a mix of regular databases and HA replica databases, it would be tedious to create and maintain multiple jobs for each database as the regular databases would not require the COPY_ONLY option. In SQL Backup, you can use the SECONDARY_REPLICA_COPY_ONLY to automatically add the COPY_ONLY option if the database that is being backed up is a secondary replica. This is useful in multi-database backups e.g.
Regular databases will be backed up without the COPY_ONLY option, while secondary replicas will have the COPY_ONLY option added automatically. Putting it all together In a HA group, to take a full backup of all user databases on each instance, you will need to create a job on each instance and use the following command:
This will fail for secondary replicas, because they require the COPY_ONLY option. Thus, you would need to amend the above command to this:
Now you may want to restrict backups to be taken only on the preferred instances, so you need to add the CHECK_PREFERRED_AG_REPLICA option:
To back up the transaction log for all databases, your job command would look like the following:
However, this would cause transaction log backups to be taken from any secondary replica. To back up the transaction logs only if the database is the preferred replica, add the CHECK_PREFERRED_AG_REPLICA option i.e.
Document history
|
||||||||||||||