Introduction

SQL Server me jab hum kaam karte hain, to generally hum tables, queries aur user databases par focus karte hain.

Lekin ek hidden backbone hota hai jo SQL Server ko chalata hai:

System Databases

Ye wo databases hain jo SQL Server ke internal working ke liye mandatory hote hain.

Agar ye properly kaam na karein → SQL Server crash bhi ho sakta hai 😬

Is blog me hum simple language me samjhenge:

• User vs System Databases
• Important system databases
• Kya karna chahiye / kya nahi karna chahiye
• Real DBA tips

User Database vs System Database

User Databases

Ye wo databases hain jo hum create karte hain.

System Databases

Ye SQL Server ke internal databases hote hain.

Inme store hota hai:

• Server configuration
• Login details
• Jobs
• Metadata
• Internal settings

Important Point ⚠

❌ System database corrupt ho gaya → SQL Server down ho sakta hai

Important SQL Server System Databases

Ab hum main system databases ko samjhte hain.

1️⃣ Master Database (Most Important)

Master database = SQL Server ka heart ❤️

Ye store karta hai:

• Server configuration
• Login accounts
• Database list
• Linked servers

Real Example

Agar aap SQL Server start karte ho:

→ Sabse pehle Master DB load hota hai

Critical Warning ⚠

Agar master database corrupt ho gaya:

SQL Server start nahi hoga

DBA Tip

✔ Master database ka regular backup lena must hai

2️⃣ TempDB

TempDB = Temporary workspace

Yaha store hota hai:

• Temporary tables (#temp)
• Table variables
• Sorting operations
• Index rebuild
• Intermediate query results

Example

SELECT * INTO #TempTable FROM Employees

Ye data TempDB me store hota hai.

Important Point

✔ Server restart → TempDB automatically reset ho jata hai

Performance Tip

TempDB slow = poora SQL Server slow

3️⃣ MSDB Database

MSDB use hota hai:

• SQL Server Agent jobs
• Backup history
• Scheduling

Example

Agar aap job create karte ho:

Daily Backup Job

Uski information MSDB me store hoti hai.

Real Scenario

Agar MSDB corrupt ho gaya:

Jobs run nahi honge
Backups fail ho sakte hain

4️⃣ Model Database

Model = Template database

Jab bhi aap new database create karte ho:

→ SQL Server Model database ka copy banata hai

Example

Agar Model DB me aap default setting change karo:

→ Naya database us setting ke saath create hoga

DBA Tip

✔ Model me unnecessary changes mat karo

5️⃣ Resource Database

Ye hidden database hota hai.

Isme store hota hai:

• System objects
• Internal stored procedures

Important

✔ Direct access nahi hota
✔ Internal use ke liye hota hai

6️⃣ Distribution Database

Ye use hota hai:

• SQL Server Replication ke liye

Agar aap replication use karte ho:

→ Ye database data sync manage karta hai

System Databases Me Kya Karna Chahiye (Best Practices)

✔ 1. Backup lena

System databases ka backup lena utna hi important hai jitna user database ka.

Example:

BACKUP DATABASE master
TO DISK = 'C:\Backup\master.bak'

✔ 2. Read Only Use

System databases ko:

✔ Read karo
✔ Understand karo

But:

❌ Modify mat karo

✔ 3. Separate Admin Database Create Karo

Best practice:

Create database: DBAdmin

Yaha store karo:

• Scripts
• Admin tables
• Monitoring queries

System Databases Me Kya Nahi Karna Chahiye

1. Objects Modify Mat Karo

System tables ya procedures modify karna risky hai.

2. New Tables Create Mat Karo

Galti:

CREATE TABLE master.dbo.Test

❌ Wrong practice

3. Objects Delete Mat Karo

Agar aapne system object delete kar diya:

SQL Server crash ho sakta hai

4. Deprecated Objects Use Mat Karo

SQL Server versions me changes aate rehte hain.

Old objects future me kaam nahi karenge.

Real World DBA Scenario

Situation:

SQL Server start nahi ho raha

Possible reason:

✔ Master DB corrupt

Solution:

✔ Backup restore
✔ Rebuild system databases

Summary

SQL Server me 2 types ke databases hote hain:

User Databases

• User create karta hai
• Safe to modify

System Databases

• SQL Server ke liye critical
• Internal working handle karte hain

Important System DBs

• Master → Server brain
• TempDB → Temporary work
• MSDB → Jobs & backup
• Model → Template
• Resource → Internal objects
• Distribution → Replication