SQL Server's System databases

Written on Apr 29, 2016

SQL Server has five system databases.

  1. resource
  2. master
  3. model
  4. msdb
  5. tempdb

You can see these databases under "System Databases" in management studio.


Resource

It's a read-only database containing all system objects. It is often referred as "resource" database, it's actual name is "mssqlsystemresource". You don't see the "resource" database in SSMS's object explorer window, but system objects persisted in resource database logically appear in every database on the server.

In order to find out actual location of resource database, we can use below query.


Master

The master database contains instance-wise metadata about sql server, also information about all databases installed on the current instance. It's suggested that we should not directly access or modify master database, because if something goes wrong it will corrupt entire sql server.

It's better to use catalog views in case of accessing information regarding server configuration and status information.

It's also important to backup master database at various times. It is basically used to store information of all databases within SQL Server and server cannot start if master database is not configured propery.


Model

This database is used as the template from which newly created database is essentially cloned. Normally we don't change this database unless we are having specific purpose, and most importantly we are extremely knowledgeable about potential implications.


MSDB

This database stores system settings and configurations information for various support services, such as database backups, DTS packages, Replication, SQL Server agent information, SQL Server Jobs, Database Mails etc.

If we need to access this database, it is suggested to use its views and stored procedures.


Tempdb

This database is used to store global and local temporary tables and temporary stored procedures. The tempdb database is recreated from scratch every time we restart SQL Server.


It's highly recommended that we should use the system-provided stored procedures and catalog views to modify system objects and system metadata, and let SQL Server manage the system database. We should not change structure of system databases through T-Sql query, and in case we needed to make some change use system stored procedures and functions.