Shulou(Shulou.com)06/01 Report--

AUTOMATED_BACKUP_PREFERENCE = {PRIMARY | SECONDARY_ONLY | SECONDARY | NONE}

Specifies how the backup job should evaluate the preferences for the primary copy when selecting the location to perform the backup. Specifies a preference about how a backup job should evaluate the primary replica when choosing where to perform backups. You can script a given backup job to consider automatic backup preferences. You can script a given backup job to take the automated backup preference into account. It is important to understand that SQL Server does not enforce this preference, so it does not affect ad hoc backups. It is important to understand that the preference is not enforced by SQL Server, so it has no impact on ad-hoc backups.

The following values are supported for this option: The supported values are as follows:

Master copy PRIMARY

Specifies that the backup should always occur on the master copy. Specifies that the backups should always occur on the primary replica. This option is useful if backup features are required, such as creating differential backups, and are not supported when running backups on secondary copies. This option is useful if you need backup features, such as creating differential backups, that are not supported when backup is run on a secondary replica.

Important Important

If you plan to use log shipping to prepare any secondary databases for the availability group, set the automatic backup preference to Primary until all secondary databases are ready and join the availability group. If you plan to use log shipping to prepare any secondary databases for an availability group, set the automated backup preference to Primary until all the secondary databases have been prepared and joined to the availability group.

Secondary copy SECONDARY_ONLY only

Specifies that backups should not be performed on the primary copy. Specifies that backups should never be performed on the primary replica. If the master copy is the only online copy, you should not make a backup. If the primary replica is the only replica online, the backup should not occur.

Secondary copy SECONDARY

Specifies that the backup should occur on the secondary copy unless the primary copy is the only online copy. Specifies that backups should occur on a secondary replica except when the primary replica is the only replica online. In this case, the backup should occur on the master copy. In that case, the backup should occur on the primary replica. This is the default behavior. This is the default behavior.

No NONE

Specifies the role that you want the backup job to ignore the availability copy when selecting the copy to perform the backup. Specifies that you prefer that backup jobs ignore the role of the availability replicas when choosing the replica to perform backups. Note: backup jobs may evaluate other factors, such as the backup priority of each available copy and its operational and connection status. Note backup jobs might evaluate other factors such as backup priority of each availability replica in combination with its operational state and connected state.

Important Important

The automatic backup preference setting is not enforced. There is no enforcement of the AUTOMATED_BACKUP_PREFERENCE setting. The interpretation of this preference depends on the logic, if any, of writing background job scripts for databases in a given availability group. The interpretation of this preference depends on the logic, if any, that you script into back jobs for the databases in a given availability group. Automatic backup preference settings have no effect on ad hoc backups. The automated backup preference setting has no impact on ad-hoc backups. "for more information, see configuring availability backup copies (SQL Server)." For more information, see Configure Backup on Availability Replicas (SQL Server).

Comment Note

To view automatic backup preferences for existing availability groups, select the automated_backup_preference or automated_backup_preference_desc column of the sys.availability_groups catalog view. To view the automated backup preference of an existing availability group, select the automated_backup_preference or automated_backup_preference_desc column of the sys.availability_groups catalog view. In addition, sys.fn_hadr_backup_is_preferred_replica (Transact-SQL) can be used to determine the preferred backup copy. Additionally, sys.fn_hadr_backup_is_preferred_replica (Transact-SQL) can be used to determine the preferred backup replica. This function returns 1 for at least one copy, even in automated_backup_preference=none. This function returns 1 for at least one of the replicas, even when AUTOMATED_BACKUP_PREFERENCE = NONE.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.