[SQL] 還原至某時間點

被告知要救回誤刪資料，透過在測試機上還原至某時間點後，再從測試機把資料更新至正式機上，該篇紀錄還原至特定時間點作法

建立 Lab 環境

use AdventureWorks2017
go 

-- Step1：建立 tblRestore
CREATE TABLE [dbo].[tblRestore](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[InsertDateTime] [datetime] NULL,
	CONSTRAINT [PK_tblRestore] PRIMARY KEY CLUSTERED 
	( [ID] ASC ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

-- Step2：新增 5 筆資料
INSERT INTO tblRestore (InsertDateTime) VALUES(getdate())
GO 5

-- Step3：進行完整備份
BACKUP DATABASE AdventureWorks2017 TO DISK = N'D:\RestoreDemo.bak'

-- Step4：間隔一段時間，再新增 5 筆資料
INSERT INTO tblRestore (InsertDateTime) VALUES(getdate())
GO 5

-- Step5：進行第一次交易備份
BACKUP LOG AdventureWorks2017 TO DISK = N'D:\RestoreDemo.bak'

-- Step6：刪除全部資料，並記錄刪除時間 2021-11-26T23:15:35.0595818+08:00
DELETE FROM tblRestore

-- Step7：進行第二次交易備份
BACKUP LOG AdventureWorks2017 TO DISK = N'D:\RestoreDemo.bak'

-- Step8：透過語法來確定 bak 檔案內容
RESTORE HeaderOnly FROM DISK = N'D:\RestoreDemo.bak'

用 RESTORE HeaderOnly 可以查詢 bak 內的備份紀錄，下圖則使用 SSMS 還原功能，把備份紀錄從 bak 內顯示出來，方便閱讀

還原流程

筆記就沒有進行結尾備份，直接下參數 replace 處理囉

use master
GO

-- Step1：切換單人模式
ALTER DATABASE AdventueWorks2017 SET SINGLE_USER WITH ROLLBACK IMMEDIATE

-- Step2：還原 File = 1 完整備份
RESTORE DATABASE [AdventureWorks2017] FROM DISK = N'D:\RestoreDemo.bak'
	WITH FILE = 1 , 
		NORECOVERY , 
		REPLACE

-- Step3：還原 File = 2 第一次交易備份
RESTORE DATABASE [AdventureWorks2017] FROM DISK = N'D:\RestoreDemo.bak'
	WITH FILE = 2 , 
		NORECOVERY

-- Step4：還原 File = 3 第二次交易備份，並指定還原時間點
RESTORE DATABASE [AdventureWorks2017] FROM DISK = N'D:\RestoreDemo.bak'
	WITH FILE = 3 , 
		RECOVERY , 
		STOPAT = '2021-11-26 23:15:00'

-- Step5：切換多人模式
ALTER DATABASE [AdventureWorks2017] SET MULTI_USER WITH ROLLBACK IMMEDIATE

查詢 Table 內資料就可以看見全部資料都回來囉

 

官方文件範例

在官方文件上，看見下圖範例，觀念上是最後一個 Log 才指定 stopat 參數並進行 recovery，測試該範例用法，是每一個 Log 都指定 stopat 參數，一樣能夠正常還原的

 

• 參考資料
• 資料庫還原
• [SQL SERVER]如何利用交易紀錄檔還原到某一時間點