[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 repl免费云主机域名ica.
重要 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
.

相关推荐: Oracle数据库ASM磁盘组掉线，ASM实例不能mount

今天给大家分享一个Oracle数据库故障数据恢复案例，数据库故障表现为ASM磁盘组掉线，ASM实例不能mount。数据库管理员自己尝试进行简单的数据库修复后没有成功，于是联系到北京当地的数据恢复公司进行数据库的数据恢复操作。数据库数据恢复工程师首先对底层的磁盘…