วิธีตรวจสอบการดำเนินการ Truncate และ Delete ใน MSSQL Server

บทนำ

บทความนี้อธิบายขั้นตอนในการระบุผู้ใช้ที่ทำการ Truncate และ Delete ข้อมูลจากตารางใน MSSQL Server รวมถึงเวลาที่เกิดเหตุการณ์ เพื่อช่วยในการตรวจสอบและกู้คืนข้อมูล

เหตุผลในการเก็บข้อมูลนี้

เพื่อตรวจสอบว่าการลบข้อมูลเป็นเจตนาหรือโดยบังเอิญ ระบุผู้กระทำเพื่อดำเนินการป้องกัน และเมื่อทราบเวลาที่แน่นอนของการลบข้อมูล สามารถกู้คืนข้อมูลได้โดยใช้ STOP AT clause ในระหว่าง Log Backup

สรุปปัญหา

กรณีศึกษา: ระหว่างเวลา 17:00-19:00 น. วันที่ 5 มกราคม 2020 ตาราง "dump_truncate" ถูก Truncate และข้อมูลในตาราง "dump_delete" ถูกลบออกจากฐานข้อมูล "truncate_test" คำถามที่ต้องการคำตอบ:

• ใครลบข้อมูลจากตาราง "dump_delete"?
• จำนวนแถวที่ถูกลบจาก "dump_delete" มีกี่แถว?
• ใคร Truncate ตาราง "dump_truncate"?
• เหตุการณ์เกิดขึ้นเมื่อไหร่?
• ตารางการสำรองข้อมูล (Backup Schedule):

• Full Backup: ทุกวันอาทิตย์
• Differential Backup: ทุกวันเวลา 13:00 น.
• Log Backup: ทุก 15 นาที

ข้อกำหนดเบื้องต้น

• Database ต้องอยู่ใน Full Recovery Mode
• ต้องมี Full, Differential และ Log Backups พร้อมใช้งาน

แนวทางการทำงาน (High Level)

1. ระบุ Log Backup ที่มีการดำเนินการ Delete และ Truncate
2. วิเคราะห์รายละเอียดการ Truncate และ Delete จาก Log Backup

ขั้นตอนที่ 1: ระบุ Log Backup ที่มีการ Delete และ Truncate

หมายเหตุ: ทดสอบโดยการ Restore สำเนาฐานข้อมูลใหม่

1. Restore Full Backup วันอาทิตย์ที่ 2 มกราคม ด้วย Standby Mode
2. Restore Differential Backup วันที่ 5 มกราคม ด้วย Standby Mode
3. Restore Log Backup ทีละไฟล์ด้วย Standby Mode ตรวจสอบจำนวนแถวในตารางหลัง Restore แต่ละไฟล์ เพื่อหาว่า Log Backup ไฟล์ไหนมีการดำเนินการ Truncate/Delete

ผลลัพธ์: เมื่อ Restore Log Backup เวลา 18:00 น. ตาราง "dump_truncate" ว่างเปล่า และข้อมูลใน "dump_delete" หายไป แสดงว่าเหตุการณ์เกิดขึ้นระหว่าง 17:45-18:00 น.

ขั้นตอนที่ 2: วิเคราะห์รายละเอียดจาก Log Backup

ส่วนก: ดึง Transaction ID ของการ Delete และ Truncate (17:45-18:00)

พบว่ามี 2 การดำเนินการ (Delete และ Truncate) เกิดขึ้นเวลา 17:50 น. โดย Login: RP_DEV

ส่วนข: หาชื่อตารางจาก Transaction ID

การวิเคราะห์ Delete Operation

1. หา Object ID และ Partition ID ของการ Delete
2. จากผลลัพธ์พบว่า:
   • Description/Transaction Name: Delete operation
   • Begin Time: 2022/01/05 17:50:22.493
   • Login_Name: RP_DEV
   • Lock Information: แต่ละแถวที่ขึ้นต้นด้วย "HoBt" แสดงการลบ 1 แถว รวม 7 แถว
   • Object ID และ Partition ID ของตารางต้นทาง
3. Query หาตารางจาก Object ID และ Partition ID
4. สรุป: ข้อมูลจาก "dump_delete" ถูกลบโดย RP_DEV เวลา 17:50 น. Transaction ID '0000:00016a96' จำนวน 7 แถว

การวิเคราะห์ Truncate Operation

1. หา Object ID และ Partition ID ของการ Truncate
2. ผลลัพธ์แตกต่างจาก Delete:
   • Partition ID ไม่แสดงถูกต้อง ต้องดูจาก Description Column
   • Partition IDs: 72057594043564032 และ 72057594043629568
   • Lock Description: แถว SCH_M_OBJECT แสดง Object ID ที่ถูกต้อง = 885578193
3. Query หาตารางจาก Object ID และ Partition ID
4. สรุป: ตาราง "dump_truncate" ถูก Truncate โดย RP_DEV เวลา 17:50 น. Transaction ID '0000:00016a95'

สรุป

การทราบว่าใครทำการ Truncate/Delete ช่วยป้องกันไม่ให้เกิดซ้ำเมื่อ Auditing ยังไม่เปิดใช้งาน สำหรับ Delete จะทราบจำนวนแถวที่ถูกลบ การทราบเวลาที่แน่นอนช่วยกู้คืนข้อมูลได้แม่นยำ นอกจากนี้เครื่องมือภายนอกเช่น ApexSQL Log และ ApexSQL Recover ก็ช่วยกู้คืนข้อมูลได้อีกด้วย