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