完整檔案備份 (SQL Server)Full File Backups (SQL Server)

適用於: 是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 Server 資料庫有關。This topic is relevant for SQL ServerSQL Server databases that contain multiple files or filegroups.

SQL ServerSQL Server 資料庫中的檔案可個別進行備份和還原。The files in a SQL ServerSQL Server database can be backed up and restored individually. 而且,您可以指定整個檔案群組,而不是個別指定每個構成的檔案。Also, you can specify a whole filegroup instead of specifying each constituent file individually. 請注意,如果檔案群組的任何檔案離線 (例如因為檔案正在還原中),整個檔案群組就會離線,並且無法進行備份。Note that if any file in a filegroup is offline (for example, because the file is being restored), the whole filegroup is offline and cannot be backed up.

唯讀檔案群組的檔案備份可以結合部分備份。File backups of read-only filegroups can be combined with partial backups. 部分備份包含所有讀取/寫入檔案群組,以及一個或多個唯讀檔案群組 (選用)。Partial backups include all the read/write filegroups and, optionally, one or more read-only filegroups. 如需詳細資訊,請參閱部分備份 (SQL Server)For more information, see Partial Backups (SQL Server).

檔案備份可以當成差異檔案備份的 「差異基底」 (Differential base)。A file backup can serve as the differential base for differential file backups. 如需詳細資訊,請參閱 差異備份 (SQL Server)For more information, see Differential Backups (SQL Server).

注意

完整檔案備份通常稱為「檔案備份」 ,但與「差異檔案備份」 明確對照時除外。Full file backups are typically called file backups, except when they are being explicitly compared with differential file backups.

本主題內容:In This Topic:

檔案備份的優點Benefits of File Backups

檔案備份提供下列優於資料庫備份的優點:File backups offer the following advantages over database backups:

  • 使用檔案備份可以增加復原的速度,因為這樣可以讓您只還原受損的檔案,而不需要還原資料庫的其餘部分。Using file backups can increase the speed of recovery by letting you restore only damaged files, without restoring the rest of the database.

    例如,如果資料庫由數個檔案組成,這些檔案分別位在不同磁碟上,現在有一個磁碟故障了,就只需還原故障磁碟上的檔案。For example, if a database consists of several files that are located on different disks and one disk fails, only the file on the failed disk has to be restored. 損毀的檔案可以快速予以還原,而且其復原速度也比復原整個資料庫還要快。The damaged file can be quickly restored, and recovery is faster than it would be for an entire database.

  • 比起完整資料庫備份,檔案備份在排程與媒體處理上彈性更大,因為對於大型資料庫,完整資料庫備份會變得難以管理。File backups increase flexibility in scheduling and media handling over full database backups, which for very large databases can become unmanageable. 對於含有各種更新特性資料的大型資料庫來說,檔案或檔案群組備份的增強彈性也很有用處。The increased flexibility of file or filegroup backups is also useful for large databases that contain data that has varying update characteristics.

檔案備份的缺點Disadvantages of File Backups

  • 相較於完整資料庫備份,檔案備份的主要缺點在於增加管理上的複雜性。The primary disadvantage of file backups compared to full database backups is the additional administrative complexity. 維護和持續追蹤完整的備份組是相當耗時的工作,其耗費成本甚至可能會超過完整資料庫備份的空間需求。Maintaining and keeping track of a complete set of these backups can be a time-consuming task that might outweigh the space requirements of full database backups.

  • 如果損毀的檔案沒有備份,媒體故障將可能造成整個資料庫無法復原。A media failure can make a complete database unrecoverable if a damaged file lacks a backup. 因此,必須維護一組完整的檔案備份,而在完整/大量記錄復原模式下,則還要備份一個或多個記錄備份,至少涵蓋第一次完整檔案備份和最後一次完整檔案備份之間的間隔。You must therefore maintain a complete set of file backups, and, for the full/bulk-logged recovery model, one or more log backups covering minimally the interval between the first full file backup and last full file backup.

檔案備份概觀Overview of File Backups

完整檔案備份會備份一個或多個檔案或檔案群組中的所有資料。A full file backup backs up all the data in one or more files or filegroups. 根據預設,檔案備份會包含足以將檔案向前復原到備份作業結束的記錄檔記錄。By default, file backups contain enough log records to roll forward the file to the end of the backup operation.

對於每一個復原模式來說,備份唯讀檔案或檔案群組都是相同的。Backing up a read-only file or filegroup is the same for every recovery model. 在完整復原模式下,一組完整的完整檔案備份連同足以涵蓋所有檔案備份的記錄備份,就相當於一個完整資料庫備份。Under the full recovery model, a complete set of full file backups, together with enough log backups to span all the file backups, is the equivalent of a full database backup.

