Use of Master, MSDB, Model, TempDB and Resource Databases

The Master database holds information for all databases located on the SQL Server instance and is the glue that holds the engine together. Because SQL Server cannot start without a functioning master database, you must administer this database with care.

It contains

• System level information for a SQL Server system
• Login accounts and all system configuration settings.
• Records the existence of all other databases, including the location of the database files.
• Records the initialization information for SQL Server 

The msdb database stores information regarding database backups, SQL Agent information, DTS packages, SQL Server jobs, and some replication information such as for log shipping.

The tempdb holds temporary objects such as global and local temporary tables and stored procedures.

This database holds

• All temporary tables and temporary stored procedures.
• It also fills any other temporary storage needs such as work tables generated by SQL Server.
• tempdb is a global resource; the temporary tables and stored procedures for all users connected to the system are stored there.

tempdb is re-created every time SQL Server is started so the system starts with a clean copy of the database. Because temporary tables and stored procedures are dropped automatically on disconnect, and no connections are active when the system is shut down, there is never anything in tempdb to be saved from one session of SQL Server to another.

The model is essentially a template database used in the creation of any new user database created in the instance.

The model database is used as the template for all databases created on a system. When a CREATE DATABASE statement is issued, the first part of the database is created by copying in the contents of the model database, and then the remainder of the new database is filled with empty pages. Because tempdb is created every time SQL Server is started, the model database must always exist on a SQL Server system.