Sytem databases in SQL Server .

SQL Server has 5 system databases   . System databases are mandatory databases for every instance of SQL Server .

Master Database : 

a. It is also called as "The Brain of SQL Server" .
b. It is the primary database which is required to start the SQLServerService .
c. Master database contains Login Accounts , endpoints ,linked servers , system configuration settings ,location of the database files for sql server and system defined error messages.
d. Its recovery model is Simple and database id is :1


Model Database :

a. Model database is a template fro creating new database in the SQL Server instance .
b. While creating new database the model database structure will be inherited to new database .
c. Its recovery model is full and database id is 3.


Temp Database :  

a. Temp db stores temporary objects such as stored procedures , cursors, tables ,table variables , the results of join.
b. SQL Server creates temp db every time while restarting the SQL Server .
c. We cannot take backup of temp db .
d. Rebuilding indexes consumes more space in temp db .
e. Its recovery model is simple and database id is 2 .

MSDB Database:

a. It is the back ground job scheduler .
b. It stores jobs, alerts,backup,restore information .
c. Other features such as service broker and DB Mail .
d. SQL ServerAgent runs those jobs and alerts stored in msdb .
e. Its recovery model is simple and database id is 4.

Resource Database :
a. Introduced in SQL Server 2005 . 
b. This database is a hidden database that contains all the system objects such as system stored procedures , system indexes , system functions , system extended stored procedures, system tables and so on .
c. Its database id is 32767.
d. We can perform only file-based copy for resource db hence it is hidden        database and SQL Server doesn't support backing up it .    (mssqlsystemresource.mdf and mssqlsystemresource.ldf files )

Reporting Services Databases

• ReportServer - will be available if you have installed Reporting Services
• ReportServerTempDB - will be available if you have installed Reporting Services

Replication System Database

• Distribution - this database will be available when you have configured Replication

SQL Server doesn't run if the system databases are not available except model database . Model database requires only while creating new database .