建立差異資料庫備份 (SQL Server)Create a Differential Database Backup (SQL Server)

此主題描述如何使用 SQL Server 2014SQL Server 2014Transact-SQLSQL Server Management Studio ,在 Transact-SQLTransact-SQL中建立差異資料庫備份。This topic describes how to create a differential database backup in SQL Server 2014SQL Server 2014 by using Transact-SQLSQL Server Management Studio or Transact-SQLTransact-SQL.

本主題內容In This Topic

開始之前Before You Begin

限制事項Limitations and Restrictions

  • 在明確或隱含的交易中,並不允許使用 BACKUP 陳述式。The BACKUP statement is not allowed in an explicit or implicit transaction.

必要條件Prerequisites

  • 建立差異資料庫備份時,需要有先前的完整資料庫備份存在。Creating a differential database backup requires that a previous full database backup exist. 如果從未備份過所選取的資料庫,在建立任何差異備份之前,請先執行完整資料庫備份。If the selected database has never been backed up, run a full database backup before creating any differential backups. 如需詳細資訊,請參閱 建立完整資料庫備份 (SQL Server)中建立差異資料庫備份。For more information, see Create a Full Database Backup (SQL Server).

建議Recommendations

  • 隨著差異備份大小增加,還原差異備份會大幅增加還原資料庫所需的時間。As the differential backups increase in size, restoring a differential backup can significantly increase the time that is required to restore a database. 因此,建議您定期進行新的完整備份,為資料建立新的差異基底。Therefore, we recommend that you take a new full backup at set intervals to establish a new differential base for the data. 例如,您可能每週進行整個資料庫的完整備份 (亦即,完整資料庫備份),然後在該週定期進行一連串的差異資料庫備份。For example, you might take a weekly full backup of the whole database (that is, a full database backup) followed by a regular series of differential database backups during the week.

安全性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 Server]SQL Server 必須能夠讀取和寫入裝置;執行 [SQL Server]SQL Server 服務的帳戶必須具備寫入權限。must be able to read and write to the device; the account under which the [SQL Server]SQL 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 create a differential database 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. [資料庫] 清單方塊中確認資料庫名稱。In the Database list box, verify the database name. 您可以選擇性從清單中選取不同的資料庫。You can optionally select a different database from the list.

    您可以為任何復原模式 (完整、大量記錄或簡單) 執行差異備份。You can perform a differential backup for any recovery model (full, bulk-logged, or simple).

  5. [備份類型] 清單方塊中,選取 [差異]In the Backup type list box, select Differential.

    重要

    差異已選取,請確認只複製備份核取方塊。When Differential is selected, verify that the Copy Only Backup check box is cleared.

  6. 針對 [備份元件] ,按一下 [資料庫]For Backup component, click Database.

  7. 接受 [名稱] 文字方塊中建議的預設備份組名稱,或者輸入不同的備份組名稱。Either accept the default backup set name suggested in the Name text box, or enter a different name for the backup set.

  8. (選擇性) 在 [描述] 文字方塊中輸入備份組的描述。Optionally, in the Description text box, enter a description of the backup set.

  9. 指定備份組會在何時過期:Specify when the backup set will expire:

    • 若要讓備份組在特定的天數後過期,請按一下 [之後] (預設選項),然後輸入備份組建立之後將會過期的天數。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.

  10. 按一下 [磁碟][磁帶] ,以選擇備份目的地的類型。Choose the type of backup destination by clicking Disk or Tape. 若要選取包含單一媒體集的磁碟或磁帶機 (最多 64 個) 的路徑,請按一下 [加入]To select the path of up to 64 disk or tape drives containing a single media set, click Add. 選取的路徑會在 [備份至] 清單方塊中顯示。The selected paths are displayed in the Backup to list box.

    若要移除備份目的地,請選取目的地,然後按一下 [移除]To remove a backup destination, select it and click Remove. 若要檢視備份目的地的內容,請選取目的地,然後按一下 [內容]To view the contents of a backup destination, select it and click Contents.

  11. 若要檢視或選取進階選項,請按一下 [選取頁面] 窗格中的 [選項]To view or select the advanced options, click Options in the Select a page pane.

  12. 按下列項目之一,以選取 [覆寫媒體] 選項:Select an Overwrite Media option, by clicking one of the following:

    • 備份至現有的媒體集Back up to the existing media set

      針對這個選項,按一下 [附加至現有的備份組][覆寫所有現有的備份組]For this option, click either Append to the existing backup set or Overwrite all existing backup sets. (選擇性) 選取 [檢查媒體集名稱及備份組是否逾期] 核取方塊,然後在 [媒體集名稱] 文字方塊中輸入名稱。Optionally, check the Check media set name and backup set expiration check box and, optionally, enter a name in the Media set name text box. 如果未指定名稱,就會建立一個空白名稱的媒體集。If no name is specified, a media set with a blank name is created. 如果您指定媒體集名稱,就會檢查媒體 (磁帶或磁碟),以查看實際名稱是否與您在此處輸入的名稱相符。If you specify a media set name, the media (tape or disk) is checked to see if the actual name matches the name you enter here.

      如果您讓媒體名稱保持空白,並核取方塊以針對媒體進行檢查,成功也會使媒體上的媒體名稱保持空白。If you leave the media name blank and check the box to check it against the media, success will equal the media name on the media also being blank.

    • 備份至新的媒體集,並清除所有現有的備份組Back up to a new media set, and erase all existing backup sets

      針對這個選項,在 [新媒體集名稱] 文字方塊中輸入名稱,然後選擇性在 [新媒體集描述] 文字方塊中描述媒體集。For this option, enter a name in the New media set name text box, and, optionally, describe the media set in the New media set description text box.

  13. (選擇性) 在 [可靠性] 區段中選取:In the Reliability section, optionally, check:

  14. 如果是備份至磁帶機 (在 [一般] 頁面的 [目的地] 區段中指定),[備份後卸載磁帶] 選項會啟用供選擇。If you are backing up to a tape drive (as specified in the Destination section of the General page), the Unload the tape after backup option is active. 按一下這個選項會啟動 [卸載之前倒轉磁帶] 選項。Clicking this option activates the Rewind the tape before unloading option.

    注意

    除非您備份的是交易記錄檔 (依 [一般] 頁面的 [備份類型] 區段中的指定),否則 [交易記錄檔] 區段中的選項為非使用中。The options in the Transaction log section are inactive unless you are backing up a transaction log (as specified in the Backup type section of the General page).

  15. SQL Server 2008 EnterpriseSQL Server 2008 Enterprise 和更新的版本支援 備份壓縮and later supports backup compression. 依預設,備份壓縮與否取決於 備份壓縮預設 伺服器組態選項的值。By default, whether a backup is compressed depends on the value of the backup-compression default server configuration option. 不過,不論目前的伺服器層級預設值為何,您都可以透過核取 [壓縮備份] 壓縮備份,而且可以透過核取 [不要壓縮備份] 防止壓縮。However, regardless of the current server-level default, you can compress a backup by checking Compress backup, and you can prevent compression by checking Do not compress backup.

    檢視目前的 backup compression defaultTo view the current backup compression default

    注意

    或者,您可以使用「維護計畫精靈」來建立差異資料庫備份。Alternatively, you can use the Maintenance Plan Wizard to create differential database backups.

