SQL Server System Databases

When we have installed SQL SERVER , it will have few system databases already installed as listed. These database is required to run the SQL Server.

  •  master
  •  tempdb
  •  model
  •  msdb
  • resource  (Hidden Database )
  • distribution (Hidden Database )

Master Database:  It is primary database, without it, sql server cannot started. It contained information about the object within SQL Server instance.

  • Databases on a instance
  • Files for each databases
  • AlwaysON
  • Database mirroring
  • Configurations
  • Logins
  • Resource Governor
  • Endpoints

tempdb Database : it’s basically deal with temporary objects created by temporary process that sql server or temporary objects by user or application.

like temporary table, table variables, store procedure, cursors.. etc.

Note:  tempdb is that it is re-created every time SQL Server is restarted.

model database : It is used as a template each time when you create database as name implies.

Note:

  • If you want to table have in all the database in an instance, you need to create that table in model database as result when new database will be created that table exists in new database. 
  • If model database is offline or not exists, tempdb can not be created, as we know tempdb always re-created when sql server restart, so there is not template defined for tempdb in case model is not there. 

msdb database : It is mainly used as backend database for SQL Server Agents. whenever a job is Create/schedule the metadata for that job is stored in this database. msdb have following component. Service brokers

  • Alerts
  • Log shipping
  • SSIS packages
  • Utility control point (UCP)
  • Database mail
  • Maintenance plans

Primary database to manage the SQL Server Agent configurations.

resource database: It is read-only, hidden database, purpose to improve the upgrade process from one version of SQL Server to another.

  • All the system objects are stored in this database.
  • Can not be backed up or restored.

Note: Do not attempt to move or change this database unless Microsoft Support direct you.

distribution database : This data is responsible for replication, this database does not exist until configured this instance as a distributor for replication.

  • All the metadata and history of various  replication stored with this database.
  • Prior configuring replication, you must configure this database.
System Databases
System Databases
  • Arif Jameel

    Anyone wants more clarification can discuss here…