SQL Server 備份與還原作業的疑難排解

原始產品版本： SQL Server
原始 KB 編號： 224071

總結

本文將協助你排除常見的 SQL Server 備份與還原操作問題。 這些問題包括備份或還原效能緩慢、版本相容性錯誤、Always On 可用性群組備份工作、媒體錯誤、權限失效、第三方 VDI 與 VSS 備份、變更追蹤失敗，以及加密資料庫還原。 文章中也包含常見問題區塊及 SQL Server 備份與還原的參考主題連結。

備份和還原作業需要很長的時間

備份和還原作業會需要大量 I/O。 備份與還原的吞吐量取決於底層 I/O 子系統針對處理 I/O 量所進行最佳化的程度。 如果您懷疑備份作業已停止或完成時間過長，請使用以下一種或多種方法來估算完成時間或追蹤備份或還原作業的進度：

  • SQL Server 錯誤記錄檔包含先前備份和還原作業的相關信息。 您可以使用這些詳細數據來估計備份和還原資料庫目前狀態所需的時間。 以下是錯誤記錄檔的範例輸出：

    RESTORE DATABASE successfully processed 315 pages in 0.372 seconds (6.604 MB/sec)

  • 在 2016 SQL Server 及以後版本中，請使用 XEvent backup_restore_progress_trace 來追蹤備份與還原作業的進度。

  • 使用percent_completesys.dm_exec_requests欄來追蹤飛行中備份與恢復操作的進度。

  • 透過使用 Device throughput Bytes/sec 與 Backup/Restore throughput/sec 效能監控計數器來衡量備份與還原吞吐量。 如需詳細資訊，請參閱 SQL Server 備份裝置物件。

  • 使用estimate_backup_restore腳本來取得備份時間的估計值。

  • 請參閱運作方式：還原/備份執行什麼？。 此部落格文章提供備份或還原作業目前階段的深入解析。

調查備份或還原效能緩慢的問題

  1. 請檢查下表中是否遇到已知問題，並考慮套用相關修正或最佳實務。

    知識庫連結 說明和建議的動作
    備份和還原 SQL Server 資料庫 涵蓋能改善備份與還原效能的最佳實務。 例如，給執行 SQL Server 的 Windows 帳號 SE_MANAGE_VOLUME_NAME 權限，讓即時檔案初始化加速資料檔案操作。
    設定防病毒軟體以使用 SQL Server 防毒軟體可能會對檔案設置 .bak 鎖定，這會影響備份和還原操作的效能。 請遵循本文中的指引，從病毒掃描中排除備份檔。
    備份或還原作業至網路位置的速度很慢 若要將問題定位到網路，請從執行 SQL Server 的伺服器將一個大小相近的檔案複製到網路位置，並檢查其效能表現。

  2. 請檢查 SQL Server 錯誤日誌和 Windows 事件日誌，看看有沒有錯誤訊息指向問題原因。

  3. 如果你使用第三方軟體或資料庫維護計畫來進行同時備份，建議更改排程以減少寫入備份磁碟上的爭用。

  4. 請與您的 Windows 系統管理員合作，檢查硬體的韌體更新。

還原至早期 SQL Server 版本備份時的錯誤

問題

你無法將 SQL Server 備份還原到比建立備份版本更早的 SQL Server 版本。 例如，你無法將從 SQL Server 2022 實例備份還原到 SQL Server 2019 實例。 否則，會出現下列錯誤訊息：

錯誤 3169：資料庫已在執行 %ls 版本的伺服器上備份。 該版本和此伺服器不相容，此伺服器目前執行 %ls 版。 請將資料庫還原到支援此備份的伺服器，或使用與此伺服器相容的備份。

Resolution

請使用以下方法將存放於較新版本 SQL Server 的資料庫複製到較早版本的 SQL Server。

附註

