備份與還原 SQL Server 資料庫

適用於：SQL Server

本文說明備份 SQL Server 資料庫的好處，介紹基本的備份與還原術語，並涵蓋備份與還原策略及 SQL Server 的安全考量。

SQL Server 備份與還原元件為您的 SQL Server 資料庫中的關鍵資料提供必要的保障。 為降低災難性資料遺失的風險，請定期備份資料庫，以保留資料的修改。 規劃周詳的備份與還原策略有助於保護資料庫免於因多種故障類型造成的資料遺失。 先恢復一組備份，然後恢復資料庫，測試你的策略，這樣你就能隨時應對災難。

除了本地儲存外，SQL Server 也支援從 Azure Blob 儲存體 備份與還原。 如需詳細資訊，請參閱使用 Azure Blob 儲存體 進行 SQL Server 備份和還原。 針對使用 Azure Blob 儲存體服務儲存的資料庫檔案，SQL Server 2016 (13.x) 的 Azure 快照集選項，提供近乎即時的備份及更快速的還原。 如需詳細資訊，請參閱 Azure 中資料庫檔案的檔案快照集備份。 Azure 也針對在 Azure VM 中執行的 SQL Server 提供企業級備份解決方案。 完全受控備份解決方案，其支援 Always On 可用性群組、長期保留、時間點復原，以及集中管理和監視。 如需詳細資訊，請參閱 關於 Azure VM 上的 SQL Server 備份。

為什麼要備份？

• 備份您的 SQL Server 資料庫、執行備份測試還原程序，並將備份副本存放於安全且異地的地點，能保護您免於可能的災難性資料遺失。 備份是保護資料的的唯一方法。

  使用有效的資料庫備份，就可以從多種失敗中復原資料，例如：

  • 媒體錯誤。

  • 使用者錯誤，例如不小心刪除資料表。

  • 硬體故障 (例如，磁碟機損壞或伺服器永久損毀)。

  • 天然災害。 透過使用 SQL Server 備份到 Azure Blob 儲存體，你可以在與本地部署不同區域建立一個異地備份，以備天災影響本地部署時使用。

• 此外，資料庫備份對於例行管理很有用，例如，將資料庫從一部伺服器複製到另一部伺服器、設定 Always On 可用性群組或資料庫鏡像，以及封存。

備份詞彙表

備份與還原策略

你必須針對環境和可用資源自訂備份與還原策略。 可靠的復原需要備份與還原策略。 設計良好的策略能在企業對最大資料可用性與最小資料遺失的需求，與維護及儲存備份的成本之間取得平衡。

備份和還原策略包含備份部分與還原部分。 備份部分定義了備份的類型與頻率、所需硬體的類型與速度、如何測試備份，以及備份媒體的存放地點與方式（包括安全考量）。 還原部分定義了誰負責執行還原、如何執行還原以達成資料庫可用性與最小資料遺失目標，以及如何測試還原。

有效的備份與還原策略需要謹慎規劃、實施與測試。 需要測試。 只有當你成功還原還原策略中包含的每一種備份組合，並測試每個還原資料庫的物理一致性時，你才會有備份策略。 請考慮多項因素，包括：

• 貴組織關於生產資料庫的目標，特別是可用性和保護資料免於遺失或損壞的需求。

• 每一個資料庫的本質：其大小、使用模式、內容本質及資料需求等等。

• 資源的限制，例如硬體、人員、儲存備份媒體的空間、儲存媒體的實體安全性等等。

最佳做法建議

不要給執行備份或還原操作的帳號超過必要的權限。 欲了解更多資訊，請參閱 備份 與 還原 以了解具體權限細節。 加密資料庫備份，並在可能的情況下進行壓縮。

使用一致的檔案副檔名，讓備份更容易辨識和管理。 SQL Server 不要求或強制這些擴充功能，但一致性有助於操作任務，例如為備份檔案設定防毒排除。 欲了解更多資訊，請參閱 「配置防毒軟體以配合 SQL Server 運作」。

• 資料庫備份檔案應該要有副檔名。.BAK
• 記錄備份檔應使用 .TRN 副檔名。

使用獨立儲存空間

將資料庫備份放在與資料庫檔案不同的實體位置或裝置上。 當存放資料庫的實體硬碟故障或當機時，恢復取決於你能否存取存放備份的獨立硬碟或遠端裝置。 你可以從同一顆實體磁碟機建立多個邏輯磁碟區或分割區。 在選擇備份儲存位置前，請仔細檢視磁碟分割區和邏輯卷的配置。

選擇適當的復原模式

備份和還原作業是在復原模式的內容中進行。 復原模式是控制交易記錄管理方式的資料庫屬性。 因此，資料庫的復原模型決定了資料庫支援哪些類型的備份與還原情境，以及其交易日誌備份的大小。 一般而言，資料庫會使用完整復原模式或簡單復原模式。 您可以在執行大量作業前，先切換為大量記錄復原模式，以補強完整復原模式。 如需這些復原模型的簡介，以及它們如何影響交易記錄管理，請參閱 交易記錄。