使用 Transact-SQLUsing Transact-SQL

建立差異資料庫備份To create a differential database backup

  1. 執行 BACKUP DATABASE 陳述式以建立差異資料庫備份,請指定:Execute the BACKUP DATABASE statement to create the differential database backup, specifying:

    • 欲備份的資料庫名稱。The name of the database to back up.

    • 寫入完整資料庫備份的備份裝置。The backup device where the full database backup is written.

    • DIFFERENTIAL 子句,指定只備份資料庫自上次進行完整資料庫備份後的變更部分。The DIFFERENTIAL clause, to specify that only the parts of the database that have changed after the last full database backup was created are backed up.

    必要的語法如下:The required syntax is:

    BACKUP DATABASE <資料庫名稱> TO <備份裝置> WITH DIFFERENTIALBACKUP DATABASE database_name TO <backup_device> WITH DIFFERENTIAL

範例 (Transact-SQL)Example (Transact-SQL)

這個範例會建立 MyAdvWorks 資料庫的完整和差異資料庫備份。This example creates a full and a differential database backup for the MyAdvWorks database.

-- Create a full database backup first.  
BACKUP DATABASE MyAdvWorks   
   TO MyAdvWorks_1   
   WITH INIT;  
GO  
-- Time elapses.  
-- Create a differential database backup, appending the backup  
-- to the backup device containing the full database backup.  
BACKUP DATABASE MyAdvWorks  
   TO MyAdvWorks_1  
   WITH DIFFERENTIAL;  
GO  

另請參閱See Also

差異備份 (SQL Server) Differential Backups (SQL Server)
建立完整資料庫備份 (SQL Server) Create a Full Database Backup (SQL Server)
備份檔案和檔案群組 (SQL Server) Back Up Files and Filegroups (SQL Server)
還原差異資料庫備份 (SQL Server) Restore a Differential Database Backup (SQL Server)
還原交易記錄備份 (SQL Server) Restore a Transaction Log Backup (SQL Server)
維護計畫 Maintenance Plans
完整檔案備份 (SQL Server)Full File Backups (SQL Server)