SQL Server : System Databases

 Every SQL Server relies on FIVE primary system databases, each of which must be present for the server to 
operate effectively. 
 
  1. Master :  The Master database stores basic configuration information for the server. This includes 
information about the file locations of the user databases, as well as logon accounts, server configuration 
settings, and a number of other items such as linked servers and startup stored procedures. 

  2. Model  :  The Model database is a template database that is copied into a new database whenever 
it is created on the instance. Database options set in model will be applied to new databases created on 
the instance, and any objects created in model will be copied over as well.

  3. MSDB   :  The MSDB database is used to support a number of technologies within SQL Server, 
including the SQL Server Agent, SQL Server Management Studio, Database Mail, and Service Broker.  
A great deal of history and metadata information is available in msdb, including the backup and 
restore history for the databases on the server as well as the history for SQL agent jobs. 

  4. TempDB :  The TempDB system databases is a shared temporary storage resource used by a 
number of features of SQL Server, and made available to all users.  Tempdb is used for temporary objects, 
worktables, online index operations, cursors, table variables, and the snapshot isolation version store,
 among other things.  It is recreated every time that the server is restarted, which means that no objects 
in tempdb are permanently stored.
 
  SQL Server provides two types of temp tables based on the behavior and scope of the table. These are:
 
  1. Local Temp Table
  2. Global Temp Table
 
  Local Temp Table
  
        Local temp tables are only available to the current connection for the user; and they are 
        automatically deleted when the user disconnects from instances. Local temporary table name is 
        stared with hash ("#") sign.
 
  Global Temp Table

        Global Temporary tables name starts with a double hash ("##"). Once this table has been created by 
        a connection, like a permanent table it is then available to any user by any connection. 
        It can only be deleted once all connections have been closed.
 
 Table created under tempdb considered as permanent temp table.

 5. Resource :  The Resource database is responsible for physically storing all of the 
SQL Server 2005 system objects. This database has been created to improve the upgrade and rollback of 
SQL Server system objects with the ability to overwrite only this database.

 Apart from this 5, other databases also available. 
 ReportServer : Primary database for Reporting Services to store the meta data and object definitions.
 ReportServerTempDB :  Report Server Temp DB.