قراءة سجل الإجراءات Transaction logs في SQL Server

إنّ سجلات الإجراءات فيSQL Server تحتوي على سجلات تصف التغييرات التي تم إجراؤها على قاعدة البيانات. حيث يتم تخزين معلومات كافية لإستعادة قاعدة البيانات إلى نقطة محددة من الوقت ، لإعادة أو التراجع عن التغيير. ولكن كيف يمكن رؤية ما بداخلها ، البحث عن إجراء معين، معرفة ما حدث والعودة عن التغييرات مثل استرداد السجلات المحذوفة عن طريق الخطأ.

إنّ رؤية ما يتم تخزينه في سجل الإجراءات الفعال، أو نسخة إحتياطية من هذه السجلات لايتم ببساطة. فإذا حاولت فتح ملف LDF أو TRN باستخدام محرر بيانات ثنائي ، ستظهر سجلات غير مفهومة ، حتى الواضح منها لا يمكن قراءته مباشرة. على سبيل المثال ، الملف التالي مقتطف من ملف LDF :

استخدام دالة fn_dblog

fn_dblog هي دالة غير موثقة في SQL Server والتي تستخدم لقراءة الجزء النشط من سجل الإجراءات الفعالة .
لنرى ما هي الخطوات التي يجب اتباعها وكيف ستظهر النتيجة:

1. قم بتنفيد الدالة fn_dblog كالتالي:

Select * FROM sys.fn_dblog(NULL,NULL)



بما أنّ هذه الدالة تقوم بإرجاع 129 عموداً، فإنه من الأفضل إرجاع بعض الأعمدة المحددة فقط ، بالإضافة الى إقتصار النتائج على بعض أنواع الإجراءات إن أمكن.

2. من قائمة النتائج السابقة للدالة ، ابحث عن الإجراءات التي تريد رؤيتها:

لرؤية السجلات التي تم ادخالها ، قم بتنفيذ التال

SELECT [Current LSN], 
       Operation, 
       Context, 
       [Transaction ID], 
       [Begin time]
       FROM sys.fn_dblog
   (NULL, NULL)
  WHERE operation IN
   ('LOP_INSERT_ROWS');



لرؤية السجلات التي تم حذفها ، قم بتنفيذ التالي:

SELECT [begin time], 
       [rowlog contents 1], 
       [Transaction Name], 
       Operation
  FROM sys.fn_dblog
   (NULL, NULL)
  WHERE operation IN
   ('LOP_DELETE_ROWS');



3. إيجاد العمود الذي تخزن فيه القيم التي تم إدخالها أو حذفها – تحقق من محتويات كل من الأعمدة : RowLog Contents 0 , RowLog Contents 1 , RowLog Contents 2 , RowLog Contents 3 , RowLog Contents 4, Description and Log Record

يتم تخزين البيانات الأولية لأنواع العمليات المختلفة في أعمدة مختلفة.ولتتمكن من رؤية ما تريد بالضبط باستخدام الدالة fn_dblog ، يجب عليك معرفة محتويات العمود لكل نوع من أنواع الإجراءات. وبما أنه لا يوجد توثيق رسمي لهذه الدالة ، فإنه من الصعب معرفة ذلك.

يتم عرض السجلات التي تم إدخالها أو حذفها على شكل قيم ستة عشرية. ولتتمكن من تحويلها إلى حقول يجب عليك معرفة الصيغة المستخدمة، فهم خانات الحالة ، معرفة عدد الأعمدة … وهكذا.

4. يجب عليك أن تأخذ بعين الإعتبار نوع بيانات العمود في الجدول عند تحويل البيانات الثنائية إلى بيانات مجدولة. علماً بأن طرق التحويل تختلف باختلاف نوع البيانات.

إنّ دالة fn_dbLog هي دالة فعّالة ، قوية ومجانية ، لكن هناك بعض القيود عليها ، ومن ذلك قراءة سجلات التغيرات على بنية العناصر تكون معقدة لانها تتضمن تعديلات جوهرية على كثير من جداول النظام. إضافة إلى ذلك ، فقط الجزء النشط من سجلات التغيرات الفعالة يمكن قراءته ، ولا يتضمن أيضاً أمر إعادة بناء القيم المعدلة أو قيم BLOB.

