在备份和还原期间可能的介质错误 (SQL Server)Possible Media Errors During Backup and Restore (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 Server 2019 (15.x)SQL Server 2019 (15.x) 允许您在恢复数据库时不必顾及检测到的错误。gives you the option of recovering a database despite detected errors. 一个重要的新错误检测机制是创建备份校验和(可选),可以通过备份操作创建并通过还原操作验证。An important new error-detection mechanism is the optional creation of a backup checksum that can be created by a backup operation and validated by a restore operation. 您可以控制操作是否检查错误,以及遇到错误时是停止操作还是继续操作。You can control whether an operation checks for errors and whether the operation stops or continues on encountering an error. 如果备份包含备份校验和,则 RESTORE 和 RESTORE VERIFYONLY 语句可以检查错误。If a backup contains a backup checksum, RESTORE and RESTORE VERIFYONLY statements can check for errors.

备注

镜像备份最多提供 4 个介质集的副本(镜像),提供备用副本以便从损坏介质导致的错误中恢复。Mirrored backups provide up to four copies (mirrors) of a media set, providing alternative copies for recovering from errors caused by damaged media. 有关详细信息,请参阅本主题后面的 镜像备份媒体集 (SQL Server)不熟悉的读者。For more information, see Mirrored Backup Media Sets (SQL Server).

备份校验和Backup Checksums

SQL ServerSQL Server 支持三种校验和:页校验和、日志块校验和以及备份校验和。supports three types of checksums: a checksum on pages, a checksum in log blocks, and a backup checksum. 生成备份校验和时,BACKUP 将验证从数据库读取的数据是否与数据库中存在的任意校验和或页残缺指示一致。When generating a backup checksum, BACKUP verifies that the data read from the database is consistent with any checksum or torn-page indication that is present in the database.

BACKUP 语句选择性地计算备份流的备份校验和;如果给定页上存在页校验和或残缺页信息,则当备份该页时,BACKUP 还将验证它的校验和、残缺页状态以及 ID。The BACKUP statement optionally computes a backup checksum on the backup stream; if page-checksum or torn-page information is present on a given page, when backing up the page, BACKUP also verifies the checksum and torn-page status and the page ID, of the page. 创建备份校验和时,备份操作不会向页中添加任何校验和。When creating a backup checksum, a backup operation does not add any checksums to pages. 将在这些页位于数据库中时对其进行备份,备份不会修改这些页。Pages are backed up as they exist in the database, and the pages are unmodified by backup.

由于验证和生成备份校验和引起的开销,使用备份校验和会对性能造成潜在的影响。Due to the overhead verifying and generating backup checksums, using backup checksums poses a potential performance impact. 工作负荷和备份吞吐量都可能受到影响。Both the workload and the backup throughput may be affected. 因此,不是必须使用备份校验和。Therefore, using backup checksums is optional. 如果决定在备份过程中生成校验和,请仔细监视由此引起的 CPU 开销以及对系统中任何并发工作负荷造成的影响。When deciding to generate checksums during a backup, carefully monitor the CPU overhead incurred as well as the impact on any concurrent workload on the system.

BACKUP 永远不会修改磁盘上的源页面和页面内容。BACKUP never modifies the source page on disk nor the contents of a page.

在启用备份校验和后,备份操作将执行以下步骤:When backup checksums are enabled, a backup operation performs the following steps:

  1. 向备份介质写入页之前,备份操作将验证页级信息(页校验和或页残缺检测)是否存在。Before writing a page to the backup media, the backup operation verifies the page-level information (page checksum or torn page detection), if either exists. 如果两者都不存在,则备份无法验证页。If neither exists, backup cannot verify the page. 将按原样包含未经验证的页,并且其内容将添加到总备份校验和中。Unverified the pages are included as is, and their contents are added to the overall backup checksum.

    如果备份操作在验证过程中遇到页错误,备份将失败。If the backup operation encounters a page error during verification, the backup fails.

    备注

    有关页校验和及页残缺检测的详细信息,请参阅 ALTER DATABASE 语句的 PAGE_VERIFY 选项。For more information about page checksums and torn page detection, see the PAGE_VERIFY option of the ALTER DATABASE statement. 有关详细信息,请参阅 ALTER DATABASE SET 选项 (Transact-SQL)For more information, see ALTER DATABASE SET Options (Transact-SQL).

  2. 无论是否存在页校验和,BACKUP 都会为备份流生成一个单独的备份校验和。Regardless of whether page checksums are present, BACKUP generates a separate backup checksum for the backup streams. 还原操作可使用(可选)备份校验和来验证该备份是否损坏。Restore operations can optionally use the backup checksum to validate that the backup is not corrupted. 备份校验和存储在备份介质上,而不是存储在数据库页上。The backup checksum is stored on the backup media, not on the database pages. 备份校验还可根据需要在还原时使用。The backup checksum can optionally be used at restore time.

  3. 备份集标记为包含备份校验和(在 msdb..backupsethas_backup_checksums列中)。The backup set is flagged as containing backup checksums (in the has_backup_checksums column of msdb..backupset). 有关详细信息,请参阅 backupset (Transact-SQL)For more information, see backupset (Transact-SQL).

在还原操作过程中,如果备份介质中存在备份校验和,则默认情况下,RESTORE 和 RESTORE VERIFYONLY 语句都将验证备份校验和及页校验和。During a restore operation, if backup checksums are present on the backup media, by default, both the RESTORE and RESTORE VERIFYONLY statements verify the backup checksums and page checksums. 如果不存在备份校验和,则这两种还原操作直接执行,而不会进行验证;这是因为没有备份校验和,还原无法可靠地验证页校验和。If there is no backup checksum, either restore operation proceeds without any verification; this is because without a backup checksum, restore cannot reliably verify page checksums.

在备份或还原操作过程中响应页校验和错误Response to Page Checksum Errors During a Backup or Restore Operation

默认情况下,在遇到页校验和错误后,BACKUP 或 RESTORE 操作将失败,而 RESTORE VERIFYONLY 操作将继续。By default, after encountering a page checksum error, a BACKUP or RESTORE operation fails and a RESTORE VERIFYONLY operation continues. 但是,您可以控制某一给定操作在遇到错误时是失败还是尽可能继续。However, you can control whether a given operation fails on encountering an error or continues as best it can.

如果 BACKUP 操作在遇到错误后将继续,则该操作将执行以下步骤:If a BACKUP operation continues after encountering errors, the operation performs the following steps:

  1. 将备份介质中的备份集标记为包含错误,并跟踪 msdb 数据库中 suspect_pages 表中的页。Flags the backup set on the backup media as containing errors and tracks the page in the suspect_pages table in the msdb database. 有关详细信息,请参阅 suspect_pages (Transact-SQL)For more information, see suspect_pages (Transact-SQL).

  2. 记录 SQL Server 错误日志中的错误。Logs the error in the SQL Server error log.

  3. 将备份集标记为包含此类型的错误(在 msdb.backupsetis_damaged列中)。Marks the backup set as containing this type of error (in the is_damaged column of msdb.backupset). 有关详细信息,请参阅 backupset (Transact-SQL)For more information, see backupset (Transact-SQL).

  4. 发出一条消息,说明已成功生成备份,但备份中包含页错误。Issues a message that the backup was successfully generated, but contains page errors.

相关任务Related Tasks

启用或禁用备份校验和To enable or disable backup checksums

控制备份操作期间的错误响应To control the response to a error during a backup operation

另请参阅See Also

ALTER DATABASE (Transact-SQL) ALTER DATABASE (Transact-SQL)
BACKUP (Transact-SQL) BACKUP (Transact-SQL)
backupset (Transact-SQL) backupset (Transact-SQL)
镜像备份媒体集 (SQL Server) Mirrored Backup Media Sets (SQL Server)
RESTORE (Transact-SQL) RESTORE (Transact-SQL)
RESTORE VERIFYONLY (Transact-SQL)RESTORE VERIFYONLY (Transact-SQL)