[SQL SERVER][Maintain]如何利用交易紀錄檔還原到某一時間點

2010-05-30

有時User沒睡飽、眼花或手滑誤刪資料，這時如果資料庫有完整備份含相關交易紀錄檔的話，

那我們就可以利用交易記錄檔來還原資料到還沒刪除的時間點，

這裡實作記錄一下。

資料庫初始狀態(復原模式須為完整才可還原到某一時間點)

--1.Create Database MYDEMO

CREATE DATABASE MYDEMO;

GO

USE MYDEMO

--完整復原模式

ALTER DATABASE MYDEMO SET RECOVERY FULL

GO

--2.Create Table

CREATE TABLE MYTBL

    C1       INT,

    C2        varchar(10),

    Time      DATETIME

GO

INSERT INTO dbo.mytbl VALUES (1,'a', GETDATE()),

                         (2,'b', GETDATE()),

                         (3,'c', GETDATE())

--4. 完整資料庫備份

BACKUP DATABASE MYDEMO TO DISK = 'E:\backup\BK_Full_20100530.bak'

   WITH COMPRESSION

GO

INSERT INTO dbo.mytbl VALUES (4,'d', GETDATE())

GO

--5. First交易記錄檔備份

BACKUP LOG MYDEMO TO DISK = 'E:\backup\BK_Log_20100530_1.trn'

    WITH COMPRESSION

GO

INSERT INTO dbo.mytbl VALUES (5,'e', GETDATE())

GO

-- Second交易記錄檔備份

BACKUP LOG MYDEMO TO DISK = 'E:\backup\BK_Log_20100530_2.trn'

   WITH COMPRESSION

GO

Table 資料。

這時某個User誤刪資料。

全砍了。

由於我們備份兩個交易紀錄檔(First and Second)，

所以我們先利用第一個交易紀錄檔還原到4筆資料的時間點。(當然你也可取最後時間點)

--切換單人模式

ALTER DATABASE MYDEMO SET SINGLE_USER WITH ROLLBACK IMMEDIATE

--結尾交易紀錄檔備份

BACKUP LOG MYDEMO

TO DISK = 'E:\backup\BK_Log_20100530_TAIL_LOG.trn'

WITH NORECOVERY, COMPRESSION

GO

擷取尚未備份的記錄檔記錄。如果復原點已包含在較早的記錄備份中，或者您要移動或取代 (覆寫) 資料庫，

就不需要有結尾記錄備份，而且不需要將它還原至最近備份之後的某個時間點。

--開始還原

RESTORE DATABASE MYDEMO

  FROM DISK = 'E:\backup\BK_Full_20100530.bak'

  WITH NORECOVERY

GO

--Restore from Transaction Log (First)

RESTORE LOG MYDEMO

FROM  DISK = 'E:\backup\BK_Log_20100530_1.trn'

WITH RECOVERY, STOPAT = '2010-05-30 11:48:00.000'

GO

--Use RECOVERY option

RESTORE DATABASE MYDEMO WITH RECOVERY

GO

--切換多人模式

ALTER DATABASE MYDEMO SET MULTI_USER WITH ROLLBACK IMMEDIATE

確認資料

資料回來了，但只有4筆。

接下來我們還原到最後一個時間點。

再度全砍了。

--切換單人模式

ALTER DATABASE MYDEMO SET SINGLE_USER WITH ROLLBACK IMMEDIATE

--Restore from Transaction Log (first and second)

RESTORE LOG MYDEMO

  FROM DISK = 'E:\backup\BK_Log_20100530_1.trn'

  WITH NORECOVERY

GO

RESTORE LOG MYDEMO

  FROM DISK = 'E:\backup\BK_Log_20100530_2.trn'

  WITH NORECOVERY

GO

套用交易紀錄檔(First and Second)。

--Restore from Transaction Log (Tail)

RESTORE LOG MYDEMO

FROM  DISK = 'E:\backup\BK_Log_20100530_TAIL_LOG.trn'

WITH RECOVERY, STOPAT = '2010-05-30 11:49:00.000'

GO

套用結尾記錄檔成功後，這時資料庫以還原成功。

確認資料

全部的資料又救回來了。

如果你想確認備份集相關資訊，可利用RESTORE HEADERONLY來確認

但如果你有玩過Oracle Flashback，相信你一定會愛上Flashback，個人覺得同樣是還原到某一時間點，

只是Oracle Flashback操作更簡單，功能更強大。

RiCo技術農場