差異備份 (SQL Server)Differential Backups (SQL Server)

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

此備份和還原主題與所有的 SQL ServerSQL Server 資料庫相關。This backup and restore topic is relevant for all SQL ServerSQL Server databases.

差異備份是以之前最近一次的完整資料備份為基礎。A differential backup is based on the most recent, previous full data backup. 差異備份只擷取自那次完整備份以後變更過的資料。A differential backup captures only the data that has changed since that full backup. 差異備份據以為基礎的完整備份就稱為差異的 「基底」 (Base)。The full backup upon which a differential backup is based is known as the base of the differential. 完整備份 (不包括僅限複製備份) 可做為一系列差異備份的基底,包括資料庫備份、部分備份及檔案備份。Full backups, except for copy-only backups, can serve as the base for a series of differential backups, including database backups, partial backups, and file backups. 檔案差異備份的基底備份可以包含在完整備份、檔案備份或部分備份之中。The base backup for a file differential backup can be contained within a full backup, a file backup, or a partial backup.

優點Benefits

  • 相較於建立完整備份,建立差異備份可以非常快速。Creating a differential backups can be very fast compared to creating a full backup. 差異備份只會記錄自差異備份據以為基礎的完整備份以後變更過的資料。A differential backup records only the data that has changed since the full backup upon the differential backup is based. 這有利於經常備份資料,以降低資料遺失的風險。This facilitates taking frequent data backups, which decrease the risk of data loss. 但是,在還原差異備份之前,必須先還原其基底。However, before you restore a differential backup, you must restore its base. 因此,從差異備份中還原較之從完整備份中還原,由於需要兩個備份檔案,因此勢必要採取更多的步驟和時間。Therefore restoring from a differential backup will necessarily take more steps and time than restoring from a full backup because two backup files are required.

  • 如果資料庫某個子集的修改比資料庫的其餘部分更加頻繁,差異資料庫備份就特別有用。Differential database backups are especially useful if a subset of a database is modified more frequently than the rest of the database. 在這些情形下,差異資料庫備份可以讓您經常備份,而避免完整資料庫備份的負擔。In these cases, differential database backups enable you back up frequently without the overhead of full database backups.

  • 在完整復原模式下,使用差異備份可減少您必須還原的記錄備份數目。Under the full recovery model, using differential backups can reduce the number of log backups that you have to restore.

差異備份概觀Overview of Differential Backups

差異備份擷取從建立差異基底到建立差異備份已變更之任何 「範圍」 (Extent) (八個實體連續頁面的集合) 的狀態。A differential backup captures the state of any extents (collections of eight physically contiguous pages) that have changed between when the differential base was created and the when differential backup is created. 這表示給定差異備份的大小取決於基底之後變更過的資料量。This means that the size of a given differential backup depends on the amount of data that has changed since the base. 一般而言,基底愈舊,新的差異備份就會愈大。Generally, the older a base is, the larger a new differential backup will be. 在一系列的差異備份中,經常更新的範圍很可能包含每個差異備份中的不同資料。In a series of differential backups, a frequently updated extent is likely to contain different data in each differential backup.

下圖說明差異備份的運作方式。The following illustration shows how a differential backup works. 圖中顯示 24 個資料範圍,其中 6 個已經變更。The figure shows 24 data extents, 6 of which have changed. 差異備份只包含這 6 個資料範圍。The differential backup contains only these 6 data extents. 差異備份作業必須藉助點陣圖頁面 (此頁面包含每個範圍的一個位元)。The differential backup operation relies on a bitmap page that contains a bit for every extent. 對於基底之後已更新的每個範圍,點陣圖中的位元將設為 1。For each extent updated since the base, the bit is set to 1 in the bitmap.

差異點陣圖識別變更的範圍Differential bitmap identifies changed extents

注意

僅限複製備份不會更新差異點陣圖。The differential bitmap is not updated by a copy-only backup. 因此,只複製備份不影響後續的差異備份。Therefore, a copy-only backup does not affect subsequent differential backups.

在基底之後不久建立的差異備份,其大小通常比差異基底小得多,A differential backup that is taken fairly soon after its base is usually significantly smaller than the differential base. 因此可以節省儲存空間和備份時間。This saves storage space and backup time. 不過,資料庫隨著時間不斷變更後,資料庫和特定差異基底之間的差異也會跟著增加。However, as a database changes over time, the difference between the database and a specific differential base increases. 差異備份及其基底之間相隔的時間愈長,差異備份的規模可能愈大。The longer the time between a differential backup and its base, the larger the differential backup is likely to be. 這表示差異備份的大小到最後會與差異基底的大小十分接近。This means that the differential backups can eventually approach the differential base in size. 大型的差異備份將喪失備份速度較快與規模較小的優勢。A large differential backup loses the advantages of a faster and smaller backup.

