SQL Server 資料庫的備份與還原

適用于:SQL Server

本文說明備份SQL Server資料庫、基本備份和還原條款的優點,並介紹備份與還原SQL Server和安全性考慮 SQL Server的備份與還原策略。

此文章介紹 SQL Server 備份。 如需備份 SQL Server 資料庫的特定步驟,請參閱建立備份

SQL Server 的備份和還原元件提供基本的防護措施,用以保護您的 SQL Server 資料庫中儲存的重要資料。 若要將重大資料遺失的風險降至最低,您必須備份資料庫,以定期保存您對資料所做的修改。 規劃完善的備份和還原策略有助於保護資料庫,以防止各種失敗所導致的資料遺失。 藉由還原一組備份並復原資料庫來測試您的策略,以做好有效因應災害的準備。

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

備份原因

  • 備份SQL Server資料庫、在備份上執行測試還原程式,並將備份複本儲存在安全的異地位置,可保護您免于發生重大資料遺失。 備份是保護資料的的唯一方法。

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

    • 媒體錯誤。
    • 使用者錯誤 (例如不小心卸除資料表)。
    • 硬體故障 (例如,磁碟機損壞或伺服器永久損毀)。
    • 天然災害。 藉由使用SQL Server備份Azure Blob 儲存體,您可以在與內部部署位置不同的區域中建立異地備份,以在影響內部部署位置的自然災害時使用。
  • 此外,資料庫的備份適用于例行的系統管理用途,例如將資料庫從一部伺服器複製到另一部伺服器、設定Always On可用性群組或資料庫鏡像,以及封存。

備份詞彙表

備份 [動詞]
從SQL Server資料庫複製資料記錄,或從其交易記錄複製記錄,以建立備份 [名詞] 的程式。

備份 [名詞]
失敗後可用來還原和復原資料的資料複本。 資料庫備份也可用來將資料庫的複本還原到新位置。

備份 裝置
寫入 SQL Server 備份並從中進行還原的磁碟或磁帶裝置。 SQL Server備份也可以寫入Azure Blob 儲存體,而URL格式可用來指定備份檔案的目的地和名稱。 如需詳細資訊,請參閱使用 Microsoft Azure Blob 儲存體SQL Server備份與還原

備份媒體
已寫入一或多個備份的一或多個磁帶或磁碟檔案。

資料備份
整個資料庫 (資料庫備份)、部分資料庫 (部分備份) 或是一組資料檔案或檔案群組 (檔案備份) 中資料的備份。

資料庫備份
資料庫的備份。 完整資料庫備份代表備份完成時的整個資料庫。 差異資料庫備份僅包含自其最近的完整資料庫備份以來,對資料庫所做的變更。

差異備份
一種資料備份,是以整個或部分資料庫或一組資料檔案或檔案群組 (差異基底) 的最新完整備份為基礎,而且只包含自該基底以來變更的資料。

完整備份
一種資料備份,包含特定資料庫或一組檔案群組或檔案中的所有資料,也包含足以讓這個資料復原的記錄。

記錄備份
交易記錄的備份,包含先前的記錄備份中未備份的所有記錄。 (完整復原模式)

recover
將資料庫回復為穩定且一致的狀態。

recovery
讓資料庫進入交易一致狀態的資料庫啟動階段或含復原之還原的階段。

復原模式
控制資料庫上交易記錄維護的資料庫屬性。 復原模式共有三種:簡單、完整和大量記錄。 資料庫的復原模式決定其備份和還原需求。

還原
多階段程式,會將所有資料和記錄頁從指定的SQL Server備份複製到指定的資料庫,然後套用記錄的變更來向前復原備份中記錄的所有交易,以在時間中轉送資料。

備份與還原策略

備份和還原資料作業必須依特定環境自訂,而且也必須使用可用的資源。 因此,以可靠的方式使用備份和還原來進行復原,需要備份和還原策略。設計良好的備份和還原策略,可以在最大資料可用性和最少資料遺失的商務需求間取得平衡,同時也將維護和儲存備份的費用納入考量。

備份和還原策略包含備份部分與還原部分。 策略的備份部分定義備份的類型和頻率、所需硬體的本質和速度、測試備份的方法,以及儲存備份媒體的位置與方法 (包括安全性考量)。 策略的還原部分定義了誰負責執行還原、如何執行還原以達成資料庫可用性並將資料損失降到最低的目標,以及如何測試還原。

設計有效的備份和還原策略需要仔細計畫、實作及測試。 測試是必要的:在利用還原策略中包含的所有備份組合順利完成還原,並測試還原的資料庫以取得實體一致性之後,您才算是擁有了備份策略。 您必須考慮各種因素。 其中包括:

  • 您組織對於生產資料庫的目標,特別是對資料可用性以及保護資料免於遺失或損壞的需求。

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

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

最佳做法建議

使用個別的儲存體

重要

請確認您將資料庫備份放置於與資料庫檔案不同的實體位置或裝置上。 當儲存資料庫的實體磁碟機故障或損毀時,復原能力取決於是否能存取儲存備份的個別磁碟機或遠端裝置以執行還原。 請記住,您可以在同一個實體磁碟機建立數個磁碟區或磁碟分割。 在選擇備份的儲存位置之前,請仔細了解磁碟分割和邏輯磁碟區配置。

選擇適當的復原模式

備份和還原作業是在復原模式的內容中進行。 復原模式是控制交易記錄管理方式的資料庫屬性。 因此,資料庫復原模式也將決定資料庫所支援的備份類型與還原案例,以及交易記錄備份的大小。 一般而言,資料庫會使用完整復原模式或簡單復原模式。 完整復原模式可以藉由在大量作業之前切換到大量記錄復原模式,來增強其功能。 如需這些復原模式及其對交易記錄管理之影響的簡介,請參閱 交易記錄 (SQL Server)

