我之前一直認為 SQL Server 完整備份會截斷交易記錄，其實不然，昨天與 德瑞克 稍微聊了一下也就此釐清了長久以來的錯誤觀念，雖然這點小東西影響不了日常的備份作業，但對於 SQL Server 備份觀念的釐清也會對資料庫還原時更有信心，今天為了驗證觀念正確也做了一些小實驗，以下是一些關於 SQL Server 備份還原的觀念整理與心得筆記。
完整備份
- 同時會備份 資料檔 ( *.mdf ) 與 交易記錄檔 ( *.ldf )
- 要做任何差異備份或交易記錄備份之前，一定要做一次完整備份
差異備份
- 只會備份 資料檔 ( *.mdf )，但僅有最近一次完整備份到備份當下這段時間所異動的資料
- 差異備份不會備份任何交易記錄檔，備份的內容只會拿目前資料檔與最近一次完整備份的資料內容做差異比對
交易記錄檔備份
- 只會備份 交易記錄檔 ( *.ldf )
- 若透過 SSMS 操作，在預設的情況下會自動截斷交易記錄
- 交易記錄備份的 紀錄序號 (LSN) 是從資料庫建立開始就一直連貫的
如何證明上述觀念正確？
透過查詢 msdb 的 backupset 系統資料表可以查詢出所有 備份組 (backupset) 的備份歷史紀錄，請參考以下 T-SQL 語法：
SELECT
a.[database_name] as '資料庫名稱',
CASE a.[type]
WHEN 'D' THEN N'資料庫'
WHEN 'I' THEN N'差異資料庫'
WHEN 'L' THEN N'紀錄'
WHEN 'F' THEN N'檔案或檔案群組'
WHEN 'G' THEN N'差異檔案'
WHEN 'P' THEN N'部分'
WHEN 'Q' THEN N'差異部分'
ELSE N'NULL'
END as '備份類型',
a.[first_lsn] as '備份組中第一個LSN',
a.[last_lsn] as '備份組之後下一個LSN',
a.[database_backup_lsn] as '最近的完整資料庫備份之LSN',
a.[differential_base_lsn] as '差異備份的基底 LSN',
a.[backup_finish_date] as '備份完成的日期和時間'
FROM
msdb..backupset a INNER JOIN master..sysdatabases b ON
a.database_name COLLATE DATABASE_DEFAULT
= b.name COLLATE DATABASE_DEFAULT
ORDER BY
a.database_name, a.backup_finish_date透過下圖執行結果 (點圖可放大)，很容易能理解備份時交易記錄的連貫特性：
第一次交易記錄備份時的備份組中第一個或最舊的紀錄序號(LSN)是一致的，代表交易記錄備份是從整個資料庫的「第一筆交易資料開始備份」：
接著我們再看看該資料庫所有交易紀錄備份的中 LSN 完全是連貫的，所以完整備份或差異備份都不會影響交易記錄備份的 LSN 連慣性：
資料庫如何依序還原？
基本上 SQL Server 有能力將資料庫的資料還原到任何一個資料庫備份的時間點，假設你每一分鐘備份一次交易記錄的話，若你的資料庫發生毀損，透過這些備份資料還原最多只會遺失 1 分鐘以內的資料，如果你想還原到 3 天前的特定時間，也可以透過交易記錄備份還原到特定的備份時間點。
由於交易記錄備份的 LSN 不會中斷的關係，你甚至可以從第一次完整備份開始逐一將交易記錄還原到任意時間點 (使用 RESTORE 可以搭配 STOPAT | STOPATMARK | STOPBEFOREMARK 等參數來還原)，但若資料庫存取量非常大或資料庫已運行多年，可能會導致交易記錄備份檔非常佔據硬碟空間，從實務上來講通常不會保留這麼完整的交易記錄備份，但對於資料完整性非常要求的單位（如：金融業）就有可能被要求要保留完整的交易記錄資料。
為了加快資料還原的速度，我們通常不會從第一次完整備份開始逐一將交易記錄備份檔還原，而會透過最近一次的完整備份檔來還原資料庫，甚或透過完整備份與差異備份的機制縮短交易記錄備份還原的時間，一般來說有以下幾種資料還原策略：
完整備份還原 –> 交易記錄備份還原
- 先還原完整備份
- 再還原完整備份後所做的交易記錄備份
完整備份還原 –> 差異備份還原 –> 交易記錄備份還原
- 先還原完整備份
- 再還原差異備份
- 再還原差異備份後所做的交易記錄備份
備註：只要交易記錄備份的紀錄序號能夠連貫，SQL Server 就可以透過交易記錄備份還原資料。
以下是一個實務上常用的資料庫備份策略：
- 每月第一天執行一次完整備份 ( AM 3:00 )
- 每個星期天執行一次差異備份 ( AM 4:00 )
- 每 15 分鐘執行一次交易記錄備份
假設你想將資料庫還原到 2010/4/21 PM 9:00 的版本，你就必須會用以下順序還原資料庫：
- 利用 2010/4/1 AM 3:00 的完整備份還原資料庫
- 再還原 2010/4/18 (日) AM 4:00 的差異資料備份
- 再還原所有 2010/4/18 (日) AM 4:00 之後做的的交易紀錄備份
還原時的注意事項：
還原的過程中在最後一個備份檔還原之前都必須保持 不回復為認可的交易 (NORECOVERY) 的狀態：
關於查詢所有 備份組 (backupset) 的備份歷史紀錄，以下是較完整的 T-SQL 語法，相關欄位可以參考《Microsoft SQL Server 線上叢書》對於 backupset (Transact-SQL) 的說明：
SELECT
a.[database_name] as '資料庫名稱',
CASE a.[type]
WHEN 'D' THEN N'資料庫'
WHEN 'I' THEN N'差異資料庫'
WHEN 'L' THEN N'紀錄'
WHEN 'F' THEN N'檔案或檔案群組'
WHEN 'G' THEN N'差異檔案'
WHEN 'P' THEN N'部分'
WHEN 'Q' THEN N'差異部分'
ELSE N'NULL'
END as '備份類型',
a.[name] as '備份組的名稱',
a.[first_lsn] as '備份組中第一個或最舊的記錄序號',
a.[last_lsn] as '備份組之後下一個記錄的記錄序號',
a.[database_backup_lsn] as '最近的完整資料庫備份之記錄序號',
a.[differential_base_lsn] as '差異備份的基底 LSN',
a.[backup_finish_date] as '備份作業完成的日期和時間',
a.[backup_size] as '備份組的大小 (以位元組為單位)'
FROM
msdb..backupset a INNER JOIN master..sysdatabases b ON
a.database_name COLLATE DATABASE_DEFAULT =
b.name COLLATE DATABASE_DEFAULT
ORDER BY
a.database_name, a.backup_finish_date
心得分享
其實我對 IT 工作一直有個體悟，由於我們不可能看完所有 MSDN 或 TechNet 文件，看過也不一定會記得，所以有些工作就算你真的在實務上知道怎麼做也不見得自己理解的觀念是對的，所以保持著一顆虛懷若谷的心還真的蠻重要的，不過該有自信的時候千萬不要畏縮！
像在我們公司的工作環境裡隨時充滿著挑戰，每個人隨時都可以挑戰你的觀念，連老闆也不例外，挑戰成功可能還有意外驚喜，例如有免費中餐之類的 XD，如果有邏輯說不通的、理論與實務不符的地方、沒效率的作法、無法清楚表達的抽象概念、……等等，雖時都可以提出自己的看法與意見，為了追求技術的真理我們毫不手軟，所以在公司內部我也經常與同事討論、甚至是辯論一些大家看法不太一致或不太確定的地方，為的就是讓每個人對於特定知識的抽象概念能有一致且正確的理解，另一方面也是加強初學者的自信心，因為無法理解抽象概念的人講話永遠比較小聲，當理解前因後果來龍去脈之後就會慢慢展現自信，相對的工作出錯的機率也會降低！
相關連結