備份檔案與檔案群組Back Up Files and Filegroups

適用於: 是SQL Server 否Azure SQL Database 否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 Studio或 PowerShell,在 Transact-SQLTransact-SQL中備份檔案與檔案群組。This topic describes how to back up files and filegroups in SQL ServerSQL Server by using SQL Server Management StudioSQL Server Management Studio, Transact-SQLTransact-SQL, or PowerShell. 當完整的資料庫備份因資料庫大小和效能需求而變得不可行時,您可以建立檔案備份來代替。When the database size and performance requirements make a full database backup impractical, you can create a file backup instead. 「檔案備份」 (File Backup) 包含一或多個檔案 (或檔案群組) 中的所有資料。A file backup contains all the data in one or more files (or filegroups).

如需詳細資訊,請參閱 完整檔案備份 (SQL Server)差異備份 (SQL Server)For more information about file backups, see Full File Backups (SQL Server) and Differential Backups (SQL Server).

開始之前Before You Begin

限制事項Limitations and Restrictions

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

  • 在簡單復原模式下,必須將所有的讀取/寫入檔案備份在一起。Under the simple recovery model, read/write files must all be backed up together. 這有助於確保資料庫還原到一致的時間點。This helps make sure that the database can be restored to a consistent point in time. 不要個別指定每一個讀取/寫入檔案或檔案群組,請改用 READ_WRITE_FILEGROUPS 選項。Instead of individually specifying each read/write file or filegroup, use the READ_WRITE_FILEGROUPS option. 這個選項會備份資料庫中的所有讀取/寫入檔案群組。This option backs up all the read/write filegroups in the database. 藉由指定 READ_WRITE_FILEGROUPS 所建立的備份即稱為「部分備份」 ,請參閱部分備份 (SQL Server)A backup that is created by specifying READ_WRITE_FILEGROUPS is known as a partial backup, see Partial Backups (SQL Server).

如需基本備份概念的詳細資訊,請參閱 備份概觀 (SQL Server)For more information about limitations and restrictions, see Backup Overview (SQL Server).

建議Recommendations

根據預設,每項成功的備份作業都會在 SQL ServerSQL Server 錯誤記錄檔與系統事件記錄檔中,加入一個項目。By default, every successful backup operation adds an entry in the SQL ServerSQL Server error log and in the system event log. 如果您經常備份記錄檔,這些成功訊息可能會快速累積,因而產生龐大的錯誤記錄檔,讓您難以尋找其他訊息。If you back up the log very frequently, these success messages accumulate quickly, resulting in huge error logs that can make finding other messages difficult. 在這類情況下,如果沒有任何指令碼相依於這些記錄項目,您就可以使用追蹤旗標 3226 來隱藏這些記錄項目,請參閱追蹤旗標 (Transact-SQL)In such cases you can suppress these log entries by using trace flag 3226 if none of your scripts depend on those entries, see Trace Flags (Transact-SQL).

權限Permissions

BACKUP DATABASEBACKUP LOG 權限預設為 系統管理員固定伺服器角色以及 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

  1. 連接到適當的 SQL Server Database EngineSQL Server Database Engine執行個體之後,請在 [物件總管] 中按一下伺服器名稱以展開伺服器樹狀目錄。After connecting to the appropriate instance of the 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, verify the database name. 您可以選擇性從清單中選取不同的資料庫。You can optionally select a different database from the list.

  5. [備份類型] 清單中,選取 [完整][差異]In the Backup type list, select Full or Differential.

  6. 若是 [備份元件] 選項,請按一下 [檔案與檔案群組]For the Backup component option, click File and Filegroups.

  7. [選取檔案與檔案群組] 對話方塊中,選取您要備份的檔案及檔案群組。In the Select Files and Filegroups dialog box, select the files and filegroups you want to back up. 您可以選取一或多個個別檔案,或選取檔案群組的方塊,以便自動選取該檔案群組中的所有檔案。You can select one or more individual files or check the box for a filegroup to automatically select all the files in that filegroup.

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

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

  10. 指定備份組會在何時過期: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 option, 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.

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

    注意

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

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

  13. 按下列項目之一,以選取 [覆寫媒體] 選項: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.

      如需備份至現有媒體集的相關資訊,請參閱媒體集、媒體家族與備份組 (SQL Server)For information about backing up to an existing media set, see Media Sets, Media Families, and Backup Sets (SQL Server).

      • (選擇性) 選取 [檢查媒體集名稱及備份組是否逾期] ,以讓備份作業確認媒體集及備份組逾期的日期和時間。(optional) Select Check media set name and backup set expiration to cause the backup operation to verify the date and time at which the media set and backup set expire.

      • (選擇性) 在 [媒體集名稱] 文字方塊中輸入名稱。(optional) 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 whether the actual name matches the name that 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.

      如需建立新媒體集的詳細資訊,請參閱媒體集、媒體家族與備份組 (SQL Server)For more information about creating a new media set, see Media Sets, Media Families, and Backup Sets (SQL Server).

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

  15. 如果是備份至磁帶機 (在 [一般] 頁面的 [目的地] 區段中指定),[備份後卸載磁帶] 選項會啟用供選擇。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 enables 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).

  16. SQL Server 2008 EnterpriseSQL Server 2008 Enterprise 及更新版本支援 備份壓縮and later versions support 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.

    如需檢視現有備份壓縮預設值,請參閱檢視或設定備份壓縮預設伺服器設定選項To view the current backup compression default, see View or Configure the backup compression default Server Configuration Option

