共用方式為

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

  • 發行項

本文提供您在 Microsoft SQL Server 備份和還原作業期間可能會遇到之常見問題的解決方案，並提供這些作業的進一步資訊參考。

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

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

備份和還原作業會需要大量 I/O。 備份/還原輸送量取決於基礎 I/O 子系統的優化程度，以處理 I/O 磁碟區。 如果您懷疑備份作業已停止或花費太長的時間才能完成，您可以使用下列一或多個方法來估計完成時間，或追蹤備份或還原作業的進度：

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

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

  • 在 SQL Server 2016 和更新版本中，您可以使用 XEvent backup_restore_progress_trace 來追蹤備份和還原作業的進度。

  • 您可以使用 percent_complete sys.dm_exec_requests 資料行來追蹤進行中的備份和還原作業進度。

  • 您可以使用和 Backup/Restore throughput/sec 性能監視器計數器來Device throughput Bytes/sec測量備份和還原輸送量資訊。 如需詳細資訊，請參閱 SQL Server 備份裝置物件。

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

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

檢查事項

  1. 檢查您是否遇到下表所列的任何已知問題。 請考慮您是否應該實作變更，或套用對應文章中所討論的修正和最佳做法。

    知識庫或在線叢書連結 說明和建議的動作
    優化 SQL Server 中的備份和還原效能 《在線叢書》主題涵蓋各種最佳做法，可用來改善備份/還原作業的效能。 例如，您可以將特殊許可權指派 SE_MANAGE_VOLUME_NAME 給執行 SQL Server 的 Windows 帳戶，以啟用資料檔的立即初始化。 這會產生顯著的效能提升。
    2920151 Windows Server 2012 R2 型故障轉移叢集的建議 Hotfix 和更新

    2822241 Windows 8 和 Windows Server 2012 更新匯總：2013 年 4 月    		 目前的系統匯總可能包含系統層級已知問題的修正程式，以降低 SQL Server 等程式的效能。 安裝這些更新有助於防止這類問題。
    2878182 FIX：應用程式中的使用者模式進程在執行 Windows Server 2012 的伺服器上沒有回應

    		備份作業需要大量 I/O，而且可能會受到這個 Bug 的影響。 套用此修正程式以協助防止這些問題。
    設定防病毒軟體以使用 SQL Server 防病毒軟體可能會鎖定.bak檔案。 這可能會影響備份和還原作業的效能。 請遵循本文中的指引，從病毒掃描中排除備份檔。
    當您嘗試存取已不存在於 Windows 中的共享資料夾時，2820470延遲的錯誤訊息 討論當您嘗試存取 Windows 2012 和更新版本中已不存在的共享資料夾時所發生的問題。
    967351 NTFS 磁碟區中大量分散的檔案可能無法成長超過特定大小 討論NTFS檔系統大量分散時所發生的問題。
    當您備份大型系統磁碟區時，304101備份程序失敗
    2455009修正：如果您在 SQL Server 2005、SQL Server 2008 或 SQL Server 2008 R2 的事務歷史記錄檔內有許多 VDF，復原資料庫時效能變慢 許多虛擬記錄檔的存在可能會影響還原資料庫所需的時間。 在還原作業的復原階段，這特別如此。 如需可能由許多 VDF 存在所造成的其他可能問題的相關信息，請參閱 資料庫作業需要很長的時間才能完成，或當事務歷史記錄檔有許多虛擬記錄檔時觸發錯誤。
    備份或還原作業至網路位置的速度很慢 嘗試從執行 SQL Server 的伺服器，將大小類似的檔案複製到網路位置，以將問題隔離至網路。 確認效能。

  2. 檢查 SQL Server 錯誤記錄檔和 Windows 事件記錄檔中是否有錯誤訊息，以取得有關問題原因的更多指標。

  3. 如果您使用第三方軟體或資料庫維護計劃來執行同時備份，請考慮是否應該變更排程，以將備份寫入的磁碟驅動器上的爭用降到最低。

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

影響不同 SQL Server 版本之間資料庫還原的問題

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

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

