通過日誌恢複SQL Server的曆史資料

園子裡前段時間發過一篇通過日誌恢複MSSQL資料例子 ，我總結一下

通過日誌還原，最重要的是：

1.必須有一個完整的備份，且這個備份必須是在修改、刪除資料之前做的。

2.在更新、刪除資料之後，做記錄備份，該log備份將用於還原之前的資料

 

下面步驟的目的：還原被刪除的表

在SQL Server Management可視化操作步驟：

1.建立資料庫並完整備份：

--建立測試資料庫
CREATE DATABASE Db
GO

--對資料庫進行備份
BACKUP DATABASE Db TO DISK='c:\db.bak' WITH FORMAT
GO

 

2.建立一個空表

--建立測試表
CREATE TABLE Db.dbo.TB_test(ID int)

3.刪除剛剛建的這個空表，假設這個表被誤刪除了

--假設我們現在誤操作刪除了 Db.dbo.TB_test 這個表
DROP TABLE Db.dbo.TB_test

到了這一步，我們想還原被刪除的TB_Test表，這個時候，記住刪除表之前的時間，後面會用到

這個時候，需要備份日誌，可以用SQL Management介面操作備份，也可以用T-SQL備份

BACKUP LOG Db TO DISK='c:\db_log.bak' WITH FORMAT
GO

 

4.還原資料庫，可以替換原來資料庫，或者還原成一個新的資料庫DB1,這裡新的資料庫DB1

如果是介面操作：

“任務”- “還原” - “資料庫”：

在“常規”選擇頁中的裝置後面選擇我們之前的完整備份：db.bak,

目標資料庫：DB1

在“恢複狀態”下選擇第2項“不對資料庫執行任何操作，不復原提交的事務。。” ，點確定後，可以看到DB1資料庫的狀態變成“正在還原。。”

接下來：在DB1資料庫上點右鍵--還原--交易記錄，選擇剛剛我們刪除表後備份的交易記錄：db_log.bak，

在下面選擇時間點，這個時間是上面我們記住的刪除表之前的時間，點擊確定後，還原成功。可以看到被刪除的表又回來了。

 

完整的指令碼：

--建立測試資料庫
CREATE DATABASE Db
GO

--對資料庫進行備份
BACKUP DATABASE Db TO DISK='c:\db.bak' WITH FORMAT
GO

--建立測試表
CREATE TABLE Db.dbo.TB_test(ID int)

--延時1秒鐘,再進行後面的操作(這是由於SQL Server的時間精度最大為百分之三秒,不延時的話,可能會導致還原到時間點的操作失敗)
WAITFOR DELAY '00:00:01'
GO

--假設我們現在誤操作刪除了 Db.dbo.TB_test 這個表
DROP TABLE Db.dbo.TB_test

--儲存刪除表的時間
SELECT dt=GETDATE() INTO #
GO

--在刪除操作後,發現不應該刪除表 Db.dbo.TB_test

--下面示範了如何恢複這個誤刪除的表 Db.dbo.TB_test

--首先,備份交易記錄(使用交易記錄才能還原到指定的時間點)
BACKUP LOG Db TO DISK='c:\db_log.bak' WITH FORMAT
GO

--接下來,我們要先還原完全備份(還原日誌必須在還原完全備份的基礎上進行)
RESTORE DATABASE Db FROM DISK='c:\db.bak' WITH REPLACE,NORECOVERY
GO

--將交易記錄還原到刪除操作前（這裡的時間對應上面的刪除時間，並比刪除時間略早
DECLARE @dt datetime
SELECT @dt=DATEADD(ms,-20,dt) FROM #  --擷取比表被刪除的時間略早的時間
RESTORE LOG Db FROM DISK='c:\db_log.bak' WITH RECOVERY,STOPAT=@dt
GO

--查詢一下,看錶是否恢複
SELECT * FROM Db.dbo.TB_test

/*--結果:
ID          
----------- 

（所影響的行數為 0 行）
--*/

--測試成功
GO

--最後刪除我們做的測試環境
DROP DATABASE Db
DROP TABLE #

 

本文代碼參考csdn的文章：http://topic.csdn.net/u/20080321/12/f5d33007-9bea-43f1-844b-914e68de727d.html