使用 Transact-SQLUsing Transact-SQL

若要建立檔案或檔案群組備份,請使用 BACKUP DATABASE <file_or_filegroup> 陳述式。To create a file or filegroup backup, use a BACKUP DATABASE <file_or_filegroup> statement. 這個陳述式至少必須指定下列各項:Minimally, this statement must specify the following:

  • 資料庫名稱。The database name.

  • 分別為每個檔案或檔案群組指定 FILE 或 FILEGROUP 子句。A FILE or FILEGROUP clause for each file or filegroup, respectively.

  • 完整備份所要寫入的備份裝置。The backup device on which the full backup will be written.

檔案備份的基本 Transact-SQLTransact-SQL 語法為:The basic Transact-SQLTransact-SQL syntax for a file backup is:

BACKUP DATABASE databaseBACKUP DATABASE database

{ FILE = logical_file_name | FILEGROUP = logical_filegroup_name } [ , ...f ]{ FILE =logical_file_name | FILEGROUP =logical_filegroup_name } [ ,...f ]

TO backup_device [ , ...n ]TO backup_device [ ,...n ]

[ WITH with_options [ , ...o ] ] ;[ WITH with_options [ ,...o ] ] ;

選項Option [描述]Description
databasedatabase 這是要備份交易記錄、部分資料庫或完整資料庫的來源資料庫。Is the database from which the transaction log, partial database, or complete database is backed up.
FILE = logical_file_nameFILE =logical_file_name 指定要包含在檔案備份中檔案的邏輯名稱。Specifies the logical name of a file to include in the file backup.
FILEGROUP = logical_filegroup_nameFILEGROUP =logical_filegroup_name 指定要包含在檔案備份中的檔案群組的邏輯名稱。Specifies the logical name of a filegroup to include in the file backup. 在簡單復原模式之下,只允許唯讀檔案群組使用檔案群組備份。Under the simple recovery model, a filegroup backup is allowed only for a read-only filegroup.
[ , ...f ][ ,...f ] 這是一個預留位置,表示可以指定多個檔案和檔案群組。Is a placeholder that indicates that multiple files and filegroups may be specified. 檔案或檔案群組的數目沒有限制。The number of files or filegroups is unlimited.
backup_device [ , ...n ]backup_device [ ,...n ] 指定一份清單,列出備份作業可使用的 1 到 64 個備份裝置。Specifies a list of from 1 to 64 backup devices to use for the backup operation. 您可以指定實體備份裝置,或者指定對應的邏輯備份裝置 (若已經定義)。You can specify a physical backup device, or you can specify a corresponding logical backup device, if already defined. 若要指定實體備份裝置,請使用 DISK 或 TAPE 選項:To specify a physical backup device, use the DISK or TAPE option:

{ DISK | TAPE } = physical_backup_device_name{ DISK | TAPE } =physical_backup_device_name

如需詳細資訊,請參閱 備份裝置 (SQL Server)執行個體上建立資料庫備份,就需要這個選項。For more information, see Backup Devices (SQL Server).
WITH with_options [ , ...o ]WITH with_options [ ,...o ] 另外,也可以指定一個或多個其他選項,如 DIFFERENTIAL。Optionally, specifies one or more additional options, such as DIFFERENTIAL. 差異檔案備份需要以完整檔案備份作為基底。A differential file backup requires a full file backup as a base.

如需詳細資訊,請參閱建立差異資料庫備份 (SQL Server)For more information, see Create a Differential Database Backup (SQL Server).

在完整復原模式下,您還必須備份交易記錄。Under the full recovery model, you must also back up the transaction log. 若要使用一組完整的完整檔案備份來還原資料庫,您還必須有足夠的記錄備份,才能從第一個檔案備份開始涵蓋所有的檔案備份。To use a complete set of full file backups to restore a database, you must also have enough log backups to span all the file backups, from the start of the first file backup.

如需詳細資訊,請參閱 備份交易記錄 (SQL Server)資料庫還原至新位置,並選擇性地重新命名資料庫。For more information, see Back Up a Transaction Log (SQL Server).

範例Examples

