上個月參加SQL PASS的九月份聚會，講師許致學老師分享了很多種備份還原的Solution。其中一種還原方式是可以指定資料庫還原到特定交易時間，很是有趣。筆記一下
首先先用下面的Script腳本建立Demo環境
Create Database [DemoDB]
GO
Use [DemoDB]
GO
--建立資料表
Create Table Employee(
[ID] int identity Primary Key Clustered,
[Name] nvarchar(10),
[Title] nvarchar(10),
Salary int
)
GO
--寫入資料
Insert Into Employee Values('張三','經理',50000),('李四','主任',40000),('王五','副理',45000)
GO
--完整備份
Backup Database [DemoDB] To Disk='E:\SQLBak\DemoDB\Full.bak'
GO
--第一次交易備份
Backup Log [DemoDB] To Disk='E:\SQLBak\DemoDB\log1.bak'
GO
WaitFor Delay '00:00:20'
GO
--更新資料
Begin Tran [Tran1] With Mark--交易名稱為Tran1
Update Employee Set Salary = Salary + 1000
Commit Tran
GO
WaitFor Delay '00:00:20'
GO
Begin Tran [Tran1] With Mark---交易名稱為Tran1
Update Employee Set Salary = Salary + 1000
Commit Tran
GO
--第二次交易備份
Backup Log [DemoDB] To Disk='E:\SQLBak\DemoDB\log2.bak'
GO
上述的Script腳本中我建立一張Employee資料表，塞入3筆資料並給定基本薪資。然後做一個完整備份及一個交易紀錄備份，再開啟一個名為Tran1的交易並Update Employee中每個人的薪資加薪1000。過20秒後再開啟一個交易Tran2同樣Update Employee中每個人的薪資加薪1000。然後再做一次交易紀錄備份。(執行結果如下圖)
我們再開啟交易時有加 With Mark 關鍵字，此時SQL就會記錄下本次交易的相關資訊。這些資料會放在msdb..logmarkhistory資料表中。如下所示剛剛我們的兩個交易的相關資料都被記錄在資料表中。
下圖我們先展示STOPBEFOREMARK，我們順利將資料庫還原至Tran1這一個交易之前。由Select出的資料可以看得出所有員工都尚未加薪。
下圖是STOPATMARK還原後的結果，可經由大家都加薪1000元的結果得知該DB還原至Tran1的交易時間點
如果交易的命名都相同呢 ? SQL會不會搞不清楚我們想要還原的時間點呢 ? 下圖紅色圈選處，我將兩個交易使用相同的命名Tran1。
msdb..logmarkhistory資料表中可以看到兩個交易都是Tran1，那如何讓SQL知道我們要還原到哪一個Tran1呢 ? 此時就要搭配時間參數了(如右邊圈紅色選處)。
加上時間參數的語法如下圖紅色圈選處 STOPATMARK = 'Tranaction Name' AFTER 'datetime' 。下圖測試中我們告訴SQL要還原至 2016/10/6 00:39:25後的第一個名為Tran1的交易點。所以很順利還原至大家都加薪1000的交易點囉。
我是ROCK
rockchang@mails.fju.edu.tw
