SQL Server Database Recovery
เรื่อง การ Rebuild ของ Raid 5 ครับ ทำ เสียบเข้าไปทำ Online แล้ว raid 5 หายไม่ได้ทำ hot swap คับ shutdown เครื่อง เปลี่ยน hdd ใหม่จากนั้น ก็เปิดเครื่อง และสั่ง online hdd จากนั้น raid 5 ที่ทำpartition ไว้ก็หาย ไปหมดเลย และที่สำคัญ backup ไม่มีคับคนที่ backup เค้า ไม่ได้ทำไว้ และมัน มี เมื่อ สัปดาห์ก่อน มันไม่ อัพเดท มา 7 วัน พอมีทางแก้ไข ได้มั้ยครับ ตอนนี้สินหวังมาก เลย
ใช้ dell open manage ทางเดลบอกว่า ดาต้า lost คับแบบนี้ มีใครพอจะกู้คืนมาได้ บ้างมั้ยครับ มีวิธีมั้ย คิดค่าใช้จ่ายก็ครับ ผมจ่ายเอง ฮือ ๆ โดนไล่ออกแน่ ๆ
----------------------------------------
บทนำ
=======
จากแหล่งอ้างอิงข้างบนนะครับ--
สรุปจากกระทู้ข้างบน ก็คือ Server มี Drive C:, Drive D: โดยที่
Drive C: ติดตั้ง OS ไว้ เป็น Mirror 2 x 73GB
Drive D: เก็บ DB ของ SQL Server เป็น RAID5 7 x 73GB
โดยมี Location ในการเก็บไฟล์ต่าง ๆ และสมมติข้อมูลต่าง ๆ ที่อาจจะเป็นไปได้ ดังนี้
1. เก็บพวก System Database ต่าง ๆ ไว้ที่ Drive C: และ ( เพราะว่าในกระทู้ว่าสามารถ Start SQL Server หลังจากที่ RAID5 หายไปแล้ว )
2. เก็บ mdf ของ Database ตัวที่ใช้งาน ไว้ที่ Drive D: และ
3. เก็บ ldf ไว้ที่ Drive C:
4. Database ตัวที่ใช้งาน มี Recovery Model เป็นแบบ Full (เพราะในกระทู้ว่าสามารถเอา Transaction log มา restore ได้)
5. มี Full database backup ของสัปดาห์ที่แล้ว อยู่บน Tape
6. ไม่มี Transaction log backup
ปัญหาที่เกิดขึ้น
1. Admin ทำการ Shutdown Server
2. เมื่อ Admin ทำการ Startup Server ขึ้นมาใหม่ ปรากฎว่า Drive D: หรือ ตัวที่เป็น RAID5 หายไป ทำให้ไฟล์ mdf ของ Database ที่ต้องใช้งานหายไป แต่ Drive C: ยังอยู่ปกติ
จำลองสถานการณ์
===========
เนื่อง จากว่า การติดตั้ง SQL Server โดยให้มี Config เรื่อง Drive C, D ต่างๆ ตามกระทู้ข้างบน อาจจะทำให้ต้อง Setup SQL Server ใหม่ ดังนั้น ในการจำลองเพื่อฝึก Recover SQL Server นี้ เราจะทำกันง่าย ๆ โดยใช้ Database PUBS เป็นตัวอย่าง และ เพิ่มสมมติว่าให้มี Transaction log backup เพิ่มเติมด้วย
สมมติ
วันที่ 1 ทำ Full database backup ชื่อ 'pubs_fullbackup.bak'
วันที่ 2 ทำ Transaction log backup ชื่อ 'pubs_tlog_01.trn'
วันที่ 3 ทำการ Stop SQL Server แล้วเกิดไปลบ ไฟล์ pubs.mdf ทิ้งเสีย ถึงแม้ว่า SQL Server จะ Start ได้ แต่ Database pubs ก็ติดสถานะ Suspect และไม่สามารถใช้งานได้เสียแล้ว
(สถานการ์สมมติในวันที่ 3 นี้จะคล้ายกับ กระทู้สถานการณ์ตัวอย่าง ที่ไฟล์ mdf หายไป เนื่องจาก Drive D หายไปทั้ง Drive )
เริ่มต้นเตรียม Server และ Backup ต่าง ๆ กันเลยนะครับ
======================================
1. ตรวจสอบ/แก้ไข Recovery Model ของ pubs ให้เป็นแบบ Full เสียก่อน โดย
-- สำหรับ ตรวจสอบ Recovery model
SELECT DATABASEPROPERTYEX('pubs', 'RECOVERY')
GO
-- สำหรับแก้ไข Recovery model ของ pubs ให้เป็นแบบ Full
ALTER DATABASE pubs
SET RECOVERY FULL
GO
2. ทำ Full database backup เอาไว้ 1 ชุด (สมมติว่าเป็น Backup เมื่อวันที่ 1 )
BACKUP DATABASE pubs
TO DISK = 'C:\pubs_fullbackup.bak'
GO
3. Insert เพิ่มเติมข้อมูล (สมมติว่าเป็น Transaction ในวันที่ 1 หลัง Full database backup )
INSERT INTO pubs.dbo.jobs (job_desc, min_lvl, max_lvl) VALUES ('Admin',25,100)
GO
INSERT INTO pubs.dbo.jobs (job_desc, min_lvl, max_lvl) VALUES ('IT Manager',75,100)
GO
ลอง Select ตรวจสอบดูว่ามีข้อมูลเพิ่มเติมจริง
4. ทำ Transaction log backup ตาม Schedule
BACKUP LOG pubs
TO DISK = 'c:\pubs_tlog_01.trn'
GO
5. Insert เพิ่มเติมข้อมูล (สมมติว่าเป็น Transaction ในวันที่ 2 หลังจากที่ทำ Transaction log backup)
INSERT INTO pubs.dbo.jobs (job_desc, min_lvl, max_lvl) VALUES ('Nurse',25,100)
GO
INSERT INTO pubs.dbo.jobs (job_desc, min_lvl, max_lvl) VALUES ('Physician',75,100)
GO
ลอง Select ตรวจสอบดูว่ามีข้อมูลเพิ่มเติมจริง
สร้างปัญหาตามสถานการณ์
=================
1. Stop SQL Server
2. ลบเฉพาะไฟล์ pubs.mdf ทิ้งเสีย หรือจะ rename ก็ได้ ส่วนจะ Copy เก็บไว้ก่อน หรือไม่ก็ได้ครับ แต่อย่าทำอะไรกับ pubs_log.ldf นะครับ
ตรงนี้ไม่ต้องกังวล ถ้า pubs เสียหาย คุณสามารถสร้างใหม่ได้ ด้วยการ run script "instpubs.sql" ซึ่งอยู่ที่ "C:\Program Files\Microsoft SQL Server\MSSQL\Install"
3. Start SQL Server
เรียบร้อยแล้วครับ ลองดูจากใน Enterprise Manager จะได้ Database pubs ที่ติดสถานะ suspect ทำอะไรก็ไม่ได้ ซึ่งน่าจะเทียบเท่า หรือคล้ายคลึงกับสถานการณ์ในกระทู้นต้นแบบแล้วนะครับ ( เห็น suspect แล้วอย่าเพิ่งไป Detach มันนะครับ เดี๋ยวทำขั้นตอนต่อไปไม่ได้ )
การกู้ Database (Recovery)
=======================
หลักการ : คือ
1. จะต้องเอา Full database backup ที่มีล่าสุด มา restore ก่อน จากนั้น --> ซึ่งจะทำให้ได้ Data กลับมาของวันที่ 1 ( มีแล้ว คือ pubs_fullbackup.bak )
2. เอา Transaction log backup ของวันที่ 2 มา restore ต่อจากข้อ 1 --> จะได้ข้อมูลถึงวันที่ 2 ( มีแล้ว คือ pubs_tlog_01.trn )
3. เอา Transaction log ล่าสุดที่มีของวันที่ 3 มา restore ต่อจากข้อ 2 --> จะได้ข้อมูลถึงวันที่ 3 ปัจจุบันก่อน Database เสียหาย (ยังไม่มี ต้องสร้างก่อน)
ดังนั้น เราต้องเตรียม Transaction log backup ของข้อ 3 ก่อน เพราะข้อมูล/Transaction ระหว่างวันที่ 2 หลัง Transaction log backup ยังคงอยู่ในไฟล์ pubs_log.ldf ดังนี้ ( Transaction log ที่ไม่ใช่ backup จะไม่สามารถนำไปใช้ restore ได้)
1. ทำ Transaction log backup ล่าสุดของไฟล์ log ที่มีอยู่ ( ถ้าในสถานการณ์จริงโชคดีที่ไฟล์ log ไม่พัง หรือเสียหายนะครับ )
BACKUP LOG pubs
TO DISK = 'C:\pubs_tlog_last.trn'
WITH NO_TRUNCATE
GO
จะเห็นว่าคำสั่งในการทำ Transaction log backup ครั้งนี้ จะต้องเพิ่ม Option ว่า WITH NO_TRUNCATE ซึ่ง Option นี้จะใช้กรณีที่ต้องการ Backup log ในขณะนี้ Database เสียหายไปแล้ว ( ลองไม่ใส่ WITH NO_TRUNCATE ก็ได้ครับ จะได้เห็น Error)
มาเริ่ม Restore กันเลยนะครับ ซึ่งสามารถทำได้ 2 แบบ คือ ใช้ NORECOVERY หรือใช้ STANDBY ซึ่งจะมีตัวอย่างคำสั่งให้ดูทั้ง 2 แบบ นะครับ
การ Restore โดยใช้ NORECOVERY
2. เริ่ม Restore Database กันก่อน
RESTORE DATABASE pubs
FROM DISK = 'C:\pubs_fullbackup.bak'
WITH NORECOVERY
GO
--> ถึงตรงนี้ถ้าดูใน Enterprise Manager จะเห็น Database ติดสถานะเป็น Loading อยู่ ซึ่งจะยังใช้งานไม่ได้
3. ทำการ Restore transaction log
RESTORE LOG pubs
FROM DISK = 'C:\pubs_tlog_01.trn'
WITH NORECOVERY
GO
--> สถานะยังเป็น Loading อยู่
4. ทำการ Restore transaction log ตัวสุดท้าย
RESTORE LOG pubs
FROM DISK = 'C:\pubs_tlog_last.trn'
WITH RECOVERY
GO
--> ซึ่งก็จะได้ Database ที่ครบถ้วนพร้อมใช้งานกลับมาดังเดิม
หรือ
การ Restore โดยใช้ STANDBY
2. เริ่ม Restore Database กันก่อน
RESTORE DATABASE pubs
FROM DISK = 'C:\pubs_fullbackup.bak'
WITH STANDBY='C:\undo_data.dat'
GO
--> ถึงตรงนี้จะได้ Database เป็นสถานะ Read-Only ซึ่งสามารถ Select ดูข้อมูลใน Table jobs ได้ว่า มีข้อมูลอะไรกลับมาบ้าง
3. ทำการ Restore transaction log
RESTORE LOG pubs
FROM DISK = 'C:\pubs_tlog_01.trn'
WITH STANDBY='C:\undo_data.dat'
GO
--> ลอง Select ข้อมูลจาก Table jobs ดู จะได้กลับมาเพิ่มอีก 2 records
4. ทำการ Restore transaction log ตัวสุดท้าย
RESTORE LOG pubs
FROM DISK = 'C:\pubs_tlog_last.trn'
WITH RECOVERY
GO
--> ลอง Select ข้อมูลจาก Table jobs ดู จะเห้นว่าได้กลับมาครบสมบูรณ์
Azn
ปล. ระหว่างแบบ WITH STANDBY กับ WITH NORECOVERY จะแตกต่างกันที่แบบ STANDBY จะได้ DB ที่เป็น Read-Only ทำให้สามารถ Select ข้อมูลมาดูได้ แต่ว่าแบบ NORECOVERY จะไม่สามารถทำอะไรได้เลย ส่วนในเชิง Performance ผมไม่รู้ว่ามันมีอะไรแตกต่างกันบ้าง
การแก้ไขกรณี Database suspect
=========================
กรณีข้างต้นเป็น วิธีการ Recover Database ที่มีสถานะเป็น Suspect โดยใช้วิธีการ Restore จาก backup
เอาล่ะทีนี้ ลองมาดูกันถึงกรณี Database suspect เพิ่มเติม
สาเหตุของการเกิด Suspect
===================
Database ใน SQL Server จะมีสถานะเป็น suspect ได้เนื่องจาก
- ไฟล์ mdf, ldf ของ databases นั้นหายไป --> อาจจะเนื่องจาก Disk เสีย หรือ Server ไม่เห็น Disk หรือ ไฟล์ถูกลบ/เปลี่ยนชื่อ โดยไม่ตั้งใจ หรือ เปิดไฟล์ไม่ได้
- ไฟล์ mdf, ldf เสียหาย ไม่สามารถใช้งานได้ --> อาจจะเกิดจากไฟดับ โดยที่ไม่ได้ Stop database หรือ เกิด Bad sectors บน Disk
- พื้นที่ Disk ไม่พอขณะที่ทำการ Recovery database
ซึ่งรายละเอียดต้องตรวจสอบกับ error log ของ SQL Server อีกครั้งหนึ่ง
การแก้ไข
======
สิ่งสำคัญอย่างแรก ก็คือ อย่าเพิ่ง Detach datatabase นะครับ ส่วนมากแล้วเวลาเจอปัญหา Database suspect สิ่งแรกที่ทำก็คือ เรามักจะทดลอง Detach แล้ว Attach ใหม่ ซึ่งผลลัพธ์ที่ออกมาก็คือ ไม่สามารถ Attach ใหม่ได้ แต่ถ้าสมมติ Detach ไปแล้ว ก็ยังมีเทคนิคนิดหน่อย (ซึ่งจะกล่าวในภายหลัง) ที่จะช่วยนำ Database กลับมามา (แต่ก็ยังเป็นสถานะ Suspect อยู่นะ)
สำหรับการแก้ไขจะมีอยู่ 2-3 แบบ คือ
1. Reset status ของ Database ที่มีสถานะเป็น suspect
วิธีนี้ จะใช้กับกรณีที่ยังมีไฟล์ mdf, ldf อยู่ครบถ้วน แต่อาจจะมีปัญหาในเรื่องของ Disk หรือ Array ของ RAID (หนังสือกล่าวไว้เช่นนี้ แต่ผมไม่เคยประสบเหตุจริงจึงไม่สามารถอธิบายเพิ่มได้) ซึ่งหลังจากแก้ไขปัญหาแล้วสามารถ Run คำสั่งในการ Reset status ดังนี้
EXEC sp_resetstatus 'pubs'
แต่ว่าหากการ Reset status แล้วยังไม่สามารถใช้งาน Database ได้ วิธีที่สามารถทำต่อได้ก็จะมีให้เลือก คือ
2.1 Restore ข้อมูลกลับมาจาก Backup ที่มีอยู่ --> ก็คือ กรณีตัวอย่างข้างต้นนั่นเอง
ถ้า ตรวจสอบแล้วพบว่ามี Database backup และ Transaction log backup ครบถ้วน ก็ให้ทำ Transacion log backup ล่าสุดให้เรียบร้อยก่อน แล้วจึงเอา Backup ทั้งหมดที่มีมา Restore
2.2 Set database ที่มีปัญหาให้เป็น emergency mode เพื่อให้สามารถดึงข้อมูลออกไปไว้ใน Database ใหม่ โดยมีคำสั่งดังนี้
EXEC sp_configure 'allow updates', 1 -- > เพื่อให้สามารถ Update system table ได้
GO
RECONFIGURE WITH OVERRIDE
GO
UPDATE SYSDATABASES SET STATUS=-32768 WHERE NAME='pubs'
EXEC sp_configure 'allow updates', 0 --> Set กลับให้เหมือนเดิม
GO
RECONFIGURE WITH OVERRIDE
GO
จาก นั้น ก็ให้ ดึงข้อมูลจาก database ตัวเดิม ไปเก็บไว้ใน database ใหม่ แล้วจะแก้ไขปัญหาเรื่อง Server หรือปัญหาอื่น ๆ กันอย่างไรก็ว่ากันต่อไป
** ในวิธีนี้หากปัญหาเกิดจาก Bad sectors ของ HDD แล้ว ข้อมูลที่ดึงออกมาได้อาจจะไม่ครบถ้วน หรือ ได้เป็นอักษรแปลก ๆ
ซึ่ง หากเป็นกรณีที่ HDD เสีย/Bad แล้วมี Backup ครบถ้วน และสามารถ ฺBackup transaction log ของ Database ปัจจุบันได้แล้ว การกู้ข้อมูลจะสามารถทำได้โดยสมบูรณ์ แต่หากว่าไม่สามารถ backup transaction log ดังในข้อ 2.1 ได้ ก็อาจจะต้องทำทั้ง 2.1, 2.2 คือ set database ให้เป็น Emergency mode แล้วดึงข้อมูลไปเก็บไว้ที่ Database ใหม่อีกตัวหนึ่งก่อน แล้วจึงเอา backup ที่มีอยู่ มาทำการ Restore แล้วค่อยเปรียบเทียบข้อมูลกันเพื่อแก้ไขส่วนที่ไม่ตรงอีกครั้งหนึ่ง
(จะเห็นว่าถ้า Disk เสีย หรือไม่มี Backup แล้ว อาจจะไม่สามารถ Recover หรือ กู้ Database ให้กลับมาเช่นเดิม โดยไม่สูญเสีย
--> ดังนั้น ควรทำ RAID และวางแผน Backup และเก็บ Backup ไว้ให้ดี ๆ)
ถ้า Detach database ไปแล้วจะทำอย่างไร
==============================
สมมติก่อนนะครับ ว่าปัญหาไม่ได้มาจาก Bad sectors ของ HDD มิฉะนั้น การแก้ไขวิธีนี้อาจจะไม่สำเร็จ
ขั้นตอนการแก้ไข
1. Rename ไฟล์ mdf, ldf ของ Database ตัวที่ได้ Detach ไป ( เดี๋ยวไฟล์มันจะไปซ้ำกับข้อ 2)
2. สร้าง Database ใหม่ โดยให้มี Logical name, Physical filename, Location ให้เหมือนกันกับ Database ้เดิมทุกประการ
จากขั้นตอนนี้ เราก็จะได้ Database ว่าง ๆ ชื่อเดิม, physical files เหมือนเดิม, Logical name เดิมกลับมาแล้ว
3. ทำการ Stop SQL Server แล้วลบ physical files ในข้อ 2 เสีย จากนั้นก็ Rename files ที่ได้แก้ไขในข้อ 1 กลับมาเหมือนเดิม (ซึ่งก็ควรจะเป็นชื่อเดียวกับที่เพิ่งถูกลบไป) แล้วก็ Start SQL Server ใหม่
( คือ Stop SQL --> เอาไฟล์เดิมแทนไฟล์ใหม่ --> Start SQL)
จากนั้น ก็จะได้ Database ที่เป็น Suspect กลับมาเหมือนเดิม แล้วค่อยแก้ไขต่อครับ
ปล. จริง ๆ แล้วในข้อ 1, 3 จะใช้วิธีการ Copy ไฟล์ mdf, ldf ออกไปเก็บไว้ที่อื่นก่อน แล้วค่อย Copy กลับมาทับก็ได้นะครับ แต่คิดว่าหากใช้ Rename อาจจะดีกว่า เพราะว่าถ้า HDD bad แล้วการ Copy อาจจะไม่สำเร็จ ( ส่วน Rename ไม่รู้อ่ะ คิดว่าน่าจะสำเร็จ )
( จะเห็นว่า จะทำอะไรก็ตามถ้า HDD bad แล้ว จะมีอุปสรรคมากขึ้น ดังนั้น RAID เป็นทางเลือกที่ดีกว่าสำหรับ Database Server มาก ๆ เลย )
แล้วถ้าจำ Logical name และ Physical filename + location ไม่ได้จะทำอย่างไร
=========================================================
ครับ ถ้ามีไฟล์ backup อยู่สามารถใช้คำสั่ง restore filelistonly เพื่อดูชื่อ Logcal name และ Physical filename, location ได้ ดังนี้
RESTORE FILELISTONLY FROM DISK = 'c:\pubs_fullbackup.bak'
GO
0 comments:
Post a Comment