檢視或設定 backup compression default 伺服器組態選項View or Configure the backup compression default Server Configuration Option

適用於: 是SQL Server 否Azure SQL Database 否Azure SQL 資料倉儲 否平行處理資料倉儲 APPLIES TO: yesSQL Server noAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

本主題描述如何使用 SQL Server Management StudioSQL Server Management StudioTransact-SQLTransact-SQL,在 SQL Server 2017SQL Server 2017 中檢視或設定 [備份壓縮預設] 伺服器組態選項。This topic describes how to view or configure the backup compression default server configuration option in SQL Server 2017SQL Server 2017 by using SQL Server Management StudioSQL Server Management Studio or Transact-SQLTransact-SQL. backup compression default 選項決定伺服器執行個體根據預設是否會建立壓縮備份。The backup compression default option determines whether the server instance creates compressed backups by default. 安裝 SQL ServerSQL Server 時, ssCurrent 選項已關閉。When SQL ServerSQL Server is installed, the backup compression default option is off.

本主題內容In This Topic

開始之前Before You Begin

限制事項Limitations and Restrictions

建議Recommendations

  • 當您建立個別備份、設定記錄傳送組態或建立維護計畫時,可以覆寫伺服器層級的預設值。When you are creating an individual backup, configuring a log shipping configuration, or creating a maintenance plan, you can override the server-level default.

  • 備份壓縮同時支援磁碟備份裝置和磁帶備份裝置。Backup compression is supported for both disk backup devices and tape backup devices.

安全性Security

權限Permissions

不含參數或只含第一個參數之 sp_configure 上的執行權限預設會授與所有使用者。Execute permissions on sp_configure with no parameters or with only the first parameter are granted to all users by default. 以同時設定兩個參數的 sp_configure 來變更組態選項或執行 RECONFIGURE 陳述式時,使用者必須取得 ALTER SETTINGS 伺服器層級權限。To execute sp_configure with both parameters to change a configuration option or to run the RECONFIGURE statement, a user must be granted the ALTER SETTINGS server-level permission. 系統管理員 (sysadmin)serveradmin 固定伺服器角色會隱含 ALTER SETTINGS 權限。The ALTER SETTINGS permission is implicitly held by the sysadmin and serveradmin fixed server roles.

使用 SQL Server Management StudioUsing SQL Server Management Studio

檢視或設定 backup compression default 選項To view or configure the backup compression default option

  1. 在物件總管中,請以滑鼠右鍵按一下伺服器,然後選取 [屬性] 。In Object Explorer, right-click a server and select Properties.

  2. 按一下 [資料庫設定] 節點。Click the Database settings node.

  3. [備份與還原] 底下, [壓縮備份] 會顯示 backup compression default 選項的目前設定。Under Backup and restore, Compress backup shows the current setting of the backup compression default option. 這項設定會決定壓縮備份的伺服器層級預設值,如下所示:This setting determines the server-level default for compressing backups, as follows:

    • 如果 [壓縮備份] 方塊是空的,新備份預設不會進行壓縮。If the Compress backup box is blank, new backups are uncompressed by default.

    • 如果 [壓縮備份] 方塊已核取,新備份預設就會進行壓縮。If the Compress backup box is checked, new backups are compressed by default.

    如果您是 sysadminserveradmin 固定伺服器角色的成員,也可以透過按一下 [壓縮備份] 方塊,變更預設值。If you are a member of the sysadmin or serveradmin fixed server role, you can also change the default setting by clicking the Compress backup box.

使用 Transact-SQLUsing Transact-SQL

檢視 backup compression default 選項To view the backup compression default option

  1. 連接到 Database EngineDatabase EngineConnect to the Database EngineDatabase Engine.

  2. 在標準列中,按一下 [新增查詢]From the Standard bar, click New Query.

  3. 複製下列範例並將其貼到查詢視窗中,然後按一下 [執行]Copy and paste the following example into the query window and click Execute. 這個範例會查詢 sys.configurations 目錄檢視以判斷 backup compression default的值。This example queries the sys.configurations catalog view to determine the value for backup compression default. 值為 0 表示備份壓縮已關閉,值為 1 表示備份壓縮已啟用。A value of 0 means that backup compression is off, and a value of 1 means that backup compression is enabled.

SELECT value   
FROM sys.configurations   
WHERE name = 'backup compression default' ;  
GO  

設定 backup compression default 選項To configure the backup compression default option

  1. 連接到 Database EngineDatabase EngineConnect to the Database EngineDatabase Engine.

  2. 在標準列中,按一下 [新增查詢]From the Standard bar, click New Query.

  3. 複製下列範例並將其貼到查詢視窗中,然後按一下 [執行]Copy and paste the following example into the query window and click Execute. 這個範例示範如何使用 sp_configure ,將伺服器執行個體設定為依預設會建立壓縮備份。This example shows how to use sp_configure to configure the server instance to create compressed backups by default.

EXEC sp_configure 'backup compression default', 1 ;  
RECONFIGURE;  
GO 

如需詳細資訊,請參閱 伺服器設定選項 (SQL Server)伺服器組態選項。For more information, see Server Configuration Options (SQL Server).

後續操作:設定 backup compression default 選項之後Follow Up: After you configure the backup compression default option

設定會立即生效,不需要重新啟動伺服器。The setting takes effect immediately without restarting the server.

另請參閱See Also

BACKUP (Transact-SQL) BACKUP (Transact-SQL)
伺服器組態選項 (SQL Server) Server Configuration Options (SQL Server)
RECONFIGURE (Transact-SQL) RECONFIGURE (Transact-SQL)
sp_configure (Transact-SQL) sp_configure (Transact-SQL)
備份概觀 (SQL Server)Backup Overview (SQL Server)