System Databases

Master is the most important database in SQL Server. The master database contains the following crucial information:

• All logins, or roles, that the user IDs belongs to

• Every system configuration setting (e.g., data sorting information, security   

   Implementation, default language)

• The names of and information about the databases within the server

• The location of databases

• How SQL Server is initialized

Specific system tables holding the following information (this list is not exhaustive):

• How the cache is used

• Which character sets are available?

• A list of the available languages

• System error and warning messages

• Special SQL Server objects called assemblies (tables within every database that deal with SQL Server objects and therefore are not specific to the master database). The master database is the security guard of SQL Server, and it uses the preceding information to ensure that everything is kept in check.

Tempdb

     The tempdb database is—as its name suggests—a temporary database whose lifetime is the duration of a SQL Server session; once SQL Server stops, the tempdb database is lost. When Server starts up again, the tempdb database is re-created, fresh and new.

                                  Any temporary table created within a stored procedure or query will be placed within the tempdb database. Being just like any other database, tempdb has size restrictions and you must ensure that it is big enough to cope with your applications and any temporary information stored within it.

msdb

     msdb is another crucial database within SQL Server, as it provides the necessary information to run jobs to SQL Server Agent. As with tempdb and model, you should not directly amend this database, and there is no real need to do so. Many other processes use msdb. For example, when you create a backup or perform a restore, msdb is used to store information about these tasks.

Model

    Purpose - Template database for all user defined databases.User defined tables, stored procedures, user defined data types, etc can be created in the Model database and will exist in all future user defined database.The database configurations such as the recovery model for the Model database are applied to future user defined databases