SQL Server System databases
SQL Server has 5 very default system databases which are
Master
As the name implies this is one of the most important databases on the system and without it SQL Server will not start. It stores all the configuration of the instance and all other databases which are subsequently created. The information is stored into tables known as system tables.
MSDB
The MSDB database is used by SQL Agent and contains data about jobs, job schedules, alerts, log shipping, database mail, recovery of damaged pages….. This information is retrievable through the tables stored within this database.
Model
This is basically a template database for the creation of new databases.
TempDB
TempDB is recreated every time SQL is restarted and is basically used for storing of information temporarily when SQL Server doesn’t have sufficient memory available. This could be a sorting query, temporary tables, holding variables…..
Resource
This is a hidden read-only database which contains information on all objects within SQL server. If you look in the directory of your SQL Server installation you will find two files named mssqlsystemresource.mdf and mssqlsystemresource.ldf which contain the resource database. The hidden nature of the database makes it easy to upgrade via hot-fixes and service packs.
Originally published at https://parvtheitgeek.com on January 16, 2014.
Parv The IT Geek 