[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 .