下列範例會備份 Sales 資料庫次要檔案群組的一或多個檔案。The following examples back up one or more files of the secondary filegroups of the Sales database. 這個資料庫使用完整復原模式,而且包含下列次要檔案群組:This database uses the full recovery model and contains the following secondary filegroups:

  • 名為 SalesGroup1 的檔案群組,其中含有檔案 SGrp1Fi1SGrp1Fi2A filegroup named SalesGroup1 that has the files SGrp1Fi1 and SGrp1Fi2.

  • 名為 SalesGroup2 的檔案群組,其中含有檔案 SGrp2Fi1SGrp2Fi2A filegroup named SalesGroup2 that has the files SGrp2Fi1 and SGrp2Fi2.

A.A. 建立兩個檔案的檔案備份Create a file backup of two files

下列範例會建立只有 SGrp1Fi2 檔案群組之 SalesGroup1 檔案及 SGrp2Fi2 檔案群組之 SalesGroup2 檔案的差異檔案備份。The following example creates a differential file backup of only the SGrp1Fi2 file of the SalesGroup1 and the SGrp2Fi2 file of the SalesGroup2 filegroup.

--Backup the files in the SalesGroup1 secondary filegroup.  
BACKUP DATABASE Sales  
   FILE = 'SGrp1Fi2',   
   FILE = 'SGrp2Fi2'   
   TO DISK = 'G:\SQL Server Backups\Sales\SalesGroup1.bck';  
GO  

B.B. 建立次要檔案群組的完整檔案備份Create a full file backup of the secondary filegroups

下列範例會為兩個次要檔案群組中的每個檔案建立完整檔案備份。The following example creates a full file backup of every file in both of the secondary filegroups.

--Back up the files in SalesGroup1.  
BACKUP DATABASE Sales  
   FILEGROUP = 'SalesGroup1',  
   FILEGROUP = 'SalesGroup2'  
   TO DISK = 'C:\MySQLServer\Backups\Sales\SalesFiles.bck';  
GO  

C.C. 建立次要檔案群組的差異檔案備份Create a differential file backup of the secondary filegroups

下列範例會為兩個次要檔案群組中的每個檔案建立差異檔案備份。The following example creates a differential file backup of every file in both of the secondary filegroups.

--Back up the files in SalesGroup1.  
BACKUP DATABASE Sales  
   FILEGROUP = 'SalesGroup1',  
   FILEGROUP = 'SalesGroup2'  
   TO DISK = 'C:\MySQLServer\Backups\Sales\SalesFiles.bck'  
   WITH   
      DIFFERENTIAL;  
GO  

使用 PowerShellUsing PowerShell

設定並使用 SQL Server PowerShell 提供者Set up and use the SQL Server PowerShell Provider.

使用 Backup-SqlDatabase Cmdlet,並指定 -BackupAction 參數值的 FilesUse the Backup-SqlDatabase cmdlet and specify Files for the value of the -BackupAction parameter. 另外再指定下列其中一個參數:Also, specify one of the following parameters:

  • 若要備份特定檔案,請指定 -DatabaseFileString 參數,其中 String 是要備份的一個或多個資料庫檔案。To back up a specific file, specify the -DatabaseFileString parameter, where String is one or more database files to be backed up.

  • 若要備份指定檔案群組中的所有檔案,請指定 -DatabaseFileGroupString 參數,其中 String 是要備份的一個或多個資料庫檔案群組。To back up all the files in a given filegroup, specify the -DatabaseFileGroupString parameter, where String is one or more database filegroups to be backed up.

以下範例會為 <myDatabase> 資料庫內的次要檔案群組 'FileGroup1' 和 'FileGroup2' 建立其中每個檔案的完整檔案備份。The following example creates a full file backup of every file in the secondary filegroups 'FileGroup1' and 'FileGroup2' in the <myDatabase> database. 備份是建立在伺服器執行個體 Computer\Instance的預設備份位置。The backups are created on the default backup location of the server instance Computer\Instance.

Backup-SqlDatabase -ServerInstance Computer\Instance -Database <myDatabase> -BackupAction Files -DatabaseFileGroup "FileGroup1","FileGroup2" 

另請參閱See Also

備份概觀 (SQL Server) Backup Overview (SQL Server)
BACKUP (Transact-SQL) BACKUP (Transact-SQL)
RESTORE (Transact-SQL) RESTORE (Transact-SQL)
備份記錄與標頭資訊 (SQL Server) Backup History and Header Information (SQL Server)
備份資料庫 (一般頁面) Back Up Database (General Page)
備份資料庫 (備份選項頁面) Back Up Database (Backup Options Page)
完整檔案備份 (SQL Server) Full File Backups (SQL Server)
差異備份 (SQL Server) Differential Backups (SQL Server)
檔案還原 (完整復原模式) File Restores (Full Recovery Model)
檔案還原 (簡單復原模式)File Restores (Simple Recovery Model)