Introduction

SQL Server ke system databases me kuch aise hidden heroes hote hain jo directly dikhte nahi, lekin bahut powerful role play karte hain.

Aaj hum 2 important databases cover karenge:

• Model Database → Template / Blueprint

• Resource Database → System objects ka hidden storage

Agar aap DBA ya developer ho, to ye dono samajhna must hai.

Previous Lecture Recap

Ab tak humne cover kiya:

• Master DB → SQL Server ka brain

• TempDB → Temporary processing

• MSDB → Jobs, backup history

👉 Ab:

• Model = Template

• Resource = Hidden system engine

Model Database Kya Hai

Simple Definition

Model Database = Template (Blueprint) for new databases

Easy Example

Socho:

Aap ek template banate ho Word me
👉 Har new document us template se banta hai

Same concept:

👉 Har new database → Model DB se copy hota hai

Model Database Ka Role

Jab aap:

CREATE DATABASE TestDB

Run karte ho:

✔ SQL Server kya karta hai?

1. Model database copy karta hai

2. New database create karta hai

3. Baaki space empty pages se fill karta hai

Important Point

👉 TempDB bhi Model se hi create hota hai restart ke time

Model DB Me Kya Copy Hota Hai

Jab new DB create hota hai:

✔ Recovery model
✔ File settings
✔ Tables / objects (agar add kiye ho)
✔ Permissions

Practical Example 🔥

Step 1: Model DB me table create karo

CREATE TABLE Model.dbo.SampleTable (
    id INT,
    name VARCHAR(50)
)

Step 2: New DB create karo

CREATE DATABASE TestDB

Result

👉 TestDB me automatically table aa jayega

✔ Ye real example lecture me bhi demonstrate hua

Real Use Case

Scenario 1: Default Settings

Aap chahte ho:

• Har DB me FULL recovery ho

• Default schema set ho

👉 Model DB change karo → sab DBs inherit karenge

Scenario 2: Common Table

Aap har DB me ek audit table chahte ho

👉 Model DB me create karo

Model DB – Recovery Model Impact

👉 Jo recovery model Model DB ka hoga:

✔ New DB me same hoga

Example

• Model = FULL
  → New DB = FULL

• Model = SIMPLE
  → New DB = SIMPLE

Model DB File Structure

Data File

model.mdf

Log File

modellog.ldf

Auto Growth

• Default: 64 MB

Model DB Restrictions ❌

• Drop nahi kar sakte

• Rename nahi kar sakte

• Offline nahi kar sakte

• Owner change nahi

• CDC enable nahi

• Mirroring nahi

Important Restriction

❌ Encrypted objects create nahi kar sakte (WITH ENCRYPTION)

⚠ Common Mistakes (Model DB)

❌ Model me unnecessary tables create karna
❌ Heavy objects add karna
❌ Recovery model galat set karna

Resource Database Kya Hai

Ab aate hain hidden hero par 👇

Simple Definition

Resource Database = Hidden read-only database jisme SQL Server ke system objects store hote hain

Important Point

✔ Ye database SSMS me visible nahi hota

Resource DB Ka Role

SQL Server ke system objects:

• sys tables

• system views

• system procedures

👉 Sab actually Resource DB me stored hote hain

Logical vs Physical

Example

Aap run karte ho:

SELECT * FROM sys.objects

👉 Ye data Resource DB se aa raha hai

✔ Ye concept lecture me clearly explain hua

Resource DB Properties

Key Features

• Read-only database

• Hidden database

• Only system use karta hai

File Names

mssqlsystemresource.mdf  
mssqlsystemresource.ldf

Location

C:\Program Files\Microsoft SQL Server\...\Binn

Important Rule ⚠

❌ Isko move mat karo

👉 SQL Server start nahi hoga

Resource DB – Why Important

SQL Server Upgrade

Pehle:

❌ System objects drop + recreate hote the

Ab:

✔ Sirf Resource DB replace hota hai

👉 Upgrade fast ho gaya

Resource DB Restrictions ❌

• Modify nahi kar sakte

• Backup normal way se nahi

• Restore normal way se nahi

• SSMS se access nahi

Backup Strategy (Important)

Direct backup nahi le sakte

✔ Alternative:

👉 File copy backup (binary file jaise)

Important Properties

Database ID

32767

Version Check

SELECT SERVERPROPERTY('ResourceVersion')

Last Update

SELECT SERVERPROPERTY('ResourceLastUpdateDateTime')

Practical Understanding 🔥

Model DB = Template

👉 New DB ka design control karta hai

Resource DB = Engine

👉 System objects control karta hai

Real DBA Scenario

Scenario 1: Wrong Model Change

❌ Model me galti se table add kar diya

👉 Har new DB me aa jayega

Scenario 2: Resource DB Corruption

👉 SQL Server start hi nahi hoga

Practical Tips 🔥

Model DB Tips

✔ Sirf necessary changes karo
✔ Testing pehle dev me karo
✔ Recovery model carefully set karo

Resource DB Tips

✔ Kabhi modify mat karo
✔ File location change mat karo
✔ Backup indirectly maintain karo

Common Interview Questions 🎯

Q1: Model DB ka use kya hai?

👉 Template for new databases

Q2: Resource DB kya hota hai?

👉 Hidden DB storing system objects

Q3: Resource DB visible hai?

❌ Nahi

Q4: Model DB change ka impact?

👉 Future DBs par apply hota hai

Summary

Model Database

✔ Template hai
✔ New DB ka behavior control karta hai
✔ TempDB bhi isi se create hota hai

Resource Database

✔ Hidden + Read-only
✔ System objects store karta hai
✔ Upgrade fast banata hai

Final Insight

Model = Blueprint
Resource = Engine

Tags (Comma Separated)

SQL Server Model Database, SQL Server Resource Database, SQL Server System Databases, SQL Server DBA Guide, SQL Server Interview Questions, SQL Server Create Database, SQL Server Recovery Model, SQL Server Internal Architecture, SQL Server Tutorial Hinglish, SQL Server System Objects, SQL Server Performance, SQL Server Upgrade Process, SQL Server Best Practices, SQL Server Learning Guide