以下程序假設你有兩個名為 SQL_A（較高版本）和 SQL_B（較低版本）的SQL Server實例。

  1. 在 SQL_A 和 SQL_B 上均下載並安裝最新版本的 SQL Server Management Studio (SSMS)。
  2. 在SQL_A上，請遵循下列步驟：
    1. 右鍵點擊 <「YourDatabase>>任務>產生腳本」，並選擇「為整個資料庫及所有資料庫物件撰寫腳本」的選項。
    2. 在 設定指令碼選項 畫面上，選取 進階，然後在 一般>為 SQL Server 版本編寫指令碼 下方選取 SQL_B 的版本。 然後，選擇最適合您的儲存選項，並繼續執行精靈。
    3. 使用 大量複製程式公用程式 (bcp) 來從不同的資料表複製資料。
  3. 在SQL_B上，請遵循下列步驟：
    1. 利用 SQL_A 伺服器產生的腳本來建立資料庫結構。
    2. 在每個表格中，關閉所有外鍵約束與觸發器。 如果資料表有身份欄位，請啟用身份插入。
    3. 使用 bcp 將你在前一步匯出的資料匯入對應的資料表。
    4. 資料匯入完成後，啟用外鍵限制與觸發器，並對步驟 c 中變更的每個資料表關閉身份插入。

此程序通常適用於中小型資料庫。 對於較大的資料庫，SSMS 及其他工具可能會出現記憶體不足的問題。 考慮使用 SQL Server Integration Services（SSIS）、複寫或其他選項，將資料庫從較舊版本複製到較早版本的 SQL Server。

如需有關如何為資料庫產生指令碼的資訊，請參閱使用產生指令碼選項編寫資料庫指令碼。

Always On 可用性群組中的備用工作問題

問題

在 Always On 可用性群組環境中，你會遇到影響備份工作或維護計畫的問題。

Resolution

  • 根據預設，自動備份喜好設定會設定為 [偏好次要]。 此設定規定備份必須在次要副本上進行，除非主要副本是唯一在線的副本。 用這個設定你無法對資料庫做差分備份。 要更改此設定，請在你目前的主要副本上使用 SSMS，並前往可用性群組的「屬性」下的備份偏好設定頁面。
  • 如果您使用維護計畫或排定的作業來產生資料庫備份，請在裝載可用性群組之可用性複本的每個伺服器執行個體上，為每個可用性資料庫建立作業。

如需 Always On 環境中備份的詳細資訊，請參閱下列文章：

從備份還原資料庫時發生的媒體錯誤

問題

顯示檔案問題的錯誤訊息通常指向備份檔案損壞。 以下錯誤是備份組損壞時可能遇到的問題範例：

3241：裝置 '%ls' 上的媒體系列格式不正確。 SQL Server 無法處理這個媒體家族。

3242：裝置 '%ls' 上的檔案不是有效的Microsoft磁帶格式備份集。

3243：裝置 '%ls' 上的媒體系列是使用Microsoft磁帶格式版本 %d.%d 建立的。 SQL Server 支援版本 %d.%d。

原因

這些問題可能因底層硬體（硬碟、網路儲存等）或病毒或惡意軟體而引起。 檢閱 Windows 系統事件記錄檔和硬體記錄中是否有回報的錯誤，並採取適當的動作（例如升級韌體或修正網路問題）。

Resolution

  • 請使用 RESTORE HEADERONLY 陳述式檢查你的備份。
  • 若要減少這些還原錯誤的發生，請在執行備份時啟用 Backup CHECKSUM 選項，以避免備份損毀的資料庫。 欲了解更多資訊，請參見 備份與還原期間可能的媒體錯誤 （SQL Server）。
  • 您也可以啟用追蹤旗標 3023，以在使用備份工具執行備份時啟用總和檢查碼。 欲了解更多資訊，請參閱 伺服器設定：備份檢查碼預設值。
  • 要解決這些問題，請尋找另一個可用的備份檔案或建立新的備份組。 Microsoft不提供任何可協助從損毀備份集擷取數據的解決方案。
  • 如果備份檔在一部伺服器上成功還原，但不在另一部伺服器上還原，請嘗試不同的方式在伺服器之間複製檔案。 例如，請嘗試 robocopy，而不是一般複製作業。 調查檔案在網路複製操作或目的儲存裝置時是否被更改。

備份失敗是因為權限問題

問題

