在備份和還原期間可能的媒體錯誤 (SQL Server)Possible Media Errors During Backup and Restore (SQL Server)

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

SQL Server 2017SQL Server 2017 可讓您選擇復原資料庫,而不理會偵測到的錯誤。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.

注意

鏡像備份提供高達四個媒體集複本 (鏡像),可在因為媒體損壞而導致錯誤時,提供其他的複本來復原。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 也會驗證該分頁的總和檢查碼、損毀頁狀態及分頁識別碼。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).

我們會持續聽取您的意見: 如果您發現本文中有過時或不正確的內容 (例如步驟或程式碼範例),請告訴我們。We are listening: If you find something outdated or incorrect in this article, such as a step or a code example, please tell us. 您可以按一下此頁面底部 [意見反應] 區段中的 [本頁] 按鈕。You can click the This page button in the Feedback section at the bottom of this page. 我們通常會在隔天閱讀有關 SQL 的每一個意見反應。We read every item of feedback about SQL, typically the next day. 謝謝。Thanks.

在還原作業期間,如果備份媒體上有備份總和檢查碼,依預設 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)