بازیابی رکوردهای حذف شده در SQL SERVER - مقدمه

 مهمترین قسمت هر برنامه افزاری، اطلاعات و یا داده های موجود در آن است چراکه برای هر سازمان، اطلاعات از اهمیت خاصی برخوردار است. به عبارتی دیگر هدف اصلی از طراحی یک برنامه، ایجاد امکان درج، حذف، بروزرسانی، تهیه گزارش و نگهداری از اطلاعات می باشد. پس یک نرم افزار بدون اطلاعات و داده ها، عملاَ کاربردی نخواهد داشت. یکی از موقعیت های پراسترس و کابوس مانند برای هر برنامه نویس و یا مدیر پایگاه داده، مواجه شدن با  رکوردهای حذف شده در SQL SERVER است. اما لازم است بدانیم با رعایت برخی نکات و آشنایی با ساختار دیتابیس در sql server می توان اقدام به بازیابی رکوردهای حذف شده در SQL SERVER کرد.

در آموزش بازیابی رکوردهای حذف شده در SQL SERVER ابتدا با ساختار یک دیتابیس آشنا می شویم و مراحل لازم جهت اجرای یک دستور delete و تغییراتی که بواسطه اجرا شدن این دستور در پایگاه داده اعمال می شود را خواهیم دید. در ادامه یک استاندارد شامل مزاحل لازم به منظور آماده شدن برای شروع فرآیند بازیابی رکوردهای حذف شده در SQL SERVER ارائه می کنیم. جهت بازیابی رکوردهای حذف شده سه روش ارائه خواهد شد. که دو روش ابتدایی با استفاده از امکانات موجود در sql server قابل انجام است و نیاز به استفاده از ابزار جانبی وجود ندارد و در روش سوم با استفاده از نرم افزارهای جانبی توسعه داده شده توسط شرکت هایی چون Apex و Systools  بازیابی رکوردهای حذف شده در SQL SERVER  را انجام می دهیم.

دانلود فیلم آموزش بازیابی رکوردهای حدف شده در SQL SERVER 

---

بازیابی رکوردهای حذف شده در SQL SERVER - ساختار دیتابیس

همانطور که می دانید با ایجاد یک دیتابیس، حداقل دو فایل اصلی MDF و LDF برای آن دیتابیس ایجاد می شود(اگر چه می توان فایل های  ndf را نیز به دیتابیس اضافه کرد) که رکوردهای اصلی دیتابیس و جداول در فایل MDF نگهداری می شود و فایل LDF نیز ذخیره کننده هرگونه تغییرات انجام شده بر روی اطلاعات موجود در فایل MDF است. با همین توضیح مختصر، فرض کنید لازم است یک رکورد را از جدولی در دیتابیس حذف کنیم پس لازم است تا آن رکورد در فایل MDF و اطلاعات مرتبط با این عملیات حذف(مقادیر فیلدها، زمان حذف، کاربر و ...) در LDF نگهداری شود:

 در تصویر بالا، مراحل لازم جهت حذف یک رکورد از جدول Employees که فیلد Family آن مقدار "بندامیر" را دارد نمایش داده شده است. این مراحل عبارتند از:

     1. انتقال pageهایی از جدول حاوی رکورهایی با شرط  "بندامیر"=Family به حافظه اصلی(buffer pool)

     2. حذف رکوردهای دارای شرط مورد نظر از pageها و اعمال تغییرات مورد نیاز در header صفحات

     3. ثبت اطلاعات عملیات حذف(مقادیر فیلدها و ...) در LDF قبل از اعمال تغییرات بر روی دیسک(WAL: write ahead logging)

     4. اعمال تغییرات از حافظه اصلی در MDF و حذف رکوردها

نکته اول: از تغییرات اعمال شده در LDF و MDF در عملیات delete، می توان جهت بازیابی رکوردهای حذف شده استفاده کرد.

