Introduction System Databases in SQL Server

SQL Server 2008 uses five system databases to store system information, track operations, and provide a temporary work area.

Master:  Master database holds information about the running server’s databases and server’s Configuration. It contains all of the system level information for SQL Server – all logins, linked servers, endpoints, and other system-wide configuration settings.

SQL Server cannot start if the master database is unavailable because master database records the existence of all other databases and the location of those database files and records the initialization information for SQL Server.

We can use master database objects in other databases e.g. store procedure in master database may be called from user database.

If master database is corrupted and sql server is start with damaged Master database then master database will be restored from backup.Sometimes Master database is corrupted and we cannot start SQL Server.  Then master database cannot be restored.  In this situation rebuild the master database using command prompt and restored from latest backup once again.

Msdb: Msdb database Maintains lists of activities, such as backups and jobs, Maintenacne  plan, DTS packages ,database mail, Service Broker ,and tracks which database backup goes with which user database.  By default msdb use simple recovery model. It  is database for sql agent.

Model: Model database is template database for new databases. Any object placed in the

 model database will be copied into any new database.

Tempdb: The tempdb is a temporary workspace  for storing temporary tables, worktables that hold intermediate results during the sorting or query processing ,batches, stored procedures (including Microsoft stored procedures), and the     SQL Server engine itself. If SQL Server needs to create temporary heaps or lists during query execution, it creates them in tempdb. tempdb is dropped and recreated when SQL Server is restarted.

The tempdb is created from model database and reset to its last configured size.
we cannot backup and restore tempdb.

Resource: This hidden and read-only database, added in SQL Server 2005. It contains

contains all the system objects that are included with SQL Server. SQL Server system objects, such as sys.objects, are physically persisted in the Resource database, but they logically appear in the sys schema of every database. The Resource database does not contain user data or user metadata.