差异备份 (SQL Server)Differential Backups (SQL Server)

适用对象:是SQL Server 否Azure SQL 数据库 否Azure Synapse Analytics (SQL DW) 否并行数据仓库 APPLIES TO: yesSQL Server noAzure SQL Database noAzure Synapse Analytics (SQL DW) 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. 差异备份所基于的完整备份称为差异的“基准” 。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

差异备份捕获在创建差异基准和创建差异备份之间发生更改的任何 盘区 (物理上连续的八个页的集合)的状态。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. 该图显示了二十四个数据区,其中的六个已发生更改。The figure shows 24 data extents, 6 of which have changed. 差异备份只包含这六个数据区。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 数据库引擎SQL Server Database Engine 无法检测或防止此问题的出现。The SQL Server 数据库引擎SQL 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)