🧠 Blog No. 3 🔄 SQL Server Log Backup Job कैसे बनाएं?

🧠 Blog No. 3

🔄 SQL Server Log Backup Job कैसे बनाएं?

(Step-by-Step + Real Production Case)

---

🔍 Introduction

अगर आपका database FULL Recovery Model में है
और फिर भी Transaction Log backup नहीं ले रहे,
तो समझ लो समस्या time की नहीं, sirf run होने की है 💣

👉 Disk full
👉 SQL service hang
👉 Production down

इस ब्लॉग में आप सीखेंगे:

• Log Backup क्यों mandatory है

• SQL Server Agent Job कैसे बनाते हैं

• Proper schedule क्या होना चाहिए

• Real-life failure + fix

---

❓ Log Backup इतना ज़रूरी क्यों?

FULL Recovery Model में:

• Log तब तक clear नहीं होता

• जब तक LOG BACKUP न लिया जाए

📌 Shrink करने से:

• Temporary space मिलती है

• Problem solve नहीं होती

📌 Log Backup से:

• Log truncate होता है

• Disk safe रहती है

• Point-in-time recovery possible होती है

---

🔎 Check करें: Database FULL Recovery में है या नहीं

SELECT name, recovery_model_desc
FROM sys.databases;

अगर output में:

FULL

और log backup नहीं है
तो 🚨 Danger Zone।

---

🧩 Step-by-Step: Log Backup Job बनाना

1️⃣ SQL Server Agent ON होना चाहिए

SQL Server Agent = OFF ❌
Job = RUN नहीं होगा

---

2️⃣ Log Backup Script

BACKUP LOG MyDB
TO DISK = 'D:\SQLBackups\MyDB_Log.trn'
WITH INIT, COMPRESSION;

📌 COMPRESSION disk space बचाता है
📌 .trn = transaction log backup

---

3️⃣ Multiple Databases के लिए Dynamic Script

DECLARE @DBName SYSNAME
DECLARE @SQL NVARCHAR(MAX)

DECLARE db_cursor CURSOR FOR
SELECT name FROM sys.databases
WHERE recovery_model_desc = 'FULL'
AND name NOT IN ('master','model','msdb','tempdb')

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @DBName

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @SQL = '
    BACKUP LOG [' + @DBName + ']
    TO DISK = ''D:\SQLBackups\' + @DBName + '_Log.trn''
    WITH INIT, COMPRESSION'
    
    EXEC (@SQL)
    FETCH NEXT FROM db_cursor INTO @DBName
END

CLOSE db_cursor
DEALLOCATE db_cursor

---

⏰ Best Schedule Strategy

Database Type	Log Backup
Critical (ERP, Live App)	Every 15 min
Medium Load	Every 30 min
Low Load	Hourly

📌 Schedule SQL Server Agent Job में set करें।

---

🏢 Real Production Case (True Story)

❌ Problem

• FULL recovery enabled

• 3 महीने से log backup job नहीं

• Log file = 180 GB

• Disk full at 2 AM

😱 Result

• Application down

• Users panic

• Night call to DBA

---

✅ Fix

1️⃣ Immediate log backup
2️⃣ Log file auto truncate
3️⃣ Disk space recovered
4️⃣ Proper Agent Job scheduled

👉 Shrink की ज़रूरत ही नहीं पड़ी 😉

---

⚠️ Common Mistakes (Avoid These)

❌ FULL recovery + no log backup
❌ Daily shrink job
❌ Log backup to same disk
❌ Single backup file overwrite without rotation

---

✅ Best Practices (Professional Rule)

✔️ Log backup always on separate disk
✔️ Use compression
✔️ Monitor job failures
✔️ Combine with Full + Diff backups

---

🎯 Conclusion

अगर आप SQL Server में:

• Disk issues

• Log growth

• Night alerts

से बचना चाहते हैं,
तो Log Backup Job mandatory है ❗

👉 Shrink = Emergency
👉 Log Backup = Permanent Solution