In Microsoft SQL Server, some databases are created automatically during installation. These are called System Databases and are essential for the working of SQL Server.
1. What are System Databases?
System databases store important information required for managing and running SQL Server.
- Store server configuration
- Manage users and logins
- Handle system operations
- Support database creation and temporary work
2. Types of System Databases
SQL Server mainly has four important system databases:
- master
- model
- msdb
- tempdb
3. master Database
What: The main database of SQL Server.
Stores:
- Login accounts
- Database information
- Server configuration
Without the master database, SQL Server cannot start.
4. model Database
What: A template database.
Purpose:
- Used as a base for creating new databases
CREATE DATABASE TestDB;
New databases are created using the structure of the model database.
5. msdb Database
What: Used for scheduling and automation.
Stores:
- SQL Server Agent jobs
- Backup history
- Scheduled tasks
It helps automate database operations.
6. tempdb Database
What: Temporary working database.
Stores:
- Temporary tables
- Intermediate query results
- Sorting and calculations
It is cleared automatically when SQL Server restarts.
CREATE TABLE #TempData (
ID INT,
Name VARCHAR(50)
);
7. Simple Summary
- master → Controls SQL Server
- model → Template for new databases
- msdb → Jobs and scheduling
- tempdb → Temporary data storage
Conclusion
System databases are automatically created and are essential for SQL Server to function properly. They help manage data, perform operations, and support application performance.
Your learning journey continues 🚀