Master Database

Master database SQL Server ka main system database hai.

Master Database Kya-Kya Store Karta Hai

Master database me almost har important information store hoti hai.

✔ Server Level Information

• SQL Server name
• Instance details
• Configuration settings

✔ Security Information

• Logins
• Users
• Permissions

✔ Database Information

• Sabhi databases ki list
• Unke file paths (MDF, LDF)

✔ Other Important Data

• Linked servers
• Endpoints
• System configurations

Real Life Example

Jab SQL Server start hota hai:

Step 1 → Master DB read hota hai  
Step 2 → Databases locate hote hain  
Step 3 → Databases online aate hain

Important Concept – Resource Database

Old versions me system objects master me hote the.

Ab kya hota hai:

• Actual system objects → Resource DB (hidden)
• Access → Master DB ke through

Advantage

✔ Direct access nahi hota
✔ System safe rehta hai
✔ Accidental changes avoid hote hain

Master Database Files

Master database ke do main files hote hain:

1️⃣ Data File

Logical Name: master  
Physical Name: master.mdf

2️⃣ Log File

Logical Name: mastlog  
Physical Name: mastlog.ldf

Important

✔ MDF = Data
✔ LDF = Log

Autogrowth Settings

• Data file → 10% growth
• Log file → 10% growth

Master Database Restrictions ❌

Master database par kuch operations allowed nahi hote.

❌ Allowed Nahi Hai

• New file add karna
• Filegroup create karna
• Database rename karna
• Database delete karna
• Offline karna
• Read-only banana
• Owner change karna
• Mirroring use karna
• CDC enable karna
• Full-text index create karna

Important

✔ Sirf Full Backup allowed hai
❌ Differential / Log backup nahi

Best Practices (Very Important)

✔ 1. Regular Backup

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

✔ 2. Backup Kab Lena Chahiye

Jab bhi ye changes ho:

• New database create
• Database drop
• Login create
• Configuration change

✔ 3. Separate Admin DB Use Karo

Bad practice:

CREATE TABLE master.dbo.Test

Good practice:

Create database: DBAdmin

✔ 4. Trustworthy Option OFF Rakho

Security reason:

TRUSTWORTHY = OFF

Common Mistakes ❌

❌ Master DB me tables create karna
❌ Direct changes karna
❌ Backup ignore karna
❌ File location change without knowledge

Error Troubleshooting

Check karo:

1️⃣ Startup Parameters

Master file path correct hai ya nahi

2️⃣ Error Logs

Path:

SQL Server Logs

Error example:

Cannot find master.mdf

Recovery Options

✔ Option 1 – Restore Master DB

Agar SQL Server start ho raha hai:

→ Backup se restore karo

✔ Option 2 – Rebuild Master DB

Agar SQL Server start nahi ho raha:

→ Master DB rebuild karna padega

⚠ Ye advanced topic hai (production level DBA skill)

Important Objects

• Tables (limited)
• Views (important)
• Stored procedures

Important Concept

Actual data → Resource DB me
Access → Master DB views se

Useful System Views

1️⃣ Databases List

SELECT * FROM sys.databases

2️⃣ Database Files

SELECT * FROM sys.database_files

3️⃣ Memory Info

SELECT * FROM sys.dm_os_performance_counters

Real DBA Advice

• Master DB ko kabhi lightly mat lo
• Always backup ready rakho
• Changes pehle DEV me test karo
• Logs check karna seekho