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.
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.
