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.

Model Database Kya Hai

Simple Definition

Model Database = Template (Blueprint) for new databases

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

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 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

Resource DB Ka Role

SQL Server ke system objects:

• sys tables

• system views

• system procedures

Sab actually Resource DB me stored hote hain

Example

Aap run karte ho:

SELECT * FROM sys.objects

Ye data Resource DB se aa raha hai

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

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