使用下列方法，將裝載在較新版本 SQL Server 上的資料庫複製到舊版 SQL Server。

注意

下列程式假設您有兩個 SQL Server 實例名為 SQL_A （較高版本） 和 SQL_B （較低版本）。

  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 環境中遇到影響備份作業或維護計劃的問題，請注意下列事項：

  • 根據預設，自動備份喜好設定會設定為 [偏好次要]。 這會指定備份應該發生在次要複本上，但主要複本是在線唯一的複本除外。 您無法使用此設定來取得資料庫的差異備份。 若要變更此設定，請使用您目前主要複本上的 SSMS，然後流覽至可用性群組的 [屬性] 底下的 [備份喜好設定] 頁面。
  • 如果您使用維護計劃或排程工作來產生資料庫的備份，請務必為每個裝載可用性群組可用性複本的伺服器實例上，為每個可用性資料庫建立作業。

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

如果您收到指出檔案問題的錯誤訊息，這是損毀備份文件的問題。 以下是備份集損毀時可能會取得的一些錯誤範例：

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

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

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

注意

您可以使用 Restore Header 語句來檢查備份。

這些問題可能會因為影響基礎硬體（硬碟、網路記憶體等）或與病毒或惡意代碼相關的問題而發生。 檢閱 Windows 系統事件記錄檔和硬體記錄中是否有回報的錯誤，並採取適當的動作（例如升級韌體或修正網路問題）。

若要避免這些錯誤，請在執行備份時啟用 Backup CHECKSUM 選項，以避免備份損毀的資料庫。 如需詳細資訊，請參閱備份和還原期間可能的媒體錯誤（SQL Server）。

您也可以啟用追蹤旗標 3023，以在使用備份工具執行備份時啟用總和檢查碼。 如需詳細資訊，請參閱如果備份公用程式未公開選項，如何 啟用 CHECKSUM 選項。

若要修正這些問題，您必須找到另一個可使用的備份檔，或建立新的備份集。 Microsoft不提供任何可協助從損毀備份集擷取數據的解決方案。

注意

如果備份檔在一部伺服器上成功還原，但不在另一部伺服器上還原，請嘗試不同的方式在伺服器之間複製檔案。 例如，請嘗試 robocopy，而不是一般複製作業。

備份因為許可權問題而失敗

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

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

    伺服器 <伺服器名稱>的備份失敗。 （Microsoft.SqlServer.SmoExtended）
    System.Data.SqlClient.SqlError：無法開啟備份裝置 '<device name>'。 操作系統錯誤 5（拒絕 Access。）。 （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 服務啟動帳戶的內容下執行。 因此，如果服務帳戶沒有必要的許可權，您會收到先前記下的錯誤訊息。

如需詳細資訊，請參閱備份裝置。

注意

您可以瀏覽至 對應資料夾屬性中的 [安全性 ] 索引標籤，選取 [進階 ] 按鈕，然後使用 [有效存取 ] 索引標籤，來檢查資料夾上 SQL Service 帳戶的目前許可權。

使用第三方備份應用程式的備份或還原作業失敗

SQL Server 提供虛擬備份裝置介面 （VDI） 工具。 此 API 可讓獨立軟體廠商將 SQL Server 整合到其產品中，以提供備份和還原作業的支援。 這些 API 是設計來提供最大的可靠性和效能，並支援 SQL Server 備份和還原功能的完整範圍。 這包括完整範圍的快照集和熱備份功能。

一般疑難排解步驟

更多資源

運作方式：可以同時備份多少個資料庫？

其他問題

徵兆/案例 補救動作或其他資訊
如果在資料庫上啟用變更追蹤，並傳回類似下列的錯誤，備份可能會失敗：

「錯誤：3999，嚴重性：17，狀態：1。

<時間戳>spid spid <> 無法將認可數據表排清到 dbid 8 中的磁碟，因為錯誤 2601。 如需詳細資訊，請檢查錯誤記錄檔。」


 請參閱下列Microsoft知識庫文章：
