查看或配置 backup compression default 服务器配置选项View or Configure the backup compression default Server Configuration Option

适用对象:是SQL Server 否Azure SQL 数据库 否Azure Synapse Analytics (SQL DW) 否并行数据仓库APPLIES TO: yesSQL Server noAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

本主题说明如何使用 SQL Server 2019SQL Server 2019 中查看或配置 SQL Server Management StudioSQL Server Management Studio backup compression default Transact-SQLTransact-SQL服务器配置选项。This topic describes how to view or configure the backup compression default server configuration option in SQL Server 2019SQL Server 2019 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 时, backup compression default 选项关闭。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.

SecuritySecurity

权限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. ALTER SETTINGS 权限由 sysadminserveradmin 固定服务器角色隐式持有。The ALTER SETTINGS permission is implicitly held by the sysadmin and serveradmin fixed server roles.

使用 SQL Server Management StudioUsing SQL Server Management Studio

查看或配置备份压缩默认值选项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 EngineConnect to the 数据库引擎Database 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 EngineConnect to the 数据库引擎Database 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)