دقت کنید که رکوردها در MDF به صورت فیزیکی حذف نمی شوند و تنها به عنوان available علامت گذاری می شوند تا فضای تخصیص داده شده به آن ها، در آینده جهت درج رکورهای جدید مورد استفاده قرار بگیرد(از این قابلیت در بازیابی اطلاعات از ویندوز نیز استفاده می کنیم)

---

بازیابی رکوردهای حذف شده در SQL SERVER - آماده سازی

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

     

دلایل پیشنهاد انجام مراحل بالا عبارتند از:

     1. جلوگیری از rewrite شدن اطلاعات موجود در فایل MDF 

     2. انجام عملیات بازیابی بر روی یک دیتابیس غیر عملیاتی جهت جلوگیری از توقف سرویس دهی برنامه

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

پس از ریستور کردن دیتابیس در یک سرور جدید، می توان از هر یک از سه روش ذکر شده در ادامه آموزش، جهت بازیابی رکوردهای حذف شده استفاده کرد.

---

بازیابی رکوردهای حذف شده در SQL SERVER - روش اول

 به عنوان اولین روش در بازیابی رکوردهای حذف شده، از backup - restore و گزینه های موجود در زمان ریستور کزدن دیتابیس استفاده می کنیم. یکی از تنظیمات مهمی که در زمان ایجاد یک دیتابیس باید به آن توجه ویژه داشته باشیم recovery model است که در سه حالت full - simple و bulk-logged قابل تنظیم است. برای اینکه بتوانیم از روش اول جهت بازیابی رکوردهای حذف شده استفاده کنیم بایستی recovery model دیتابیس را در حالت full تنظیم کنیم تا سابقه کلیه تغییرات اعمال شده بر روی رکوردهای جدول در فایل LDF ذخیره شود و تا زمان تهیه بک آپ از لاگ فایل این اطلاعات حذف نشوند(در حالت simple با رخ دادن هر checkpoint این اطلاعات حذف می شوند).

 نکته دوم: می توان ریستور کردن دیتابیس را در زمان خاص و یا قبل و بعد از علامت(MARK) خاصی انجام داد.

با توجه به نکته بالا، در زمان ریستور کردن یک دیتابیس می توانیم تعیین کنیم که اطلاعات و تغییرات اعمال شده در دیتابیس تا یک نقطه خاص(زمان، علامت) یا قبل و بعد از آن برگشت داده شود. برای درک بهتر این امکان در sql server می توانید قابلیت Windows System Restore در سیستم عامل ویندوز را در نظر بگیرید که با Restore Point هایی که به صورت خودکار توسط سیستم عامل و یا به صورت دستی توسط کاربر ایجاد شده می توان ویندوز را به وضعیت مورد نظر ریستور کرد.

برای برگرداندن وضعیت دیتابیس، نیاز به تعیین نقطه بازگشت هستیم، در sql server این نقطه بازگشت می تواند زمان(تاریخ و ساعت)، آی دی تراکنش و یا یک MARK باشد. در توضیح MARK دز این حد بسنده می کنیم که بدانید در زمان اجرای یک تراکنش می توانیم با With Mark برای تراکنش مورد نظر یک Mark ایجاد کنیم و در زمان نیاز به بازگشت وضعیت دیتابیس، از آن استفاده کنیم:

BEGIN TRANSACTION DailyDeleteOldData WITH MARK
DELETE FROM OrderHistory 
COMMIT TRANSACTION DailyDeleteOldData

در شکل زیر، مراحل لازم جهت استفاده از روش اول در بازیابی رکوردهای حذف شده، بطور خلاصه نمایش داده شده است:

در فیلم آموزش بازیابی رکوردهای حدف شده در SQL SERVER جزئیات روش اول را به همراه مثال عملی از بازیابی رکوردهای حذف شده مشاهده نمایید.

---

بازیابی رکوردهای حذف شده در SQL SERVER - روش دوم

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

بطور خلاصه، مراحل لازم در روش دوم جهت بازیابی رکوردهای حذف شده، عبارتند از:

و اما فیلد حاوی اطلاعات رکوردهای حذف شده در لاگ فایل با نام RowLog content 0 است. ساختار بیت های موجود در این فیلد را در تصویر زیر از کتاب Kalen Delaney(صفحه 258 از کتاب microsoft_sql_server_2012_internals) مشاهده می کنید:

با آگاهی از ترتیب و تعداد بیت های موجود در این کافی است، مقادیر زیر را بدست آوریم:

[Fixed Length Data] = Substring (RowLog content 0, Status Bit A+ Status Bit B + 1,2 bytes)
[Total No of Columns]= Substring (RowLog content 0, [Fixed Length Data] + 1,2 bytes)
[Null Bitmap length] = Ceiling ([Total No of Columns]/8.0)
[Null Bytes]= Substring (RowLog content 0, Status Bit A+ Status Bit B + [Fixed Length Data] +1, [Null Bitmap length] )
Total no of variable columns = Substring (RowLog content 0, Status Bit A+ Status Bit B + [Fixed Length Data] +1, [Null Bitmap length] + 2 )
Column Offset Array= Substring (RowLog content 0, Status Bit A+ Status Bit B + [Fixed Length Data] +1, [Null Bitmap length] + 2 , Total no of variable columns*2 )
Variable Column Start = Status Bit A+ Status Bit B + [Fixed Length Data] + [Null Bitmap length] + 2+( Total no of variable columns*2)

همانظور که در تصویر بالا مشخص است، با اظلاعات استخراج شده از RowLog content 0، تعداد ستون های با مقدار ثابت و متغییر، تعداد کل ستون های جدول، مقادیر null و غیر null و ... در دسترس است. در دو مرحله نهایی کافی است که اطلاعات را از فرمت هگزادسیمال به نوع داده ای مرتبط با فیلد مربوطه تبدیل کنیم و  بازیابی رکوردهای حذف شده را انجام دهیم.

در فیلم آموزش بازیابی رکوردهای حدف شده در SQL SERVER جزئیات روش دوم را به همراه sp تهیه شده جهت بازیابی رکوردهای حذف شده با مثال عملی از کاربرد آن را مشاهده نمایید.

---

بازیابی رکوردهای حذف شده در SQL SERVER - روش سوم

 همانطور که بارها اشاره کرده ام، شما به عنوان یک مشاور، متخصص و یا طراح دیتابیس در sql server بایستی یک جعبه ابزار از برنامه های کاربردی را تهیه و همواره به همراه خود داشته باشید. یکی از این دسته برنامه ها، نرم افزارهای لازم جهت بازیابی رکوردهای حذف شده است. که ما در این آموزش به عنوان سومین روش از آن ها استفاده می کنیم. از آنجایی که در sql server جهت حذف اطلاعات ممکن است از یکی از سه دستور delete- truncate و یا drop استفاده کرده باشید و  بازیابی رکوردهای حذف شده با توجه به استفاده از هریک از این سه دستور متفاوت است. در ابتدا نگاهی بر تفاوت عملکرد بین این سه دستور داریم:

 دستور delete در حذف رکوردها:

      1. جزء دستورات DML محسوب می شود

      2. ایجاد یک رکورد جدید در لاگ فایل به ازای هر رکورد حذف شده

     3. می تواند دارای شرط در حذف رکوردها باشد

     4. عدم ریست شدن مقدار Identity با اجرای این دستور

     5. بروز رسانی متادیتا 

دستور truncate در حذف رکوردها:

     1. جزء دستورات DDL محسوب می شود

     2. ثبت حداقل اطلاعات در لاگ فایل(تنها آی دی صفحه و فایل)

     3. در حذف رکوردها فاقد هرگونه شرطی است(همه رکورها حذف می شوند)

     4. ریست شدن مقدار Identity با اجرای این دستور

     5.  بروز رسانی متادیتا 

دستور drop در حذف رکوردها:

     1. جزء دستورات DDL محسوب می شود

     2. ثبت حداقل اطلاعات در لاگ فایل(تنها آی دی صفحه و فایل)

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