隨著差異備份大小增加,還原差異備份會大幅增加還原資料庫所需的時間。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.

還原時,您必須在還原差異備份之前先還原其基底。At restore time, before you restore a differential backup, you must restore its base. 接著,只還原最新的差異備份,以便將資料庫向前復原到建立該差異備份的時間點。Then, restore only the most recent differential backup to bring the database forward to the time when that differential backup was created. 通常,您會在還原最新的完整備份之後,接著還原以該完整備份為基礎的最新差異備份。Typically, you would restore the most recent full backup followed by the most recent differential backup that is based on that full backup.

使用記憶體最佳化資料表的資料庫差異備份Differential Backups of Databases with Memory-Optimized Tables

如需差異備份和具有記憶體最佳化資料表之資料庫的資訊,請參閱 備份含有記憶體最佳化資料表的資料庫For information about differential backups and databases with memory-optimized tables, see Backing Up a Database with Memory-Optimized Tables.

唯讀資料庫的差異備份Differential Backups of Read-Only Databases

若為唯讀資料庫,單獨使用的完整備份會比搭配差異備份一起使用更容易管理。For read-only databases, full backups used alone are easier to manage than when they are used with differential backups. 當資料庫是唯讀時,備份和其他作業無法改變檔案所包含的中繼資料。When a database is read-only, backup and other operations cannot change the metadata that is contained in the file. 因此,差異備份所需的中繼資料,例如差異備份開始的記錄序號 (差異基底 LSN),會儲存在 master 資料庫中。Therefore, metadata that is required by a differential backup, such as the log sequence number at which the differential backup begins (the differential base LSN) is stored in the master database. 如果差異基底是在資料庫為唯讀時建立,則差異點陣圖顯示的變更會比基底備份之後實際發生的變更還要多。If the differential base is taken when the database is read-only, the differential bitmap indicates more changes than have actually occurred since the base backup. 備份會讀取這些額外資料,但是不會將它寫入備份,因為 backupset 系統資料表中儲存的 differential_base_lsn 是用來判斷在基底之後是否實際變更過資料。The extra data is read by backup, but is not written to the backup, because the differential_base_lsn stored in the backupset system table is used to determine whether the data has actually changed since the base.

重建,還原或卸離後再重新附加唯讀資料庫時,會遺失差異基底資訊。When a read-only database is rebuilt, restored, or detached and attached, the differential-base information is lost. 發生這種情形的原因是, master 資料庫與使用者資料庫並未同步。This occurs because the master database is not synchronized with the user database. SQL Server Database EngineSQL Server Database Engine 無法偵測或防止此問題。The SQL Server Database EngineSQL Server Database Engine cannot detect or prevent this problem. 以後的任何差異備份將不會以最近的完整備份為基底,因此可能會產生出人意料的結果。Any later differential backups are not based on the most recent full backup and could provide unexpected results. 若要建立新的差異基底,我們建議您建立完整資料庫備份。To establish a new differential base, we recommend that you create a full database backup.

對唯讀資料庫使用差異備份的最佳作法Best Practices for Using Differential Backups with a Read-Only Database

在建立唯讀資料庫的完整資料庫備份之後,如果您打算建立後續的差異備份,請備份 master 資料庫。After you create a full database backup of a read-only database, if you intend to create a subsequent differential backup, back up the master database.

如果 master 資料庫遺失,在還原使用者資料庫的任何差異備份之前,請先將此資料庫還原。If the master database is lost, restore it before you restore any differential backup of a user database.

如果您先卸離再附加您打算於稍後使用差異備份的唯讀資料庫,請盡可能進行唯讀資料庫和 master 資料庫的完整資料庫備份。If you detach and attach a read-only database for which you plan to later use differential backups, as soon as it is practical, take a full database backup of both the read-only database and of the master database.

相關工作Related Tasks

另請參閱See Also

備份概觀 (SQL Server) Backup Overview (SQL Server)
完整資料庫備份 (SQL Server) Full Database Backups (SQL Server)
完整資料庫還原 (完整復原模式) Complete Database Restores (Full Recovery Model)
完整資料庫還原 (簡單復原模式) Complete Database Restores (Simple Recovery Model)
交易記錄備份 (SQL Server)Transaction Log Backups (SQL Server)