資料庫復原模式的最佳選擇取決於您的業務需求。 若要避免管理交易記錄，並簡化備份和還原，請使用簡單復原模式。 若要以管理額外負擔為代價，將工作遺失的風險降到最低，請使用完整復原模式。 為了在批量記錄作業中減少日誌大小的影響，同時仍允許恢復這些作業，請使用批量記錄恢復模型。 關於復原模型對備份與還原的影響，請參閱備份概述（SQL Server）。

設計備份策略

在您選擇符合特定資料庫業務需求的復原模式後，規劃並實施相應的備份策略。 最佳的備用策略取決於多項因素。 以下因素尤其重要：

• 應用程式每天需要多少小時才能存取資料庫？

  如果有可預測的非尖峰時段，你應該在那段時間安排完整的資料庫備份。

• 可能發生變更和更新的頻率為何？

  如果變動頻繁，請考慮：

  • 在簡單的復原模型下，你可以在完整資料庫備份之間排程差分備份。 差異備份只會擷取最後一次完整資料庫備份之後所發生的變更。

  • 在完整復原模式下，你可以排程頻繁的日誌備份。 在完整備份之間排定差異備份，可減少您在還原資料後必須還原的記錄備份數目，從而減少還原時間。

• 變更可能只會發生在資料庫的一小部分，還是很大一部分？

  對於大型資料庫，且變更集中在部分檔案或檔案群組中，部分備份或完整檔案備份是有用的。 如需詳細資訊，請參閱部分備份（SQL Server）和完整文件備份（SQL Server）。

• 完整的資料庫備份需要多少磁碟空間？

• 您的企業需要保留多久以前的備份資料？

  確保你有一個符合應用程式需求與業務需求的妥善備份時程。 隨著備份老化，除非你有辦法重新生成所有資料直到故障點，否則資料遺失的風險會增加。 在因儲存空間限制而丟棄舊備份之前，請考慮是否需要在那麼久以前的備份中進行復原。

估計完整資料庫備份的大小

在實施備份與還原策略之前，先估算完整資料庫備份使用多少磁碟空間。 備份作業會將資料庫中的資料複製到備份檔中。 備份只包含資料庫中的實際資料，沒有未使用的空間。 因此，備份通常會比資料庫本身還小。 要估算完整資料庫備份的大小，請使用 sp_spaceused 系統儲存程序。 如需詳細資訊，請參閱 sp_spaceused。

排程備份

備份操作對執行交易的影響很小，所以你可以在一般操作中執行備份。 您可以執行 SQL Server 備份，並且將實際執行工作負載受到的影響降至最低。

在決定需要哪種備份類型以及執行頻率後，將定期備份納入資料庫維護計畫。 如需有關維護計畫以及如何為資料庫備份和記錄備份建立這些計畫的詳細資訊，請參閱＜ Use the Maintenance Plan Wizard＞。

測試您的備份

在測試備份之前，您沒有還原策略。 透過將每個資料庫的副本還原到測試系統上，徹底測試其備份策略。 您必須測試還原您要使用的每個備份類型。 備份還原後，對資料庫執行 DBCC CHECKDB 確認備份媒體沒有損壞。

驗證媒體穩定性和一致性

使用備份工具提供的驗證選項（BACKUPT-SQL 指令、SQL Server 維護計畫、您的備份軟體或解決方案等）。 舉例請參見 RESTORE 陳述 - VERIFYONLY。

使用進階功能，例如 BACKUP CHECKSUM 偵測備份媒體本身的問題。 如需更多資訊，請參閱備份與還原期間可能發生的媒體錯誤 (SQL Server)。

文件備份/還原策略

記錄你的備份與還原程序，並將文件副本保存在你的運行簿中。

你也應該為每個資料庫維護一本操作手冊。 本作業手冊應記錄備份的位置、備份裝置名稱（如有）及還原測試備份所需的時間。

從不受信任來源還原備份的安全風險

本節概述了從不受信任的來源還原備份至各種 SQL Server 環境（包括內部部署、Azure SQL 受控實例、Azure 虛擬機（VM）上的 SQL Server 以及其他環境）所產生的安全風險。

為什麼這很重要

如果備份來源不受信任，還原 SQL 備份檔案.bak（）會帶來潛在風險。 當 SQL Server 環境擁有多個實例時，安全風險進一步加劇，因為威脅範圍會被放大。 雖然保持在受信任邊界內的備份不會造成安全問題，但還原惡意備份可能會危及整個環境的安全。

惡意 .bak 檔案可以：

• 接管整個 SQL Server 實例。
• 提升權限並取得對底層主機或虛擬機的未經授權存取權。