當您嘗試執行資料庫備份作業時，會發生下列其中一個錯誤。

  • 案例 1：當您從 SQL Server Management Studio 執行備份時，備份會失敗並傳回下列錯誤訊息：

    伺服器 <伺服器名稱>的備份失敗。 （Microsoft.SqlServer.SmoExtended）
    System.Data.SqlClient.SqlError：無法開啟備份裝置 '<device name>'。 作業系統錯誤 5（存取被拒。） （Microsoft.SqlServer.Smo）

  • 案例 2：排程備份失敗，併產生錯誤訊息，其記錄在失敗作業的作業歷程記錄中，如下所示：

    Executed as user: <Owner of the job>. ....2 for 64-bit  Copyright (C) 2019 Microsoft. All rights reserved.    
Started:  5:49:14 PM  Progress: 2021-08-16 17:49:15.47    
Source: {GUID}      Executing query "DECLARE @Guid UNIQUEIDENTIFIER      EXECUTE msdb..sp...".: 100% complete  End Progress  
Error: 2021-08-16 17:49:15.74     
Code: 0xC002F210     
Source: Back Up Database (Full) Execute SQL Task     
Description: Executing the query "EXECUTE master.dbo.xp_create_subdir N'C:\backups\D..." failed with the following error: "xp_create_subdir() returned error 5, 'Access is denied.'". 
Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

原因

如果 SQL Server 服務帳號沒有對備份所寫資料夾的讀寫權限，兩種情況都可能發生。 備份語句可以作為工作步驟的一部分執行，或是從 SQL Server Management Studio 手動執行。 無論哪種情況，它們都是在 SQL Server 服務啟動帳號的上下文下執行。 所以，如果服務帳號沒有必要的權限，你就會收到之前提到的錯誤訊息。

Resolution

請前往資料夾的 Security 標籤，選擇 Advanced，然後使用 Effective Access 標籤，檢查該SQL Server服務帳號目前的權限。欲了解更多資訊，請參閱 Backup devices。

第三方備份或還原操作失敗

SQL Server 提供虛擬備份裝置介面（VDI）。 此 API 讓獨立軟體廠商能將 SQL Server 整合進產品中，以支援備份與還原操作。 這些 API 設計以提供可靠性與效能，並支援 SQL Server 的全方位備份與還原功能，包括快照與熱備份功能。

常用的疑難排解步驟

  • 在所有支援的 SQL Server 版本中，設定時都會建立一個名為 NT SERVICE\SQLWriter 的登入帳號。 請確認這個登入帳號是否存在於 SQL Server，並且是備份實例上的 sysadmin伺服器角色的一部分。 另外，檢查 SQL Server VSS Writer 服務是否已啟動，且啟動帳號設定為 Local System。

  • 請在執行 SQL Server 的伺服器上，以提高權限的命令提示字元執行 SqlServerWriter 命令，確認是否列出 VSSADMIN LIST WRITERS。 寫入者必須在場且處於 穩定 狀態，VSS 備份才能成功完成。

  • 如需更多資訊，請查閱備份軟體及廠商支援網站的日誌。

    徵兆或案例 參考
    瞭解 VDI 備份的運作方式 運作原理：SQL Server - VDI（VSS）備份資源
    同時可以備份多少個資料庫 運作方式：可以同時備份多少個資料庫？

啟用變更追蹤時，備份會失敗

問題

當你在資料庫啟用變更追蹤時，備份可能會失敗。 你可能會看到像以下這樣的錯誤：

錯誤：3999，嚴重性：17，狀態：1。
<時間戳記> spid <spid> 因錯誤 2601，無法將 dbid 8 中的認可資料表排清到磁碟。 請檢查錯誤記錄檔，以取得詳細資訊。

Resolution

如果你在支援的 SQL Server 版本遇到此問題，請安裝你版本的最新累積更新。 關於背景與歷史修正，請參閱以下條目：

恢復加密資料庫備份時的錯誤

問題

當你還原由透明資料加密（TDE）保護的資料庫備份時，會遇到問題。

Resolution

要解決此問題，請參見將受 TDE 保護的資料庫移至另一個 SQL Server。

關於 SQL Server 備份與還原的常見問題

如何檢查備份作業的狀態？

用 estimate_backup_restore 腳本來估算備份時間。

如果 SQL Server 在備份過程中發生故障轉移，我該怎麼辦？

請依照 重新啟動中斷的還原作業 (Transact-SQL) 中的說明，重新啟動還原或備份作業。

我可以把舊版本的資料庫備份還原到新版本，反之亦然嗎？

