system databases in SQL server


When you Install SQL Server on a PC, a few databases installed on a PC called Sys databases. SQL Server utilizes Sys databases to store its config. Settings and data. And data about every one of the databases installed in the current SQL Server instance. System databases in SQL server used to track operations. And give a temporary work region to clients for doing databases operations.\

system databases in SQL server

List of Databases in SQL Server

Below you can find the List of Databases in SQL Server 2012

  • Master database.
  • Msdb database.
  • Model database.
  • Tempdb database.
  • Resource database.

We can see All DB's except Resource DB in Object Explorer.

The master Database

The master database contains the data about every database. Installed on the present instance of SQL Server. It additionally contains design and status about the current SQL Server instance. This information saved in Sys tables and can be accessed by DBA's using system functions and views. When Developer creates a new DB. All corresponding details stored in master DB track the information. Try to avoid changes in master DB, because changes in a master DB entire server should corrupt.

The model Database

The model database gives you a format for making new databases. When new DB created all model DB objects copied in the new DB's. Any changes are done in model DB changes made in user-created databases on a server.

The MSDB Database

The msdb DB have configuration data about different services. for example, SQL Server Agent, Database Mail, and Service Broker. This DB stores the job scheduling information and alerts in SQL Server Agent service. Try to avoid the changing the information in MSDB, if it requires use SP's and views of msdb.

The TEMPDB Database

The tempdb DB is saved the temporary tables created by the users. SQL Server uses tempdb DB to save the results of complex queries. All tempdb tables, views, and databases dropped when SQL Server restarted.

The Resource Database

The Resource DB is a read database that stores all the sys objects contained in SQL Server. Sys objects contained in the DB; Yet they are available in the sys.shema in each database. mssqlsystemresource.mdf and mssqlsystemresource.ldf are the physical documents of the Resource database.