با مقایسه ای اجمالی بین سه دستور بالا، متوجه می شویم که در حالت رخ دادن عملیات حذف با استفاده از دستور truncate و یا drop، بازیابی اطلاعات، تنها با استفاده از لاگ فایل امکان پذیر نیست و لازم است از MDF نیز استفاده کنیم. با توجه به اینکه اطلاعات در MDF به صورت فیزیکی وجود دارند و تنها به عنوان available علامتگذاری می شوند، این امکان جهت بازیابی رکوردهای حذف شده فراهم است. ابزارهای استفاده شده در این قسمت از آموزش عبارتند از: Apexsql log، Apexsql recover و Systools Log Analyzer.

دلیل استفاده از چند ابزار مختلف در بازیابی رکوردهای حذف شده، این است که مکانیزم مورد استفاده در هر نرم افزار جهت بازیابی، متفاوت است. بنابراین استفاده از چند ابزار(در صورت عدم موفقیت در یکی از آن ها) درصد موفقیت عملیات بازیابی رکوردهای حذف شده را افزایش می هد. علاوه بر اینکه در مستندات شرکت Apex توصیه شده است که از هر دو نرم افزار Apexsql Log و Apexsql recover در عملیات بازیابی استفاده شود. در قسمتی از شکل زیر مقایسه ای داریم بین قابلیت های این دو نرم افزار در بازیابی رکوردهای حذف شده:

جهت انتخاب صحیح از دو ابزار Apexsql Log و Apexsql recover شرکت Apex یک فلوچارت به عنوان راهنمایی در انتخاب این دو ابزار در موقعیت های مختلف تهیه کرده است که در شکل زیر مشاهده می کنید:

 مهمترین عملیاتی که می توان در بازیابی رکوردهای حذف شده با استفاده از نرم افزارهای اشاره شده(بویژه Apexsql Recover) انجام داد عبارتند از:

     1. مشاهده اطلاعات موجود در لاگ فایل به صورت قابل فهم 

     2. بازیابی رکوردهای حذف شده با دستور delete

     3. بازیابی رکوردهای حذف شده با دستور truncate

     4. بازیابی رکوردهای حذف شده با دستور drop

     5. بازیابی داده های حذف شده از نوع Blob

     6. ریستور کردن تنها یک جدول از بک آپ تهیه شده از دیتابیس

---

بازیابی رکوردهای حذف شده در SQL SERVER - جمع بندی

هر فرد متخصص در حوزه دیتابیس و sql server، لازم است تا علاوه بر آشنایی با مباحث اینترنال، مجموعه ای از ابزارهای مورد نیاز جهت استفاده در مواقع ضروری و حساس را نیز آموزش دیده باشد. در آموزش بازیابی رکوردهای حذف شده، سه روش جهت بازیابی معرفی شدند که روش اول علاوه بر اجرا با استفاده از اسکریپت، به سادگی از طریق ویزاردهای موجود در sql server قابل انجام است. برای پیاده سازی روش دوم نیاز به آشنایی با لاگ فایل و جزئیات فیلد Row Contents 0 داریم. البته sp تهیه شده برای این منظور آماده در اختیار شما قرار داده می شود اما برای اعمال تغییرات و توسعه دادن آن نیاز است با برخی مسایل اینترنال آشنایی داشته باشید و نهایتاً در روش سوم نیز ار سه ابزار مختلف برای بازیابی اطلاعات استفاده کرده ایم. در تصویر زیر محاسن و معایب سه روش اشاره شده در طول آموزش بازیابی رکوردهای حذف شده را عنوان کرده ایم:

در فیلم آموزش بازیابی رکوردهای حدف شده در SQL SERVER جزئیات سه روش بازیابی، مثال هایی از نحوه استفاده عملی از این روشها، اسکریپت sp معرفی شده در روش دوم، آموزش نصب و استفاده از نرم افزارهای معرفی شده، امکان دانلود کتاب Kalen Delaney و اسکریپتهای استفاده شده در طول آموزش جهت استفاده شما قرار داده شده است.

• قبلی