配置 media retention 服务器配置选项Configure the media retention 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 ServerSQL Server 中配置 SQL Server Management StudioSQL Server Management Studio media retention Transact-SQLTransact-SQL服务器配置选项。This topic describes how to configure the media retention server configuration option in SQL ServerSQL Server by using SQL Server Management StudioSQL Server Management Studio or Transact-SQLTransact-SQL. media retention 选项指定保留每个备份集的时间长度。The media retention option specifies the length of time to retain each backup set. 此选项可以防止在指定的天数前覆盖备份。The option helps protect backups from being overwritten until the specified number of days has elapsed. 配置了 media retention 选项后,无需在每次进行备份时都指定系统备份的保持时间。After you configure media retention option, you do not have to specify the length of time to retain system backups each time you perform a backup. 默认值为 0 天,最大值为 365 天。The default value is 0 days, and the maximum value is 365 days.

本主题内容In This Topic

开始之前Before You Begin

限制和局限Limitations and Restrictions

  • 如果未等设定的天数过去即使用备份介质, SQL ServerSQL Server 将会发出警告消息。If you use the backup medium before the set number of days has passed, SQL ServerSQL Server issues a warning message. SQL ServerSQL Server 不会发出警告。does not issue a warning unless you change the default.

建议Recommendations

  • 此选项是一个高级选项,仅应由有经验的数据库管理员或认证的 SQL ServerSQL Server 专业人员更改。This option is an advanced option and should be changed only by an experienced database administrator or certified SQL ServerSQL Server professional.

  • media retention 选项可以使用 BACKUP 语句的 RETAINDAYS 子句覆盖。The media retention option can be overridden by using the RETAINDAYS clause of the BACKUP statement.

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

配置 media retention 选项To configure the media retention option

  1. 在对象资源管理器中,右键单击服务器并选择 “属性”In Object Explorer, right-click a server and select Properties.

  2. 单击 “数据库设置” 节点。Click the Database settings node.

  3. “备份/还原” 下的 “默认备份媒体保持期” 框中,键入或选择一个 0 到 365 之间的值,以设置备份媒体在数据库或事务日志备份后保留的天数。Under Backup/Restore, in the Default backup media retention box, type or select a value from 0 through 365 to set the number of days the backup medium will be retained after a database or transaction log backup.

使用 Transact-SQLUsing Transact-SQL

配置 media retention 选项To configure the media retention 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_configuremedia retention 选项的值设置为 60 天。This example shows how to use sp_configure to set the value of the media retention option to 60 days.

USE AdventureWorks2012 ;  
GO  
EXEC sp_configure 'show advanced options', 1;  
GO  
RECONFIGURE ;  
GO  
EXEC sp_configure 'media retention', 60 ;  
GO  
RECONFIGURE;  
GO  
  

有关详细信息,请参阅 服务器配置选项 (SQL Server)版本的组合自动配置的最大工作线程数。For more information, see Server Configuration Options (SQL Server).

跟进:在配置介质保留期选项之后Follow Up: After you configure the media retention option

该设置将立即生效,无需重新启动服务器。The setting takes effect immediately without restarting the server.

另请参阅See Also

SQL Server 数据库的备份和还原 Back Up and Restore of SQL Server Databases
BACKUP (Transact-SQL) BACKUP (Transact-SQL)
RECONFIGURE (Transact-SQL) RECONFIGURE (Transact-SQL)
服务器配置选项 (SQL Server) Server Configuration Options (SQL Server)
sp_configure (Transact-SQL)sp_configure (Transact-SQL)