你無法用比建立備份版本更早的 SQL Server 版本來還原 SQL Server 備份。 欲了解更多資訊，請參閱 RESTORE 相容性支援。

我該如何檢查我的 SQL Server 資料庫備份？

請參閱 RESTORE 陳述句 - VERIFYONLY （Transact-SQL） 中所記錄的程序。

如何取得 SQL Server 中資料庫的備份歷程記錄？

請參閱 如何在 SQL Server 中取得資料庫的備份歷程記錄。

我可以在 64 位元伺服器上還原 32 位元備份，反之亦然嗎？

是。 SQL Server 的磁碟儲存格式在 64 位元與 32 位元環境中相同。 因此，備份與還原作業可跨越 64 位元與 32 位元環境運作。

我該如何備份並還原一個受透明資料加密（TDE）保護的資料庫？

備份資料庫、資料庫加密金鑰的伺服器憑證，以及憑證的私鑰。 若要在另一個實例上還原備份，先將伺服器憑證（連同其私鑰）還原到 master 目標實例的資料庫，然後還原使用者資料庫備份。 如需逐步指引，請參見將受 TDE 保護的資料庫移至另一個 SQL Server。

備份壓縮在啟用 TDE 的資料庫上有效嗎？

是。 自 2016 SQL Server 起，當你在 MAXTRANSFERSIZE 陳述中指定 BACKUP 大於 65536（64 KB）時，備份壓縮在啟用 TDE 的資料庫上有效。 沒有這個設定，即使你請求壓縮，備份也會未壓縮。 詳情請參見 備用壓縮。

VDI 和 VSS 備份如何與 Always On 可用性群組的次要副本互動？

透過 SQL Writer 服務執行的 VSS 型（快照）備份，僅支援對主要複本進行。 在次要副本上，請透過 VDI 用戶端要求執行僅複製完整備份，因為不支援針對次要副本執行 VSS 完整備份。 欲了解更多資訊，請參閱 作用中的次要複本：在次要複本上備份 (Always On 可用性群組)。

一般疑難排解提示

  • 在你寫備份的資料夾上，授予 SQL Server 服務帳號 Read 和 Write 權限。 如需詳細資訊，請參閱備份權限。
  • 檢查你寫備份的資料夾是否有足夠的空間放資料庫備份。 利用 sp_spaceused 儲存程序來大致估算資料庫的備份大小。
  • 使用最新版本的 SSMS，以避免與作業及維護計畫設定相關的已知問題。
  • 先測試你的工作，確認備份是否成功建立。 為檢查備份加入邏輯。
  • 如果你打算將系統資料庫從一台伺服器移到另一台，請檢視 「移動系統資料庫」。
  • 如果你看到間歇性備份失敗，請檢查你的 SQL Server 版本最新更新是否能解決問題。 欲了解更多資訊，請參閱 SQL Server 版本與更新 。
  • 若要為 SQL Server Express 版本排程並自動執行備份，請參閱 在 SQL Server Express 中排程並自動執行 SQL Server 資料庫備份。

SQL Server 備份與還原的參考主題

下表列出了針對特定備份與還原任務需要檢視的主題。

文章 描述
BACKUP （Transact-SQL） 回答關於備份的基本問題，並提供不同類型的備份與還原操作範例。
備份裝置（SQL Server） 一個了解備份裝置、備份到網路共享、Azure Blob 儲存體 及相關任務的參考資料。
復原模式 (SQL Server) 詳細介紹簡單、完整及批量記錄的復原模型，並說明復原模型如何影響備份。
備份與還原系統資料庫 （SQL Server） 涵蓋你在系統資料庫備份與還原操作時的策略與考量。
還原與復原概觀 (SQL Server) 涵蓋恢復模式如何影響還原作業。 如果你對資料庫的復原模型如何影響還原過程有疑問，請參考這篇文章。
管理在另一部伺服器上提供資料庫時所需的中繼資料 當你移動資料庫或遇到影響登入、加密、複製、權限等問題時，需要注意的事項。
交易記錄備份 （SQL Server） 介紹如何在完整與批量登錄恢復模型中備份與還原（套用）交易日誌的概念。 說明如何進行例行的交易日誌備份以恢復資料。
SQL Server 受管理備份至 Microsoft Azure 介紹管理備份及相關程序。

相關內容

  • 上次更新時間：