還原加密資料庫備份的問題 將 TDE 保護的資料庫移至另一個 SQL Server
嘗試從 Enterprise Edition 還原 CRM 備份失敗 還原 Microsoft Dynamics CRM 資料庫時發生「無法在此版 SQL Server 中啟動資料庫」錯誤2567984

SQL Server 備份和還原作業的常見問題

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

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

如果 SQL Server 在備份中間故障轉移，該怎麼辦？

每個 重新啟動中斷的還原作業 （Transact-SQL） 重新啟動還原或備份作業。

我可以從較新版本的舊程式版本還原資料庫備份，反之亦然？

SQL Server 備份無法使用比建立備份版本還晚的 SQL Server 版本來還原。 如需詳細資訊，請參閱 相容性支援。

如何? 驗證 SQL Server 資料庫備份嗎？

請參閱 RESTORE 語句 - VERIFYONLY （Transact-SQL） 中所述的程式。

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

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

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

是。 64 位和 32 位環境中的 SQL Server 磁碟上記憶體格式相同。 因此，備份和還原作業可在64位和32位環境中運作。

一般疑難排解提示

  • 請務必將讀取和寫入許可權布建至要寫入備份之資料夾中的 SQL Server 服務帳戶。 如需詳細資訊，請參閱備份權限。
  • 請確定要寫入備份的資料夾有足夠的空間來容納您的資料庫備份。 您可以使用預 sp_spaceused 存程式來取得特定資料庫的備份大小粗略估計。
  • 請務必使用最新版本的 SSMS，以確保您不會遇到任何與作業和維護計劃設定相關的已知問題。
  • 執行作業的測試回合，以確定已成功建立備份。 一律新增邏輯以驗證您的備份。
  • 如果您打算將系統資料庫從一部伺服器移至另一部伺服器，請檢閱 移動系統資料庫。
  • 如果您發現間歇性備份失敗，請檢查您是否遇到 SQL Server 版本最新更新中已修正的問題。 如需詳細資訊，請參閱 SQL Server 版本和更新。
  • 若要排程和自動化 SQL Express 版本的備份，請參閱在 SQL Server Express 中檢查 SQL Server 資料庫的排程和自動備份。

SQL Server 備份和還原作業的參考主題

  • 如需備份和還原作業的詳細資訊，請參閱《在線叢書》中的下列主題：

    「SQL Server 資料庫的備份和還原」：本主題涵蓋 SQL Server 資料庫的備份和還原作業概念、提供其他主題的連結，並提供執行各種備份或還原工作的詳細程式（例如驗證備份，以及使用 T-SQL 或 SSMS 備份）。 這是 SQL Server 檔中此主題的父主題。

  • 下表列出您可能想要檢閱與備份和還原作業相關的特定工作的其他主題。

    參考 描述
    BACKUP (Transact-SQL) 提供與備份相關的基本問題的解答。 提供不同類型的備份和還原作業範例。
    備份裝置 (SQL Server) 提供絕佳的參考，讓您了解各種備份裝置、備份至網路共用、Azure Blob 記憶體和相關工作。
    復原模式 (SQL Server) 詳細說明各種恢復模式：簡單、完整和大容量日誌。 提供恢復模式如何影響備份的資訊。
    備份與還原：系統資料庫 （SQL Server） 涵蓋策略，並討論您在處理系統資料庫的備份和還原作業時必須注意的內容。
    還原和復原概觀 (SQL Server) 涵蓋恢復模式如何影響還原作業。 如果您有資料庫恢復模式如何影響還原程序的問題，您應該檢閱此問題。
    管理在另一部伺服器上提供資料庫時所需的中繼資料 移動資料庫時，或遇到任何影響登入、加密、複寫、許可權等問題時，您應該注意的各種考慮。
    使用事務歷史記錄備份 說明如何在完整和大容量日誌恢復模式中備份和還原事務歷史記錄（套用）的概念。 說明如何進行事務歷史記錄（記錄備份）的例行備份來復原數據。
    SQL Server 受控備份到 Microsoft Azure 介紹受控備份和相關聯的程式。