使用fn_dblog讀取資料庫的LOG檔

問題：同事想要知道資料庫何時進行新增、更新與刪除的動作，而我們的資料庫沒有設定CDC等機制..，所以只好從log檔下手了。

解決方法：使用fn_dblog function去讀取sql server的log檔，藉由讀取log檔可以知道資料庫何時做新增和更新資料的動作。

步驟：

1.新增資料到table後，使用fn_dblog讀取新增資料的TransactionID。

2.找出新增的TransactionID後，利用TransactionID查詢新增資料的Transaction行為，由下圖可以知道資料庫何時對TestDBLogTable進行新增資料的動作。

3.更新資料後，使用fn_dblog讀取更新資料的TransactionID。

4.找出更新資料的TransactionID後，利用TransactionID查詢更新資料的Transaction行為，由下圖可以知道資料庫何時對TestDBLogTable進行更新資料的動作。

5.刪除資料後，使用fn_dblog讀取刪除資料的TransactionID。

6. 找出刪除資料的TransactionID後，利用TransactionID查詢刪除資料的Transaction行為，由下圖可以知道資料庫何時對TestDBLogTable進行刪除資料的動作。

結論：使用fn_dblog function去讀取只能讀到DML的做作與時間相關資料，如果要知道改變的內容需要把BINARY的資料轉成STRING，不過轉成STRING的過程有點複雜，最好還是使用CDC或SNAPSHOT會比較方便，另外使用DBCC LOG也可以看LOG檔。

使用RESTORE DATABASE 搭配STOPAT將資料庫還原到特定的時間點

問題：今天因為應用程式的bug造成某些資料庫資料異常，同事請我幫忙把異常資料回復資料正確的時間點。

解決：出問題的資料庫沒有任何的DATA TRACING，還好有完整的LOG檔，這時候要找到正確的資料就要使用RESTORE DATABASE 搭配STOPAT。

步驟：

1.建立測試資料庫
--建立測試的DATABASE

CREATE DATABASE TestRestoreDatabase

GO

USE TestRestoreDatabase;

GO

2.確認還原模式為FULL:資料庫的還原模式不可以為SIMPLE。

--設定資料庫的還原模式為FULL

ALTER DATABASE TestRestoreDatabase SET RECOVERY FULL

--確認DB的還原模式為FULL

SELECT [name] AS [DatabaseName],

CONVERT(SYSNAME, DATABASEPROPERTYEX(N''+ [name] + '', 'Recovery')) AS [RecoveryModel]

FROM master.dbo.sysdatabases

WHERE [name]='TestRestoreDatabase'

ORDER BY name

3.建立測試的Table和測試資料

--建立測試Table

CREATE TABLE TestRestoreTable

(

NID INT IDENTITY(1,1),

NAMES VARCHAR(30),

DATES DATETIME

)

--建立測試資料

INSERT TestRestoreTable VALUES ('RYO',GETDATE())

SELECT * FROM TestRestoreTable

4. 備份資料庫

--備份資料庫TestRestoreDatabase

BACKUP DATABASE TestRestoreDatabase

TO DISK = 'D:\TestRestoreDatabase.Bak'

WITH INIT

INIT 覆寫原來的備份檔

GO

5.新增測試資料

--新增第一筆資料

INSERT TestRestoreTable VALUES ('WILLIAM',GETDATE())

SELECT * FROM TestRestoreTable

-新增第二筆資料

INSERT TestRestoreTable VALUES ('RYOLIU',GETDATE())

SELECT * FROM TestRestoreTable

6.備份LOG檔

--備份LOG檔

BACKUP LOG TestRestoreDatabase

TO DISK = 'D:\TestRestoreDatabaseLog1.trn'

WITH INIT;

GO

7.還原資料庫

--還原資料庫，資料庫名稱為newTestRestoreTable

RESTORE DATABASE newTestRestoreDatabase

FROM  DISK = N'D:\TestRestoreDatabase.Bak' WITH

   MOVE N'TestRestoreDatabase' TO N'D:\TestRestoreDatabase.mdf'

,  MOVE N'TestRestoreDatabase_log' TO N'D:\TestRestoreDatabase_log.LDF'

,  STANDBY = N'D:\ROLLBACK_UNDO_NewTestRestoreDatabase.BAK'

,  NOUNLOAD,  STATS = 10

GO

說明：

MOVE TO:將MDF與LDF檔移動到不同的地方，MOVE N'TestRestoreDatabase' TO N'D:\TestRestoreDatabase.mdf'就表示把TestRestoreDatabase的MDF檔案從預設的還原位置移到D:\ 。

STANDBY:讓資料庫成唯讀狀態，也就是可以SELECT該資料庫。

STATS:顯示資料庫的還原進度，STATS = 10表示以10%的進度顯示還原進度。

--查看資料

SELECT * FROM newTestRestoreDatabase.dbo.TestRestoreTable

GO

8.還原資料庫到特定的時間點

--使用LOG檔還原到特定的時間點2011-08-24 12:33:30.000

RESTORE LOG newTestRestoreDatabase

FROM DISK = N'D:\TestRestoreDatabaseLog.BAK'

WITH STANDBY = N'D:\TestRestoreDatabase.bak',

STATS = 10, STOPAT = '2011-08-24 12:33:30.000'

GO

--查看資料

SELECT * FROM newTestRestoreDatabase.dbo.TestRestoreTable

9.還原資料庫到特定的時間點

--使用LOG檔還原到特定的時間點2011-08-24 12:34:30.000

RESTORE LOG newTestRestoreDatabase

FROM DISK = N'D:\TestRestoreDatabaseLog1.TRN'

WITH STANDBY = N'D:\ROLLBACK_UNDO_NewTestRestoreDatabase.BAK',

STATS = 10, STOPAT = '2011-08-24 12:34:30.000'

GO

--查看資料

SELECT * FROM newTestRestoreDatabase.dbo.TestRestoreTable

結論：如果資料庫沒有CDC、Replication等…其他機制，對於操作失誤而導致刪除或更新資料時時想救回特定時間點的資料使用RESTORE DATABASE 搭配STOPAT是不錯的選擇。

如何檢視DB的log檔並且壓縮

--為保險起見，要先備份DB的log檔
--查看recovery_model
SELECT recovery_model_desc FROM sys.databases WHERE name = 'CustomLogging'
--設定查看recovery_model為SIMPLE
ALTER DATABASE CustomLogging SET recovery SIMPLE
--查看修改後的recovery_model
SELECT recovery_model_desc FROM sys.databases WHERE name = 'CustomLogging'
--查看硬碟空間
EXEC xp_fixeddrives
-- 記下尚未壓縮LOG檔之前的LOG檔大小
EXEC sp_helpdb CustomLogging
-- 壓縮LOG檔
DBCC shrinkfile(CustomLogging_log, 0)
-- 記下壓縮LOG檔之前的LOG檔大小
EXEC sp_helpdb CustomLogging
--查看硬碟空間
EXEC xp_fixeddrives