設定備份的到期日 (SQL Server)Set the Expiration Date on a Backup (SQL Server)

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

此主題描述如何使用 SQL Server 2017SQL Server 2017SQL Server Management StudioSQL Server Management Studio ,在 Transact-SQLTransact-SQL中設定備份的到期日。This topic describes how to set the expiration date on a backup in SQL Server 2017SQL Server 2017 by using SQL Server Management StudioSQL Server Management Studio or Transact-SQLTransact-SQL.

本主題內容In This Topic

開始之前Before You Begin

安全性Security

權限Permissions

BACKUP DATABASE 和 BACKUP LOG 權限預設為 sysadmin 固定伺服器角色以及 db_ownerdb_backupoperator 固定資料庫角色的成員。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 Database EngineSQL Server Database Engine執行個體之後,在 [物件總管] 中按一下伺服器名稱展開伺服器樹狀目錄。After connecting to the appropriate instance of the MicrosoftMicrosoft SQL Server Database EngineSQL 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 EngineDatabase EngineConnect to the Database EngineDatabase Engine.

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

  3. BACKUP 陳述式中,指定 EXPIREDATE 或 RETAINDAYS 選項,以決定 SQL Server Database EngineSQL Server Database Engine 何時可覆寫備份。In the BACKUP statement, specify either the EXPIREDATE or RETAINDAYS option to determine when the SQL Server Database EngineSQL Server Database Engine can overwrite the backup. 如果沒有指定任何選項,便會由 media retention 伺服器組態設定來決定到期日。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)