بالنسبة لعملية التعديل، فإنها يتم تسجيلها بشكل طفيف في سجلات الإجراءات، دون تحديد القيم قبل أو بعد التعديل ، لكن فقط ما تم تغييره في السجل ( على سبيل المثال ، يقوم SQL Server بتسجيل أن حرف G تبدل إلى F ، مع أنه فعليا تم تبديل كلمة GLOAT بكلمة FLOAT) ، مما يتطلب منك أن تحلل يدوياً الحالة قبل التعديل ،كما تتضمن كل الحالات التي تتوسطها من إدخال السجل الأصلي إلى التعديل النهائي الذي تحاول اكتشافه.

عند محاولة حذف بيانات BLOB ، فإن قيمتها لا يتم إدخالها الى سجل الإجراءات، لذلك فإن مجرد قراءة سجل الإجراءات لقيمة BLOB المحذوفة لن يعيد هذه القيمة مرة أخرى. فقط اذا كان هناك سجل إدخال لهذه القيمة المحذوفة ، يمكنك عندها مزاوجة هاتين القيمتين ومن ثم إعادة القيمة المحذوفة من سجل الإجراءات باستخدام الدالة fn_dblog.

استخدام دالة fn_dump_dblog

لقراءة سجلات الإجراءات الأصلية أو النسخ الإحتياطية المضغوطة حتى بدون قاعدة البيانات الفعالة ، يمكنك استخدام الدالة fn_dump_dblog. وكما في الدالة التي قبلها ، هي أيضاً دالة غير موثقة.

1. قم بتنفيذ دالة fn_dump_dblog على نسحة احتياطية من سجل الإجراءات ، مع العلم أنه يجب عليك تحديد 63 متغير:

SELECT *
FROM fn_dump_dblog
(NULL,NULL,N'DISK',1,N'E:\ApexSQL\backups\AdventureWorks2012_05222013.trn', 
DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT, 
DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT, 
DEFAULT);



ومثل الدالة السابقة fn_dbLog، فإن هذه الدالة ترجع 129 عموداً ، لذا ينصح بإرجاع أعمدة محددة فقط:

SELECT [Current LSN], 
       Operation, 
       Context, 
       [Transaction ID], 
     [transaction name],
       Description
FROM fn_dump_dblog
(NULL,NULL,N'DISK',1,N'E:\ApexSQL\backups\AdventureWorks2012_05222013.trn', 
DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT, 
DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT, 
DEFAULT);

مرة أخرى، يجب عليك فك القيم الستة عشرية للحصول على المعلومات التي تبحث عنها.



وهنا نعود لنفس النقطة السابقة عند استخدام fn_dblog، حيث أنك تحتاج إلى إعادة بناء كل قيم السجل يدوياً، من خلال متابعة سلسلة التغيرات في عملية التعديل والعمليات على قيم BLOB.

إذا كنت لا تريد استخراج الإجراءات من النسخة الاحتياطية ، ولكن إستعادة قاعدة البيانات إلى نقطة معينة قبل حدوث عملية محددة، بإمكانك :

2. تحديد نقطة العودة LSN لهذا الإجراء.

3. تحويل نقطة العودة LSN إلى الصيغة المستخدمة في جملة WITH STOPBEFOREMARK = ‘<mark_name>’ clause

   على سبيل المثال : 00000070:00000011:0001 يجب أن يتم تحويله إلى 112000000001700001 .

4. استعادة كامل سلسلة النسخة الإحتياطية حتى تصل إلى النقطة التي حصل عندها الإجراء المطلوب. عندها تستخدم جملة mark _name> WITH STOPBEFOREMARK = ‘< لتحديد نقطة العودة LSN للإجراء.

RESTORE LOG AdventureWorks2012
FROM
    DISK = N'E:\ApexSQL\backups\AW2012_05232013.trn'
WITH
    STOPBEFOREMARK = 'lsn:112000000001700001',
    NORECOVERY;

استخدام أمر DBCC PAGE

طريقة أخرى مفيدة ، لكن غير موثّقة ، باستخدام أمر DBCC PAGE. والذي يمكنك استخدامه لقراءة محتويات ملفات قاعدة البيانات الفعالة MDF و LDF. تكون صيغة الأمر كالتالي:

DBCC PAGE ( {'dbname' | dbid}, filenum, pagenum [, printopt={0|1|2|3} ])

