使用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是不錯的選擇。