SQL Server 利用交易紀錄拯救資料

今天出了一個大包,大概是從寫程式以來最大的問題了。

查詢備份檔案

要利用交易紀錄拯救資料,首先必須要有一個完整的備份 bak ,再來需要有從完整備份開始的不中斷交易紀錄,這樣就可以利用來拯救交易紀錄期間所有資料庫異動。

SELECT 
a.[database_name] as '資料庫名稱',
f.physical_device_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.dbo.backupmediafamily f
INNER JOIN msdb..backupset a ON f.media_set_id = f.media_set_id
INNER JOIN master..sysdatabases b ON
a.database_name COLLATE DATABASE_DEFAULT =
b.name COLLATE DATABASE_DEFAULT
where
a.database_name = 'DatabaseName'
ORDER BY
a.database_name, a.backup_finish_date

在結果之中會顯示每一次備份的紀錄,還有記錄到的紀錄序號,紀錄序號一定要連續不中斷的,一中斷只能說事情大條了。

還原資料庫

接下來我們利用 bak 以及  trn 檔案來還原資料庫,先還原 bak 的完整備份

image

image

先別急著按下確定,這時候我們點下指令碼,把介面替你做掉的 T-SQL 撈出來看看,應該會類似下面這段。

RESTORE DATABASE [Test20120401] 
FROM DISK = N'C:2012_20120309.bak' WITH FILE = 1,
MOVE N'test2012' TO N'C:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLDATATest20120401.mdf',
MOVE N'test2012_log' TO N'C:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLDATATest20120401_1.ldf',
NORECOVERY, NOUNLOAD, STATS = 10
GO

接下來還原 trn 的部分
image
選擇至某個時間點一樣點下指令碼,接下來讓兩段指令碼合併

RESTORE DATABASE [Test2012] 
FROM DISK = N'C:2012_20120309.bak' WITH FILE = 1,
MOVE N'test2012' TO N'C:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLDATATest20120401.mdf',
MOVE N'test2012_log' TO N'C:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLDATATest20120401_1.ldf',
NORECOVERY, NOUNLOAD, STATS = 10
GO
RESTORE LOG [Test2012]
FROM DISK = N'C:2012_20120309120405_190000_2012_TRN.trn' WITH FILE = 1,
NORECOVERY, NOUNLOAD, STATS = 10,
STOPAT = N'2012-04-03T22:40:00'
GO

藉由這種方式,我們可以更改變數還有還原時間點還原出不同時間點的資料庫

image

比較資料庫差異性

再來就是利用工具比較還原的資料庫,我們就可以找到每一段區間中間的變動,新增或是刪除的資料列。在這邊我用的工具是 Visual Studio 的 Data Compare

Data > Data Compare > New Data Comparison

image

在這邊的可以選擇來源資料庫跟比較資料庫,Only in Source 或是 Only in Target 也可以單純過濾說被刪除/被新增的資料,選擇完比較的兩邊之後就可以直接開始比較的動作。

image

可以看到我這邊指定了 Only in Source ,各有兩個 Table 少了 20 跟 42 筆資料,點到那一個 Table 的時候下方就會列出差異性的資料,或是選擇上方 Export To Editor 可以匯出將資料塞回 Table 的 T-SQL

image

總結

當然系統安安穩穩的不需要做還原最好,但是誰也不知道哪一天會出大災難,這次的事件真的是學了一次教訓。

發表迴響

你的電子郵件位址並不會被公開。 必要欄位標記為 *