System Databases in SQL Server

 When you install SQL Server, you will start with four system databases:

·         master

·         model

·         msdb

·         tempdb

All the above databases are essential to run your server properly.

 

The master database

Every SQL Server, regardless of the version, has master database and hold special set of tables called system tables. These system tables keeps track of the system as whole. For instance, when you create a new database then an entry goes to sys.databases or sysdatabases table.

SELECT * FROM sysdatabases

All the extended and system stored procedures are stored in this database regardless of which database they are intened to use with.

Note: everything that describes  your server is stored in master database so this is very critical database nd hence can not be deleted.

The model database

This is a database on which a copy can be based. Model database forms a template for any new database that we create.So you can modify model database if you want to change what standards, newly created database look like. For instance, you can add some set of tables, functions anmd procedures that need to be part of every newly created database. Also you can include some users that would be cloned into every new database.

Note: this database serves as a template for any other database, it is a required database and can not be deleted.

The msdb database

MSDB database stores all the tasks and processes related to SQL Agent. For instance, if you schedule backups to run on a databases nightly  then there will be an enrty in msdb database.

The tempdb database

One of the key workin area for the server is tempdb. SQl Server needs to create interm temporary tables to solve complex or large queries and these interim tables are created in tempdb.

tempdb is different from any other database. Not only are the objects within it temporary, the database itself is temporary. This is the only database in the system that is completely rebuilt from scratch everytime you start your SQL server.