[AlwaysOn] 创建SQL Server高可用性组T-SQL语法：AUTOMATED_BACKUP_PREFERENCE参数

AUTOMATED_BACKUP_PREFERENCE = { PRIMARY | SECONDARY_ONLY| SECONDARY | NONE }

指定在选择执行备份的位置时，备份作业应如何评估主副本的首选项。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 take the automated backup preference into account. 重要的是要了解SQL Server没有强制执行该首选项，因此它不会影响即席备份。It is important to understand that the preference is not enforced by sql Server, so it has no impact on ad-hoc backups.

该选项支持的值如下：The supported values are as follows:

主副本 PRIMARY

指定备份应始终发生在主副本上。Specifies that the backups should always occur on the primary replica. 如果需要备份功能(如创建差异备份)，则此选项非常有用，在辅助副本上运行备份时，这些功能不受支持。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

如果计划使用日志传送为可用性组准备任何辅助数据库，请将自动备份首选项设置为“主”，直到所有辅助数据库都已准备好并加入可用性组。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_ONLY

指定不应在主副本上执行备份。Specifies that backups should never be performed on the primary replica. 如果主副本是唯一联机的副本，则不应进行备份。If the primary replica is the only replica online, the backup should not occur.

辅助副本 SECONDARY

指定备份应发生在辅助副本上，除非主副本是唯一联机副本。Specifies that backups should occur on a secondary replica except when the primary replica is the only replica online. 在这种情况下，备份应该发生在主副本上。In that case, the backup should occur on the primary replica. 这是默认行为。This is the default behavior.

无 NONE

指定在选择要执行备份的副本时，您希望备份作业忽略可用性副本的角色。Specifies that you prefer that backup jobs ignore the role of the availability replicas when choosing the replica to perform backups. 注意：备份作业可能会评估其他因素，例如每个可用性副本的备份优先级及其操作状态和连接状态。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

没有强制执行“自动备份”首选项设置。There is no enforcement of the AUTOMATED_BACKUP_PREFERENCE setting. 此首选项的解释取决于为给定可用性组中的数据库编写后台作业脚本的逻辑(如果有)。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. 自动备份首选项设置对即席备份没有影响。The automated backup preference setting has no impact on ad-hoc backups. 有关详细信息，请参阅配置可用性备份副本(SQL Server)。For more information, see Configure Backup on Availability Replicas (SQL Server).

注释 Note

要查看现有可用性组的自动备份首选项，请选择sys.availability_groups目录视图的 automated_backup_preference或 automated_backup_preference_desc列。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. 此外， sys.fn_hadr_backup_is_preferred_replica(Transact-SQL)可用于确定首选备份副本。Additionally, sys.fn_hadr_backup_is_preferred_replica (Transact-SQL) can be used to determine the preferred backup replica. 此函数至少为一个副本返回1，即使在automated_backup_preference=none时也是如此。This function returns 1 for at least one of the replicas, even when AUTOMATED_BACKUP_PREFERENCE = NONE .