بازیابی داده های حذف شده در SQL Server

مقاله بررسی بازیابی داده های حذف شده در SQL Server در این آموزش نشان می دهیم که چگونه داده های حذف شده در SQL Server را دوباره برگردانیم. به شما خواهیم گفت درمواقع حذف تصادفی و غیرعمدی داده ها چگونه آن ها را بازیابی نمایید. این کار روی نسخه Enterprise Edition 2012 تست شده است.

لطفا مراحل زیر را اجرا نمایید و تنها زمانی به مرحله بعد بروید که مرحله قبلی به اتمام رسیده باشد.

مرحله اول:

یک دیتابیس جدید و سپس جدولی درون آن ایجاد می کنیم.

USE master  
GO  
  
CREATE DATABASE TestDb  
GO  
  
USE TestDb  
GO  
  
CREATE TABLE TestTable (  
 id INT identity(1, 1)  
 ,NAME VARCHAR(50)  
 ,Value INT  
 ,ValueAt DATETIME DEFAULT(GetDate())  
 )  
GO  

مرحله دوم:

یک بکاپ کامل از این دیتابیس فراهم می کنیم.

BACKUP DATABASE TestDb TO DISK = 'D:\TestDb_Full.bak'  
WITH init  
 ,format  
 ,stats = 10  
GO  

مرحله سوم:

DML زیر را روی جدول dbo.TestTable دنبال کنید.

USE TestDb  
GO  
  
INSERT INTO TestTable ( NAME,Value)  
SELECT 'Maral' ,50  
UNION  
SELECT 'Araz' ,250  
UNION  
SELECT 'Ata' ,1200  
UNION  
SELECT 'Alireza',150  
UNION  
SELECT 'Mahdi' ,125  
UNION  
SELECT 'Mahsa' ,560  
UNION  
SELECT 'Amir' ,3200  
GO  
  
SELECT * FROM TestTable  
GO  
  
DELETE FROM TestTable WHERE id > 5  
GO  
  
SELECT * FROM TestTable  
GO  

مرحله چهارم:

حال که داده ها حذف شدند، زمان بازیابی آن هاست.

بازیابی داده های حذف شده

توجه: در اینجا، دیتابیس و به تبع آن جدول تازه ایجاد شده است. اگر زمان حذف داده ها یا زمان تقریبی را بدانیم، بازیابی داده ها به راحتی انجام می شود. اما اگر بازه زمانی بسیار بلند باشد یا زمان آن را فراموش کرده باشیم، بازیابی اطلاعات دشوار خواهد شد.

SELECT [Current LSN]  
 ,[Transaction ID]  
 ,[Operation]  
 ,[Context]  
 ,[AllocUnitName]  
FROM fn_dblog(NULL, NULL)  
WHERE [Operation] = 'LOP_DELETE_ROWS'  
 AND [AllocUnitName] = 'dbo.TestTable'  
  
SELECT [Current LSN]  
 ,[Operation]  
 ,[Transaction ID]  
 ,[Begin Time]  
 ,[Transaction Name]  
 ,[Transaction SID]  
 ,[AllocUnitName]  
FROM fn_dblog(NULL, NULL)  
WHERE [Transaction ID] = '0000:00000343'--<<Paste the TransactionID  
 AND [Operation] = 'LOP_BEGIN_XACT'  

--Copy CurrentLSN 00000021:000000b0:0001  
SELECT CONVERT(INT, CONVERT(VARBINARY, '0x00000021', 1)) --Same Value  
SELECT CONVERT(INT, CONVERT(VARBINARY, '0x000000b0', 1)) --Total 10 digit preceeded by 0  
SELECT CONVERT(INT, CONVERT(VARBINARY, '0x0001', 1)) --Total 5 digit preceeded by 0  
  
-->33000000017600001  
SELECT Cast(CONVERT(INT, CONVERT(VARBINARY, '0x00000021', 1))
 AS VARCHAR(5)) + RIGHT('0000000000' + CAST(CONVERT(INT, CONVERT(VARBINARY, '0x000000b0', 1)) AS NVARCHAR), 10) 
+ RIGHT('00000' + CAST(CONVERT(INT, CONVERT(VARBINARY, '0x0001', 1)) AS NVARCHAR), 5) AS 'MarkPoint'  
GO  

مرحله پنجم

حالا یک گزارش پشتیبان می گیریم. برای این کار، مدل بازیابی دیتابیس باید Full یا Bulk-logged باشد (روی دیتابیس راست کلیک کرده، Properties و سپس Options و بعد گزینه Recovery Model را انتخاب می کنیم)

BACKUP log TestDb TO DISK = 'D:\TestDb_log.trn'  
GO  

مرحله ششم:

بکاپ کامل دیتابیس را بازیابی می کنیم.

-- Starting first with restoring the FULL BACKUP with NORECOVERY  
RESTORE filelistonly  
FROM DISK = 'D:\TestDb_Full.bak';  
  
RESTORE DATABASE [TestDb_New]  
FROM DISK = 'D:\TestDb_Full.bak'  
WITH MOVE 'TestDb' TO 'C:\TestDb.mdf'  
 ,MOVE 'TestDb_log' TO 'C:\TestDb_log.ldf'  
 ,REPLACE  
 ,NORECOVERY;  
GO  

مرحله هفتم:

گزارش پشتیبان دیتابیس را به صورت زیر بازیابی می نماییم.

RESTORE LOG TestDb_New  
FROM DISK = 'D:\TestDb_log.trn'  
WITH STOPBEFOREMARK = 'lsn:33000000017600001'  
GO  

مرحله هشتم:

داده های جدول را بررسی می کنیم.

مرحله نهم

جدول را از بین می بریم.

USE master  
GO  
DROP DATABASE TestDb  
DROP DATABASE TestDb_New  
-->The End<--  

مرحله تعیین بازه زمانی

SELECT [Current LSN]  
 ,[Operation]  
 ,[Transaction ID]  
 ,[Begin Time]  
 ,[Transaction Name]  
 ,[Transaction SID]  
 ,[AllocUnitName]  
FROM fn_dblog(NULL, NULL)  
WHERE --[Operation] = 'LOP_BEGIN_XACT'  
 --and   
 [Begin Time] BETWEEN '2015/07/17 15:30:00:000'  
  AND '2015/07/17 16:00:00:000'