لتفريغ محتويات الصفحة الأولى من ملف سجل الإجراءات في قاعدة بيانات AdventureWorks2012 ، استخدم:

SELECT FILE_ID ('AdventureWorks2012_Log') AS 'File ID' 
-- to determine Log file ID = 2
DBCC PAGE (AdventureWorks2012, 2, 0, 2)

عند التنفيذ ستحصل على :

DBCC execution completed. If DBCC printed error messages, 
contact your system administrator.

افتراضيا، لا يتم عرض النتائج. إذا كنت ترغب في عرضها في SQL Server Management Studio
، قم بتفعيل إشارة تتبع 3604 أولاً.

DBCC TRACEON (3604, -1)

والآن قم بإعادة تنفيذ الأمر:

DBCC PAGE (AdventureWorks2012, 2, 0, 2)

سوف تحصل على مجموعة من الأخطاء وترويسة سيئة، يمكنك إهمالها جميعاً. وفي النهاية ستحصل على النتيجة على صيغة ستة عشرية من ملف قاعدة البيانات LDF الحالية:

وهي ليست طريقة عرض واضحة ومفهومة لبيانات قاعدة بياناتك ، ولا يختلف كثيراً عن عرضها في محرر الصيغة الستة عشرية ، على الأقل ستكون قادر على الوصول إلى البيانات الفعالة.

استخدام تطبيق ApexSQL Log

ApexSQL Log هو تطبيق لقراءة سجل الإجراءات في SQL Server ، حيث يقوم بقراءة سجل الإجراءات في قواعد البيانات الفعالة ، وسجل الإجراءات لقاعدة البيانات المنفصلة ، وسجل الإجراءات من النسخة الإحتياطية والنسخة الإحتياطية المضغوطة. وعند الحاجة ، يستخدم لقراءة النسخة الإحتياطية للحصول على بيانات كافية لإعادة بناء ناجحة. إن هذا التطبيق يقوم بإعادة تنفيذ التغيرات على البيانات والعناصر التي أثرت على قاعدة البيانات، بالإضافة إلى التغيرات التي حصلت قبل تنزيل التطبيق. وخلافاً للدالات غير الموثّقة وغير المدعومة المذكورة أعلاه، ستحصل على معلومات واضحة ومفهومة لما حدث ، وعلى أي عنصر ، وما هي القيم القديمة والقيم الجديدة.

1. ابدأ تطبيق ApexSQL Log

2. اشبك على قاعدة البيانات التي تريد قراءة سجل إجراءاتها :

   

3. في خطوة إختيار سجلات SQL من أجل التحليل ، إختر السجلات التي تريد قراءتها. تأكد من أنها تشكل سلسلة كاملة:

   

4. لإضافة نسخة إحتياطية من سجل الإجراءات أو ملف LDF منفصل ، استخدم زر Add

5. استخدم خيارات إعداد التصفية Filter setup لتضييق نطاق الإجراءات التي تريد قراءتها من خلال النطاق الزمني ، نوع العملية ، الجدول وفلاتر تصفية أخرى متاحة.

   

6. انقر على زر Open للتنفيذ

   سيتم عرض نتائج شاملة وواضحة في شبكة نتائج ApexSQL Log.

   سوف تكون قادرا على رؤية الوقت الذي بدأت فيه العملية وانتهت، ونوع العملية، والمخطط وإسم العنصر للعنصر المتأثر، وإسم المستخدم الذي نفذ العملية، الجهاز والتطبيق المستخدم لتنفيذ العملية. وللتعديلات على البيانات، ستتمكن من رؤية القيمة القديمة والقيمة الجديدة للحقل المعدّل.

   

لتجنّب القيم الستة عشرية ، الدالات غير الموثقّة ، المحتويات غير الواضحة للأعمدة ، جُمل الإستعلام الطويلة ،الخطوات المعقدة ، عمليات إعادة بناء البيانات غير الكاملة لجمل التعديل و بيانات BLOB عند قراءة سجل الإجراءات ، استخدم تطبيق ApexSQL Log. حيث يقوم بقراءة سجل الإجراءات وعرض النتائج بلغة إنجليزية سهلة وواضحة. إضافة الى ذلك، ستحصل على برامج التراجع والإعادة بنقرة زر.

مترجم Ahmad Yaseen