آموزش عملی با استفاده از Transaction Log و DBCC PAGE

یکی از رایج‌ترین تماس‌هایی که یک DBA دریافت می‌کند این جمله است:

«چند دقیقه پیش اطلاعات را Delete کردم، بکاپ هم نداریم، می‌شود برگرداند؟»

خبر خوب این است که در بعضی شرایط پاسخ بله است.

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

در این مقاله به صورت عملی بررسی می‌کنیم که چگونه می‌توان بدون داشتن Backup، اطلاعات حذف شده را بازیابی کرد.

آیا واقعاً امکان بازیابی وجود دارد؟

وقتی دستور DELETE اجرا می‌شود، SQL Server در اکثر مواقع داده را بلافاصله از فایل MDF پاک نمی‌کند.

در واقع اتفاقات زیر رخ می‌دهد:

• رکورد در Transaction Log ثبت می‌شود.
• رکورد داخل Data Page به عنوان Ghost Record علامت‌گذاری می‌شود.
• در زمان مناسب توسط Ghost Cleanup Process پاک می‌شود.

تا قبل از پاک شدن کامل Page، هنوز شانس بازیابی وجود دارد.

شرایط لازم برای موفقیت

قبل از شروع مطمئن شوید:

•  دیتابیس Offline نشده باشد.
•  Shrink انجام نشده باشد.
•  DBCC CHECKDB با گزینه Repair اجرا نشده باشد.
•  عملیات سنگین Insert یا Update انجام نشده باشد.
•  بهتر است Recovery Model روی FULL باشد.

سناریوی عملی

جدول زیر را در نظر بگیرید.

 CREATE TABLE Employees ( ID INT PRIMARY KEY, FullName NVARCHAR(100), Salary MONEY )

چند رکورد درج می‌کنیم.  

INSERT INTO Employees
VALUES
(1,'Ali',5000),
(2,'Reza',7000),
(3,'Sara',9000)

نتیجه: 

حالا اشتباهی اجرا می‌کنیم:  

DELETE FROM Employees
WHERE ID=2

رکورد حذف شد.

اما Backup نداریم.

قدم اول؛ هیچ کاری انجام ندهید

اشتباهی که اکثر افراد انجام می‌دهند:

• Rebuild Index
• Shrink Database
• Restart SQL Server
• Insert اطلاعات جدید

همه این کارها احتمال بازیابی را کمتر می‌کنند.

قدم دوم؛ پیدا کردن زمان حذف

ابتدا بررسی می‌کنیم چه زمانی DELETE انجام شده است.

اگر Audit یا Extended Events داشته باشید بسیار ساده است.

در غیر این صورت باید سراغ Transaction Log برویم.

قدم سوم؛ خواندن Transaction Log

SQL Server یک تابع داخلی دارد.

SELECT
Operation,
Context,
Transaction Name,
Begin Time,
End Time
FROM fn_dblog(NULL,NULL)
WHERE Operation='LOP_DELETE_ROWS'

خروجی مشابه:

اگر تعداد رکورد زیاد باشد می‌توانید بر اساس زمان فیلتر کنید.

پیدا کردن شماره صفحه (Page)

در خروجی fn_dblog معمولاً ستونی مانند زیر مشاهده می‌شود.

Page ID

0001:00000130

یعنی رکورد حذف شده داخل Page شماره 304 قرار داشته است.

بررسی محتوای Page

حالا Page را بررسی می‌کنیم.

DBCC TRACEON(3604)
GO
DBCC PAGE('TestDB',1,304,3)

خروجی بسیار بزرگی خواهید دید.

پیدا کردن Ghost Record

داخل خروجی عباراتی مانند این دیده می‌شود.

Record Type = PRIMARY_RECORD

Record Attributes = GHOST_RECORD

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

مشاهده اطلاعات رکورد

در همان خروجی معمولاً ستون‌ها نیز دیده می‌شوند.

مثلاً:

ID = 2

FullName = Reza

Salary = 7000

در این مرحله اطلاعات قابل مشاهده است.

بازیابی رکورد

حالا کافی است دوباره Insert کنیم.

 INSERT INTO Employees
(ID,FullName,Salary)
VALUES
(2,'Reza',7000)

رکورد بازگردانده شد.

اگر اطلاعات داخل DBCC PAGE مشخص نبود چه کنیم؟

در برخی مواقع داده‌ها به صورت Hex نمایش داده می‌شوند.

مثلاً: 

520065007A006100

برای تبدیل Hex به متن: 

SELECT CONVERT(NVARCHAR(MAX),0x520065007A006100)

خروجی:

Reza

اگر Ghost Record حذف شده باشد

در این حالت روش بالا دیگر پاسخگو نیست.

باید از ابزارهای تخصصی استفاده شود؛ مانند:

• ApexSQL Recover
• Redgate SQL Log Rescue (قدیمی)
• Quest LiteSpeed Log Reader
• نرم‌افزارهای تحلیل فایل MDF و Transaction Log

این ابزارها مستقیماً Log و Pageهای دیتابیس را تحلیل می‌کنند و در برخی شرایط امکان استخراج رکوردهای حذف‌شده را فراهم می‌کنند.

محدودیت‌های این روش

این روش همیشه موفق نیست.

موارد زیر احتمال بازیابی را کاهش می‌دهند:

• اجرای CHECKDB با Repair
• Shrink Database
• حجم زیاد عملیات Insert
• پاک شدن Ghost Record
• Rebuild Index
• بازیابی روی دیتابیسی که مدت زیادی از حذف گذشته است

نکات طلایی برای DBAها

• پس از حذف اشتباه، اولین اقدام باید متوقف کردن فعالیت روی دیتابیس باشد.
• هرگز برای آزاد کردن فضا، بلافاصله Shrink اجرا نکنید.
• پیش از هر اقدام، از فایل‌های MDF و LDF یک کپی تهیه کنید و عملیات بازیابی را روی نسخه کپی انجام دهید.
• اگر از Recovery Model = FULL استفاده می‌کنید، نگهداری منظم Transaction Log Backup شانس بازیابی را به‌طور قابل توجهی افزایش می‌دهد.

جمع‌بندی

بازیابی رکوردهای حذف‌شده بدون Backup غیرممکن نیست، اما به عوامل متعددی مانند وضعیت Transaction Log، Data Pageها و زمان سپری‌شده از حذف بستگی دارد. در بسیاری از موارد، رکورد حذف‌شده تا مدتی به‌صورت Ghost Record در صفحات داده باقی می‌ماند و با استفاده از ابزارهایی مانند fn_dblog و DBCC PAGE می‌توان اطلاعات آن را استخراج و مجدداً ثبت کرد.

در مقاله بعدی، به‌صورت عمیق‌تر بررسی خواهیم کرد که چگونه با استفاده از Data Page و ساختار داخلی صفحات SQL Server، حتی در شرایط پیچیده‌تر نیز اطلاعات حذف‌شده را بازیابی کنیم؛ روشی که در پرونده‌های حساس و سناریوهای فوریتی، یکی از مهم‌ترین مهارت‌های یک DBA حرفه‌ای محسوب می‌شود.