SQL Server 資料庫備份與還原


  1. 備份
    1. 備份目標
    2. 備份類型 Backup Type
      1. 完整備份
      2. 差異備份
      3. 記錄檔備份
    3. 備份集資訊 (Backup Sets Informatino)
    4. 復原模式 Recovery Model
      1. 簡易復原模式
      2. 完整復原模式
      3. 大量紀錄復原模式
    5. 覆寫媒體 Overwrite media
    6. 可靠性 Reliability
  2. 還原
    1. 還原選項 Restore options
    2. 復原狀態
    3. 資料庫卡住在「正在還原」狀態
  3. 參考資料

筆記資料庫備份與還原的規劃與執行 🎁
持續編輯中 🧙‍♂️

SQL Server Logo

備份

備份目標

  • RPO, Recovery Point Objective 可以接受的資料損失程度
  • PTO, Recovery Time Objecttive 可以接受的服務中止時間

備份類型 Backup Type

  • Full 完整備份
  • Differential 差異備份
  • Log Backup 記錄檔備份

每周日進行完整備份,每天進行差異備份,每六個小時進行記錄檔備份。

備份計畫示意圖

完整備份

備份資料檔以及備份期間所產生的交易紀錄,同時也是差異備份以及交易紀錄備份的還原基準。備份所需的時間較長,且需要較大的空間,僅能還原為任一紀錄點,不同於記錄檔備份可以還原為任一個瞬點 (point-in-time recovery)。

差異備份

備份 mdf 檔中自上一次完整備份到目前為止的資料異動。

記錄檔備份

備份上一次交易紀錄備份,到目前為止的交易資料。

觀念釐清:SQL Server 完整備份、差異備份、交易記錄備份
淺談SQL Server的備份類型

備份集資訊 (Backup Sets Informatino)

備份進行後,會於 msdb 系統資料庫留下相關資訊,並保存於下列的系統資料表:

  • backupfile : 備份集的 ID、檔案大小、邏輯名稱以及實體路徑
  • backupfilegroup : 備份資料庫的檔案群組資訊
  • backupset : 每次備份動作相關的資訊,例如備份名稱、描述、作業時間、檔案大小等
  • backupmediaset
  • backupmediafamily

復原模式 Recovery Model

復原模式是資料庫的屬性,用於控制資料庫「交易的紀錄方式」、允許「備份交易紀錄」以及可用的還原類型。

簡易復原模式

簡單模式無備份交易紀錄優點是節省空間的使用,但只能復原到備份結束的時間點,且不支援需要交易記錄備份的作業,包括下列功能:
  1. 記錄傳送
  2. AlwaysOn 或資料庫鏡像
  3. 無資料遺失的媒體復原
  4. 時間點還原

完整復原模式

保存所有的交易紀錄,因此可以復原至任意時間點,除非碰到紀錄結尾毀損必須要重做該次交易紀錄以後的變更;缺點是占用儲存空間。

大量紀錄復原模式

完整復原模式的輔助,允許執行高效能的大量複製作業。針對大多數的大量作業使用最少記錄,以減少記錄空間的使用量

淺談SQL Server備份基本概念
復原模式 (SQL Server)
如何利用交易紀錄檔還原到某一時間點
MS Doc - 快速入門:備份與還原內部部署的 SQL Server 資料庫
MS Doc - Restore a Database Backup Using SSMS

覆寫媒體 Overwrite media

  • 附加至現有
  • 覆寫至現有
  • 檢查媒體名稱及備份組是否逾期

可靠性 Reliability

  • 完成後驗證備份
  • 寫入媒體前執行總和檢查碼
  • 發生錯誤時繼續

還原

還原選項 Restore options

  • With Replace
  • With Keep_Replication
  • With Restricted_User

還原資料庫 (選項頁面)

復原狀態

  • Restore With Recovery
  • Restore With NoRecovery
  • Restore With StandBy

資料庫卡住在「正在還原」狀態

使用下列 TSQL 進行恢復

RESTORE DATABASE AdventureWorksLT2019
WITH RECOVERY
GO

參考資料

SQL還原問題排除 (記憶體不足、限制的使用者)