设置备份的过期日期 (SQL Server)Set the Expiration Date on a Backup (SQL Server)

适用对象:是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 ServerSQL Server Management StudioSQL Server Management StudioTransact-SQLTransact-SQL中设置备份的过期日期。This topic describes how to set the expiration date on a backup in SQL ServerSQL Server by using SQL Server Management StudioSQL Server Management Studio or Transact-SQLTransact-SQL.

本主题内容In This Topic

开始之前Before You Begin

SecuritySecurity

权限Permissions

默认情况下,为 sysadmin 固定服务器角色以及 db_ownerdb_backupoperator 固定数据库角色的成员授予 BACKUP DATABASE 和 BACKUP LOG 权限。BACKUP DATABASE and BACKUP LOG permissions default to members of the sysadmin fixed server role and the db_owner and db_backupoperator fixed database roles.

备份设备的物理文件的所有权和权限问题可能会妨碍备份操作。Ownership and permission problems on the backup device's physical file can interfere with a backup operation. SQL ServerSQL Server 必须能够读取和写入设备;运行 SQL ServerSQL Server 服务的帐户必须具有写入权限。must be able to read and write to the device; the account under which the SQL ServerSQL Server service runs must have write permissions. 但是,用于在系统表中为备份设备添加项目的 sp_addumpdevice不检查文件访问权限。However, sp_addumpdevice, which adds an entry for a backup device in the system tables, does not check file access permissions. 备份设备物理文件的这些问题可能直到为备份或还原而访问物理资源时才会出现。Such problems on the backup device's physical file may not appear until the physical resource is accessed when the backup or restore is attempted.

使用 SQL Server Management StudioUsing SQL Server Management Studio

设置备份的过期日期To set the expiration date on a backup

  1. 连接到相应的 MicrosoftMicrosoft SQL Server 数据库引擎SQL Server Database Engine实例之后,在对象资源管理器中,单击服务器名称以展开服务器树。After connecting to the appropriate instance of the MicrosoftMicrosoft SQL Server 数据库引擎SQL Server Database Engine, in Object Explorer, click the server name to expand the server tree.

  2. 展开 “数据库” ,然后根据数据库的不同,选择用户数据库,或展开 “系统数据库” ,再选择系统数据库。Expand Databases, and, depending on the database, either select a user database or expand System Databases and select a system database.

  3. 右键单击数据库,指向“任务” ,再单击“备份” 。Right-click the database, point to Tasks, and then click Back Up. 将出现 “备份数据库” 对话框。The Back Up Database dialog box appears.

  4. “常规” 页上,为 “备份集过期时间” 指定一个过期日期以指明其他备份可以覆盖该备份集的时间:On the General page, for Backup set will expire, specify an expiration date to indicate when the backup set can be overwritten by another backup:

    • 若要使备份集在特定天数后过期,请单击“之后” (默认选项),并输入备份集从创建到过期所需的天数。To have the backup set expire after a specific number of days, click After (the default option), and enter the number of days after set creation that the set will expire. 此值范围为 0 到 99999 天;0 天表示备份集将永不过期。This value can be from 0 to 99999 days; a value of 0 days means that the backup set will never expire.

      默认值在 “服务器属性” 对话框(位于 “数据库设置” 页上)的 “默认备份媒体保持期(天)” 选项中设置。The default value is set in the Default backup media retention (in days) option of the Server Properties dialog box (Database Settings page). 若要访问它,请在对象资源管理器中右键单击服务器名称,选择属性,再选择“数据库设置” 页。To access this, right-click the server name in Object Explorer and select properties; then select the Database Settings page.

    • 若要使备份集在特定日期过期,请单击 “在” ,并输入备份集的过期日期。To have the backup set expire on a specific date, click On, and enter the date on which the set will expire.

使用 Transact-SQLUsing Transact-SQL

设置备份的过期日期To set the expiration date on a backup

  1. 连接到 数据库引擎Database EngineConnect to the 数据库引擎Database Engine.

  2. 在标准菜单栏上,单击 “新建查询”From the Standard bar, click New Query.

  3. BACKUP 语句中,指定 EXPIREDATE 或 RETAINDAYS 选项以便确定 SQL Server 数据库引擎SQL Server Database Engine 何时可以覆盖备份。In the BACKUP statement, specify either the EXPIREDATE or RETAINDAYS option to determine when the SQL Server 数据库引擎SQL Server Database Engine can overwrite the backup. 如果这两个选项均未指定,则过期日期由 介质保持期 服务器配置设置确定。If neither option is specified, the expiration date is determined by the media retention server configuration setting. 下面的示例使用 EXPIREDATE 选项指定过期日期为 2015 年 6 月 30 日 (6/30/2015)。This example uses the EXPIREDATE option to specify an expiration date of June 30, 2015 (6/30/2015).

USE AdventureWorks2012;  
GO  
BACKUP DATABASE AdventureWorks2012  
 TO DISK = 'Z:\SQLServerBackups\AdventureWorks2012.Bak'  
   WITH EXPIREDATE = '6/30/2015' ;  
GO  

另请参阅See Also

创建完整数据库备份 (SQL Server) Create a Full Database Backup (SQL Server)
备份文件和文件组 (SQL Server) Back Up Files and Filegroups (SQL Server)
备份事务日志 (SQL Server) Back Up a Transaction Log (SQL Server)
创建差异数据库备份 (SQL Server)Create a Differential Database Backup (SQL Server)