USE master
GO
-- 創建測試數據庫
CREATE DATABASE db_test GO
-- 對數據庫進行備份
BACKUP DATABASE db_test
TO DISK = 'c:/db_test.bak'
WITH FORMAT
GO
-- 創建測試表
CREATE TABLE db_test.dbo.tb_test(
??? ID int)
-- 延時 1 秒鐘,再進行后面的操作(這是由于SQL Server的時間精度最大為百分之三秒,不延時的話,可能會導致還原到時間點的操作失敗)
WAITFOR DELAY '00:00:01'
GO
-- 假設我們現在誤操作刪除了 db_test.dbo.tb_test 這個表
DROP TABLE db_test.dbo.tb_test
GO
--在刪除操作后,發現不應該刪除表 db_test.dbo.tb_test
--下面演示了如何恢復這個誤刪除的表 db_test.dbo.tb_test
--首先,備份事務日志(使用事務日志才能還原到指定的時間點)
BACKUP LOG db_test
TO DISK = 'c:/db_test_log.bak'
WITH FORMAT
GO
-- 獲取可嘗試的時間范圍
IF OBJECT_ID(N'tempdb..#') IS NOT NULL
??? DROP TABLE #
CREATE TABLE #(
??? BackupName nvarchar(128) ,
??? BackupDescription nvarchar(255) ,
??? BackupType smallint ,
??? ExpirationDate datetime ,
??? Compressed tinyint ,
??? Position smallint ,
??? DeviceType tinyint ,
??? UserName nvarchar(128) ,
??? ServerName nvarchar(128) ,
??? DatabaseName nvarchar(128) ,
??? DatabaseVersion int ,
??? DatabaseCreationDate datetime ,
??? BackupSize numeric(20,0) ,
??? FirstLSN numeric(25,0) ,
??? LastLSN numeric(25,0) ,
??? CheckpointLSN numeric(25,0) ,
??? DatabaseBackupLSN numeric(25,0) ,
??? BackupStartDate datetime ,
??? BackupFinishDate datetime ,
??? SortOrder smallint ,
??? CodePage smallint ,
??? UnicodeLocaleId int ,
??? UnicodeComparisonStyle int ,
??? CompatibilityLevel tinyint ,
??? SoftwareVendorId int ,
??? SoftwareVersionMajor int ,
??? SoftwareVersionMinor int ,
??? SoftwareVersionBuild int ,
??? MachineName nvarchar(128) ,
??? Flags int ,
??? BindingID uniqueidentifier ,
??? RecoveryForkID uniqueidentifier ,
??? Collation nvarchar(128) ,
??? FamilyGUID uniqueidentifier ,
??? HasBulkLoggedData bit ,
??? IsSnapshot bit ,
??? IsReadOnly bit ,
??? IsSingleUser bit ,
??? HasBackupChecksums bit ,
??? IsDamaged bit ,
??? BeginsLogChain bit ,
??? HasIncompleteMetaData bit ,
??? IsForceOffline bit ,
??? IsCopyOnly bit ,
??? FirstRecoveryForkID uniqueidentifier ,
??? ForkPointLSN numeric(25,0) NULL,
??? RecoveryModel nvarchar(60) ,
??? DifferentialBaseLSN numeric(25,0) NULL,
??? DifferentialBaseGUID uniqueidentifier ,
??? BackupTypeDescription nvarchar(60) ,
??? BackupSetGUID uniqueidentifier NULL
)
INSERT # EXEC(N'
RESTORE HEADERONLY
FROM DISK = ''c:/db_test.bak''
WITH FILE = 1
RESTORE HEADERONLY
FROM DISK = ''c:/db_test_log.bak''
WITH FILE = 1
')
--SELECT
--??? *
--FROM #
-- 定義嘗試的時間范圍，以及嘗試時間點的間隔
DECLARE
??? @start_date datetime,
??? @stop_date datetime,
??? @try_step_millsecond int,
??? @msg nvarchar(1000)
SELECT
??? @start_date = MIN(BackupFinishDate),? -- 嘗試查找誤刪除數據的開始時間
??? @stop_date = MAX(BackupFinishDate),?? -- 嘗試查找誤刪除數據的結束時間
??? @try_step_millsecond = 500??????????? -- 每 500 毫秒為一個時間點找一次數據
FROM #
-- 還原完全備份
RESTORE DATABASE db_test
FROM DISK = 'c:/db_test.bak'
WITH REPLACE,
??? NORECOVERY
-- 還原日志備份到各點，以尋找出所需數據
WHILE @start_date < @stop_date
BEGIN
??? SELECT
??????? @start_date = DATEADD(ms, @try_step_millsecond, @start_date),
??????? @msg = N'嘗試時間點: ' + CONVERT(varchar(50), @start_date, 121)
??? RAISERROR(@msg, 10, 1) WITH NOWAIT
??? BEGIN TRY
??????? -- 還原日志到指定的點，并通過 STANDBY 使數據庫能只讀訪問
??????? RESTORE LOG db_test
??????? FROM DISK = 'c:/db_test_log.bak'
??????? WITH STOPAT = @start_date,
??????????? STANDBY = 'c:/db_test_redo.bak'
??????? -- 檢查需要的數據是否存在
??????? IF OBJECT_ID(N'db_test.dbo.tb_test') IS NOT NULL
??????? BEGIN
??????????? -- 顯示還原的時間點
??????????? SELECT Restoreto = @start_date
??????????? -- 完成數據庫還原，使數據庫可讀寫
??????????? RESTORE LOG db_test
??????????? WITH RECOVERY
??????????? SELECT
??????????????? @start_date = @stop_date
??????? END
??? END TRY
??? BEGIN CATCH
??? END CATCH
END
GO
--最后刪除測試環境
DROP DATABASE db_test
DROP TABLE #