資料庫復原模式的最佳選擇視您的商務需求而定。 若要避免管理交易記錄,並簡化備份和還原,請使用簡單復原模式。 若要將遺失工作的風險降到最低 (但會耗用管理負擔成本),請使用完整復原模式。 在大量記錄作業期間,若要將對記錄大小的影響降至最低,同時允許對這些作業的復原能力,請使用大量記錄復原模式。 如需有關備份和還原之復原模式影響的資訊,請參閱備份概觀 (SQL Server)

設計備份策略

在為特定資料庫選擇符合商務需求的復原模式之後,您必須規劃並實作對應的備份策略。 最佳備份策略取決於各種因素,其中特別重要的是下列項目:

  • 應用程式每天必須花多少時間來存取資料庫?

    如果可預測離峰期間,則建議您排定該期間內完整資料庫備份的作業時程。

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

    如果經常變更,請考慮下列作法:

    • 在簡單復原模式下,請考慮在完整資料庫備份之間排程差異備份。 差異備份只會擷取最後一次完整資料庫備份之後所發生的變更。

    • 在完整復原模式下,您應排程經常的記錄備份。 在完整備份之間排定差異備份,可減少您在還原資料後必須還原的記錄備份數目,從而減少還原時間。

  • 變更可能會發生在資料庫的一小部分,還是資料庫的大部分?

    如果是大型資料庫,且其變更集中於檔案或檔案群組部分,則部分備份及 (或) 檔案備份可能十分有用。 如需詳細資訊,請參閱部份備份 (SQL Server) 完整檔案備份 (SQL Server)

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

  • 您的企業需要維護備份是多久以前的事了?

    請確認您已根據應用程式需求和商務需求,建立適當的備份排程。 當備份已過久,除非您有方法重新產生到失敗點之前的所有資料,否則資料遺失的風險會提升。 在您決定處置舊的備份之前,由於儲存體資源有限,請考量那麼久以前是否具有復原能力

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

在實作備份和還原策略前,您必須估計完整資料庫備份將使用多少磁碟空間。 備份作業會將資料庫中的資料複製到備份檔中。 備份僅包含資料庫中的實際資料,而不包含任何未使用的空間。 因此,備份通常會比資料庫本身還小。 您可以使用 sp_spaceused 系統預存程序來估計完整資料庫備份的大小。 如需詳細資訊,請參閱 sp_spaceused (Transact-SQL)

排程備份

執行備份作業對進行中的交易影響最小,所以可以在一般作業過程中執行備份作業。 您可以對生產工作負載執行SQL Server備份。

如需備份期間並行限制的相關資訊,請參閱備份概觀 (SQL Server)

決定您需要的備份類型以及執行每一類型所需的頻率之後,建議您將一般備份排程為資料庫之資料庫維護計畫的一部分。 如需有關維護計畫以及如何為資料庫備份和記錄備份建立這些計畫的詳細資訊,請參閱< Use the Maintenance Plan Wizard>。

測試備份!

在您完成備份的測試之前,還不能算是具備還原策略。 您務必要將資料庫副本還原到測試系統,以針對每個資料庫完整測試備份策略。 您必須測試還原您要使用的每個備份類型。 此外,也建議您在還原備份後透過資料庫的 DBCC CHECKDB 來執行資料庫一致性檢查,以確認備份媒體未損毀。

確認媒體穩定性和一致性

使用由備份公用程式 (BACKUP T-SQL 命令、SQL Server 維護計劃、備份軟體或解決方案等等) 所提供的驗證選項。 如需範例,請參閱 [RESTORE VERIFYONLY] (../t-sql/statements/restore-statements-verifyonly-transact-sql.md) 使用如備份總和檢查碼的進階功能,來偵測備份媒體本身的問題。 如需詳細資訊,請參閱在備份和還原期間可能發生的媒體錯誤 (SQL Server)

文件備份/還原策略

建議您寫下備份和還原程序,並將文件的副本保留在執行書中。 我們也建議您維護每個資料庫的作業手冊。 這份作業手冊應記載備份位置、備份裝置名稱 (如果有的話),以及還原測試備份所需的時間量。

使用 xEvent 監視進度

由於資料庫的大小和涉及作業的複雜度,備份和還原作業可能需要相當長的時間。 當任一項作業引發問題時,您可以使用 backup_restore_progress_trace 擴充事件來即時監視進度。 如需擴充事件的詳細資訊,請參閱擴充事件

警告

使用 backup_restore_progress_trace 擴充事件可能會造成效能問題,而且會耗用大量的磁碟空間。 請於短時間內使用、小心執行,並且在生產環境中實作之前徹底測試。

-- Create the backup_restore_progress_trace extended event esssion
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  

來自擴充事件的範例輸出

備份 xevent 輸出的範例還原 xevent 輸出範例

深入了解備份工作

使用備份裝置和備份媒體

建立備份

注意

針對部分或僅限複製的備份,您必須分別使用 Transact-SQLBACKUP 語句搭配 PARTIAL 或 COPY_ONLY 選項。

使用 SSMS

使用 T-SQL

還原資料備份

使用 SSMS

使用 T-SQL

還原交易記錄 (完整復原模式)

使用 SSMS

使用 T-SQL

詳細資訊和資源

Backup Overview (SQL Server)
還原和復原概觀 (SQL Server)
BACKUP (Transact-SQL)
RESTORE (Transact-SQL)
備份與還原 Analysis Services 資料庫
備份並還原全文檢索目錄與索引
備份及還原複寫的資料庫
交易記錄 (SQL Server)
復原模式 (SQL Server)
媒體集、媒體家族與備份組 (SQL Server)