此攻擊發生在任何驗證腳本或安全檢查執行前，極具危險性。 恢復不受信任的備份，等同於在關鍵伺服器或虛擬機上執行不受信任的應用程式，並在環境中引入任意的程式碼執行。

最佳做法

請遵循以下備份安全最佳實務，以降低對 SQL Server 環境的威脅：

• 將恢復備份視為高風險作業。
• 透過使用隔離實例來減少威脅服務區域。
• 只允許受信任的備份：切勿還原來自未知或外部來源的備份。
• 只允許留在受信任邊界內的備份：確保備份來自受信任邊界內。
• 請勿為了方便而繞過安全控制。
• 啟用 伺服器層級稽核 ，以捕捉備份與還原事件，並減少稽核逃避。

使用 XEvent 監視進度

由於資料庫規模龐大且操作複雜，備份與還原操作可能耗時較長。 當任一操作出現問題時，利用 backup_restore_progress_trace 擴展事件即時監控進度。 如需延伸事件的詳細資訊，請參閱 延伸事件概觀。

-- Create the backup_restore_progress_trace extended event session
CREATE EVENT SESSION [BackupRestoreTrace] ON SERVER
ADD EVENT sqlserver.backup_restore_progress_trace
ADD TARGET package0.event_file (SET filename = N'BackupRestoreTrace')
WITH
(
    MAX_MEMORY = 4096 KB,
    EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
    MAX_DISPATCH_LATENCY = 5 SECONDS,
    MAX_EVENT_SIZE = 0 KB,
    MEMORY_PARTITION_MODE = NONE,
    TRACK_CAUSALITY = OFF,
    STARTUP_STATE = OFF
);
GO

-- Start the event session
ALTER EVENT SESSION [BackupRestoreTrace] ON SERVER
STATE = START;
GO

-- Stop the event session
ALTER EVENT SESSION [BackupRestoreTrace] ON SERVER
STATE = STOP;
GO

擴充事件的範例輸出

深入了解備份工作

• 建立維護計劃
• 在 SQL Server Management Studio 中建立 SQL Server 代理程式作業
• 設定 SQL Server 代理程式作業的排程

使用備份裝置和備份媒體

• 定義磁碟檔案的邏輯備份裝置 （SQL Server）
• 定義磁帶機的邏輯備份裝置 （SQL Server）
• 指定磁碟或磁帶備份目的地 （SQL Server）
• 刪除備份裝置 （SQL Server）
• 設定備份的到期日 （SQL Server）
• 檢視備份磁帶或檔案的內容 （SQL Server）
• 檢視備份集中的資料和記錄檔 （SQL Server）
• 檢視邏輯備份裝置的屬性和內容 （SQL Server）
• 從裝置還原備份 （SQL Server）

建立備份

對於部分備份或僅複製備份，請分別使用具有 COPY_ONLY 或 BACKUP 選項的 Transact-SQL PARTIAL 陳述式。

使用 SSMS

• 建立完整的資料庫備份
• 備份交易記錄
• 備份檔案與檔案群組
• 建立差異資料庫備份 （SQL Server）

使用 T-SQL

• 使用 Resource Governor 來透過備份壓縮來限制 CPU 使用率
• 資料庫損毀時備份交易記錄檔 （SQL Server）
• 在備份或還原期間啟用或停用備份總和檢查碼 （SQL Server）
• 指定備份或還原以在錯誤後繼續或停止

還原備份資料

使用 SSMS

• 使用 SSMS 還原資料庫備份
• 將資料庫還原至新位置 （SQL Server）
• 還原差異資料庫備份（SQL Server）
• 還原檔案與檔案群組（SQL Server）

使用 T-SQL

• 在簡單復原模型下還原資料庫備份
• 將資料庫還原至失敗點 - 完整復原
• 覆寫現有檔案來還原檔案及檔案群組（SQL Server）
• 將檔案還原到新位置（SQL Server）
• 還原主資料庫

還原交易記錄 （完整復原模型）

使用 SSMS

• 將資料庫還原至已標記的交易 （SQL Server Management Studio）
• 還原交易記錄備份 （SQL Server）
• 將 SQL Server 資料庫還原到某個時間點（完整復原模型）

使用 T-SQL

• 將 SQL Server 資料庫還原到某個時間點（完整復原模型）
• 重新啟動中斷的還原作業
• 在不還原資料的情況下恢復資料庫

相關內容

• 備份概觀 （SQL Server）
• 還原和復原概觀 （SQL Server）
• BACKUP （Transact-SQL）
• RESTORE 陳述式（Transact-SQL）
• Analysis Services 資料分析服務資料庫的備份與還原
• 備份並還原全文目錄與索引
• 備份並還原複製的資料庫
• 交易記錄
• 復原模型 （SQL Server）
• 媒體集、媒體系列和備份集 （SQL Server）