[Technology] SQL Server: Backup/Restore Story (2)

จากบทความที่แล้ว สำหรับ SQL Server: Backup/Restore Story (2) นี้จะประกอบไปด้วยเนื้อหาของ Recovery Model / How to restore database และ การทำ Point-in-time recovery

Recovery Model

คือ Option หนึ่งที่อยู่ใน database ของเรา ซึ่ง Recovery Model นี้จะส่งผลต่อผู้ใช้งานในขั้นตอนการ backup และ restore ข้อมูลและ มีทั้งสิ้น 3 ชนิด (เนื่องจากมีบางชื่อคล้ายคลึง backup type จากบทความก่อนหน้าขอย้ำว่าอย่าจำสับสนเพราะ backup type เป็น subset ที่อยู่ข้างในของ Recovery Model อีกที)

Full

ทำให้การ backup ของ database นั้นๆ สามารถทำ full, differential และ transaction log backup ได้ โดย transaction ที่ถูกเขียน จะยังไม่ถูก turncate จนกว่าจะดำเนินการทำ transaction log backup

ข้อดี: สามาารถทำ point-in-time recovery ได้

จุดที่ควรระวัง: log file มีขนาดที่โต ใช้พื้นที่เยอะ

Backup type ที่ใช้ได้: Full Differential Log

Simple

ทำให้การ backup ของ database นั้นๆ สามารถทำ full, differential backup แต่ไม่สามารถทำ transaction log backup ได้ Recovery model แบบ Simple จึงเหมาะสมกับ database ที่ไม่ได้มีความเปลี่ยนแปลงของข้อมูลมากนัก ยกตัวอย่างเช่น database ที่ใช้เพื่อการ test และยังไม่ได้ถูกใช้งานจริง

ข้อดี: ใช้พื้นที่น้อย ประหยัดพื้นที่

จุดที่ควรระวัง: หากข้อมูลสูญหาย ก็จะสามารถ restore จาก backup file ได้อย่างเดียว ไม่สามารถใช้ point-in-time recovery ได้

Backup type ที่ใช้ได้: Full Differential

Bulk-logged

เหมือนกับ Recovery model แบบ Full ซึ่งทำให้การ backup ของ database นั้นๆ สามารถทำ full, differential และ transaction log backup ได้ อีกทั้งยังเพิ่มความสามารถของ Bulk import ทำให้สามารถนำเข้าข้อมูลปริมาณมหาศาลในช่วงเวลาสั้นๆได้

ข้อดี: สามารถนำเข้าข้อมูลปริมาณมากๆได้, สามาารถทำ point-in-time recovery ได้

จุดที่ควรระวัง: ไม่มีการเก็บ transaction log ในขณะทำ bulk insert เนื่องจากข้อมูลมีปริมาณเยอะ

Backup type ที่ใช้ได้: Full Differential Log

วิธีการตั้งค่า Recovery Model

1. database > Properties

2. Options > Recovery model

How to Restore

การ restore สามารถทำได้ 2 แบบ คือการใช้ SQL command ในการ query และการใช้ GUI ของ SSMS (SQL SERVER Management Studio)

Minimal permission ของการ restore

Server level — DBCREATOR ROLE
Database level — DB_OWNER ROLE

Prerequisite

sql server/ssms/free space/file backup of database

แบบที่ 1: SQL command

Case 1: การ restore ทับไฟล์เดิม

Restore database databasename from disk = 'D:\full.bak' with replace

โดย databasename แทนชื่อของ database และ D:\full.bak แทน path ของไฟล์

Case 2: การ restore ไฟล์ไปไว้ที่ใหม่

RESTORE DATABASE databasename FROM DISK = 'D:\ Full.bak' 
WITH 
MOVE 'databasename' TO 'D:\Data\databasename.mdf', 
MOVE 'databasename_Log' TO 'D:\Data\databasename_Log.ldf'

โดย databasename แทนชื่อของ database

Note: ถ้ามีไฟล์ที่ต้อง restore หลายไฟล์ อย่าลืมไล่จาก Full ตัวล่าสุด → Differential ตัวล่าสุด(ถ้ามี) → Log ที่ต่อจาก Full (ถ้ามี Diff ใช้ต่อจาก Diff) →ไฟล์ Log ลำดับถัดไปจนถึง Log ตัวล่าสุด

แบบที่ 2: GUI ของ SSMS (SQL SERVER Management Studio)

case1: การ restore ทับไฟล์เดิม

1. database > task >restore>database…

2. General > Device > เลือก path ที่มีไฟล์ backup

3. Options > Restore options > เลือก WITH REPLACE > เลือก OK

4. หน้าต่างแสดงว่าการ restore สำเร็จ

Case2: การ restore ไฟล์ไปไว้ที่ใหม่

1. database > task >restore>database…

2. General > Device > เลือก path ที่มีไฟล์ backup

3. Files> เลือก Relocate all file to folder> เลือก path ใหม่ที่ต้องการวาง

4. Options > ไม่มีจุดไหนที่เลือก > เลือก OK

5. หน้าต่างแสดงว่าการ restore สำเร็จ

แล้วการทำ Point-in-time recovery คืออะไรล่ะ?

Point-in-time recovery คือ การฟื้นฟูข้อมูลที่สามารถระบุจุดของเวลาก่อนที่ข้อมูลจะเกิดการเสียหายได้ เช่น database พังเวลา 17.00 เราสามารถฟื้นฟู database ให้กลับมาใช้งานได้โดยระบุจุดของเวลาที่ใช้ในการดำเนินการฟื้นฟูในช่วงเวลา 16.59 ได้นั่นเอง

ตัวอย่างการทำ Point-in-time recovery แบบ SQL command

USE [master]
RESTORE DATABASE [databasename] FROM  DISK = N'D:\ Full.bak' 
WITH  NORECOVERY
RESTORE DATABASE [databasename] FROM  DISK = N'D:\ Diff.bak' 
WITH  NORECOVERY
RESTORE LOG [databasename] FROM  DISK = N'D:\ Log1.bak' 
WITH  NORECOVERY
RESTORE LOG [databasename] FROM  DISK = N'D:\ Log2.bak' 
WITH  NORECOVERY
RESTORE LOG [databasename] FROM  DISK = N'D:\ Log3.bak'
 WITH STOPAT = N'2020-01-09T16:59:59', RECOVERY

ตัวอย่างการทำ Point-in-time recovery แบบ GUI ของ SSMS (SQL SERVER Management Studio)

1. ดำเนินการ restore database
2. ที่ General > เลือก Timeline

3. เลือก Specific date and time > เลือก วันที่ และ เวลาที่ต้องการ > OK

สำหรับบทความนี้เราได้รู้จักความแตกต่างของ Recovery model แต่ละชนิด การกู้คืนฐานข้อมูล และการทำ Point-in-time recovery ผู้เขียนขอจบหัวข้อ SQL Server: Backup/Restore Story ไว้แต่เพียงเท่านี้ หากผู้อ่านท่านไหนสนใจหรือมีประเด็นสงสัยประการใด สามารถตอบกลับได้ที่บนความนี้ ไว้พบกันใหม่บทความถัดไป

Reference

recovery model

https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/recovery-models-sql-server?view=sql-server-ver15

point in time recovery

https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/restore-a-sql-server-database-to-a-point-in-time-full-recovery-model?view=sql-server-ver15

Point-in-time recovery - Sql Server Backup Academy