同一時間只能進行一個檔案備份作業。Only one file backup operation can occur at a time. 您可以在一個作業中備份多個檔案,但是如果您只需要還原單一檔案,這樣可能延長復原的時間。You can back up multiple files in one operation, but this might extend the recovery time if you only have to restore a single file. 這是因為若要找到這個檔案,就要讀取整個備份。This is because to locate that file, the whole backup is read.

注意

您可以從資料庫備份還原個別檔案;不過,若要尋找並還原一個檔案,從資料庫備份著手會比從檔案備份來得更久。Individual files can be restored from a database backup; however, locating and restoring a file takes longer from a database backup than from a file backup.

檔案備份和簡單復原模式File Backups and the Simple Recovery Model

在簡單復原模式下,必須將所有的讀取/寫入檔案備份在一起。Under the simple recovery model, read/write files must all be backed up together. 這樣可以確保將資料庫還原到一致的時間點。This makes 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 所建立的備份即稱為「部分備份」。A backup that is created by specifying READ_WRITE_FILEGROUPS is known as a partial backup. 如需詳細資訊,請參閱部分備份 (SQL Server)For more information, see Partial Backups (SQL Server).

檔案備份和完整復原模式File Backups and the Full Recovery Model

在完整復原模式下,不論備份策略的其餘部分為何,您都必須備份交易記錄。Under the full recovery model, you must back up the transaction log, regardless of the rest of your backup strategy. 一組完整的完整檔案備份,連同足以從第一個檔案備份開始涵蓋所有檔案備份的記錄備份,就相當於一個完整資料庫備份。A complete set of full file backups, together with enough log backups to span all the file backups from the start of the first file backup, is the equivalent of a full database backup.

只使用檔案和記錄備份來還原資料庫可能會很複雜。Restoring a database using just file and log backups can be complex. 因此,最好是盡可能執行完整資料庫備份,然後在第一次檔案備份之前啟動記錄備份。Therefore, if it is possible, it is a best practice to perform a full database backup and start the log backups before the first file backup. 下圖顯示在建立資料庫 (時間 t0) 之後不久即建立完整資料庫備份 (時間 t1) 的策略。The following illustration shows a strategy in which a full database backup is taken (at time t1) soon after the database is created (at time t0). 第一個資料庫備份可讓交易記錄備份啟動。This first database backup enables transaction log backups to start. 交易記錄備份已排程為依設定的間隔進行。Transaction log backups are scheduled to occur at set intervals. 當間隔最符合資料庫的商務需求時,就會進行檔案備份。File backups occur at whatever interval best meets the business requirements for the database. 本圖顯示逐一備份的四個檔案群組。This illustration shows each of the four filegroups being backed up one at a time. 這些檔案群組的備份順序 (A、C、B、A) 反映了資料庫的商務需求。The order in which they are backed up (A, C, B, A) reflects the business requirements of the database.

結合資料庫、檔案和記錄備份的策略Strategy combining database, file, and log backups

注意

在完整復原模式下,還原讀取/寫入檔案備份時必須向前復原交易記錄,以確保檔案與資料庫其餘部分的一致性。Under the full recovery model, you must roll forward the transaction log when restoring a read/write file backup to make sure that the file is consistent with the rest of the database. 為了避免向前復原過多的交易記錄備份,請考慮使用差異檔案備份。To avoid rolling forward a lot of transaction log backups, consider using differential file backups. 如需詳細資訊,請參閱 差異備份 (SQL Server)For more information, see Differential Backups (SQL Server).

相關工作Related Tasks

建立檔案或檔案群組備份To create a file or filegroup backup

注意

「維護計畫精靈」不支援檔案備份。File backups are not supported by the Maintenance Plan Wizard.

另請參閱See Also

BACKUP (Transact-SQL) BACKUP (Transact-SQL)
備份概觀 (SQL Server) Backup Overview (SQL Server)
備份與還原:互通性與共存性 (SQL Server) Backup and Restore: Interoperability and Coexistence (SQL Server)
差異備份 (SQL Server) Differential Backups (SQL Server)
檔案還原 (簡單復原模式) File Restores (Simple Recovery Model)
檔案還原 (完整復原模式) File Restores (Full Recovery Model)
線上還原 (SQL Server) Online Restore (SQL Server)
分次還原 (SQL Server)Piecemeal Restores (SQL Server)