Introduction

Agar aap SQL Server ya kisi bhi database ke saath kaam karte ho, to sirf backup lena enough nahi hai ❌

👉 Real power hoti hai: Recovery (data wapas lana)

Aur yahi decide karta hai:
➡️ Aapka Recovery Model

Is blog me hum detail me samjhenge:

• Recovery model kya hota hai

• Kitne types hote hain

• Kaunsa kab use karna chahiye

• Transaction log ka role

• Real-world DBA tips

Previous Lecture Recap

Pichle topic me humne seekha:

• Database backup kya hota hai

• Backup kyun important hai

• Backup kaha store karte hain

👉 Ab next step hai:
Backup ko restore kaise karenge?

Yahi kaam karta hai Recovery Model.

Recovery Model Kya Hota Hai?

👉 Simple definition:

Recovery model decide karta hai ki database ko kaise restore kiya ja sakta hai aur kaunse recovery options available hain.

Hinglish Explanation

Recovery model decide karta hai:

• Kitna data (transactions) save hoga

• Kitne time tak save hoga

• Aap restore kaise kar paoge

Recovery Options:

• Full restore

• Point-in-time restore

• Partial restore

DBA Reality Check 🚨

💡 Important baat:

👉 Agar aap database restore nahi kar paate
👉 To aap DBA nahi ho

Itna important hai recovery.

Transaction Log Kya Hota Hai?

Recovery model samajhne se pehle ye clear hona chahiye.

👉 Transaction log ek file hoti hai jo:

• Har change record karti hai

• INSERT / UPDATE / DELETE sab track hota hai

Example:

UPDATE Employees
SET Salary = Salary + 1000
WHERE ID = 1;

👉 Ye operation transaction log me record hota hai

Recovery Models Kitne Types Ke Hote Hain?

SQL Server me 3 types hote hain:

1. Simple Recovery Model

2. Full Recovery Model

3. Bulk Logged Recovery Model

1. Simple Recovery Model

Kya hota hai?

• Sab transactions log me likhe jate hain

• Lekin automatically delete ho jate hain (checkpoint ke baad)

Important Points:

• ❌ Transaction log backup allowed nahi hai

• ❌ Point-in-time recovery possible nahi

• ✅ Sirf Full aur Differential backup

Kaise kaam karta hai?

1. Transaction hota hai

2. Log me save hota hai

3. Checkpoint ke baad delete ho jata hai

Advantages:

• Transaction log size chhota rehta hai

• Easy to manage

Disadvantages:

• Data loss possible

• Limited recovery options

Use Case:

👉 Best for:

• Development environment

• Testing database

Real-Life Example

Agar aap ek test DB use kar rahe ho:

• Data lose ho gaya → koi problem nahi

👉 Simple recovery perfect hai

2. Full Recovery Model

Kya hota hai?

• Har transaction log me save hota hai

• Automatically delete nahi hota

👉 Jab tak aap log backup nahi lete, data rehta hai

Key Feature:

✅ Point-in-time recovery possible

Kaise kaam karta hai?

1. Transaction log me save

2. Log backup lene tak delete nahi hota

3. Backup ke baad clean hota hai

Example:

BACKUP LOG MyDB
TO DISK = 'C:\Backup\MyDB_Log.trn';

Advantages:

• No data loss (almost)

• Full recovery control

• Exact time tak restore

Disadvantages:

⚠️ Transaction log grow karta rehta hai

Agar backup nahi liya:
👉 Disk full
👉 Database stop 😱

Real Problem Scenario

• App heavy transactions kar raha hai

• Log backup nahi liya

👉 Result:

• Log file full

• DB freeze

Use Case:

👉 Best for:

• Production database

• Banking systems

• E-commerce

Important DBA Rule 🔥

👉 Full recovery use kar rahe ho?

To:

• Regular log backup lena mandatory hai

• Monitoring hona chahiye

3. Bulk Logged Recovery Model

Kya hota hai?

• Bulk operations me minimal logging hoti hai

Bulk Operations Example:

• BULK INSERT

• SELECT INTO

• CREATE INDEX

Kaise kaam karta hai?

👉 Normally:

• Har row log hoti hai

👉 Bulk mode:

• Sirf minimal info log hoti hai

Advantage:

• Log size kam

• Performance fast

Disadvantage:

• Point-in-time recovery limited ho jati hai

Important Scenario

Agar bulk operation ke beech me crash ho gaya:

👉 Aap sirf last log backup tak restore kar sakte ho

Best Practice:

👉 Bulk operation se pehle:

BACKUP LOG MyDB;

👉 Operation ke baad:

BACKUP LOG MyDB;

Comparison Table

Recovery Model Default Kaha Se Aata Hai?

👉 New database ka recovery model:

➡️ model database se inherit hota hai

Recovery Model Change Kaise Kare?

SQL Query:

ALTER DATABASE MyDB
SET RECOVERY FULL;

ALTER DATABASE MyDB
SET RECOVERY SIMPLE;

Important:

• Change kabhi bhi kar sakte ho

• But impact samajh ke karo

Practical DBA Tips 💡

🔹 Tip 1: Production me hamesha FULL use karo

🔹 Tip 2: Log Backup Schedule

• Every 15 min / 30 min

🔹 Tip 3: Monitoring Lagao

• Log file size track karo

🔹 Tip 4: Bulk Operation Smartly Karo

• Temporarily BULK LOGGED use karo

• Baad me FULL me wapas aao

🔹 Tip 5: Auto Growth Set Karo

• Log file crash se bachaega

Common Mistakes ❌

• Full recovery me log backup nahi lena

• Simple recovery production me use karna

• Bulk mode use karke restore strategy ignore karna

• Log file monitoring nahi karna

Summary

• Recovery model decide karta hai recovery capability

• 3 types hote hain: Simple, Full, Bulk Logged

• Transaction log sabse important component hai

• Full recovery = best for production

• Backup + Restore + Log strategy = success

👉 Final Line:

“Backup lena skill hai, lekin restore karna real power hai.”

Tags