When you install SQL Server on a computer, some databases are called system databases and are installed on the computer. SQL Server uses system databases to store its configuration settings and information and information about all the databases installed in the current SQL Server instance. System databases are also used to track operations and provide a temporary work area to users for doing databases operations.
The following 5 databases are available in SQL Server 2012:
- The master database.
- The msdb database.
- The model database.
- The tempdb database.
- The Resource database.
All the databases except the Resource database are displayed in Object Explorer.
Let's discuss the purpose of all the databases in details.
The master Database
The master database contains the information about all the databases installed on the current instance of SQL Server. It also contains configuration and status about the current SQL Server instance. This data is stored in system tables and can be accessed by database administrators using system views and functions. When a new database is created, entries corresponding to that database are also created in that master database to track the file structure and logical database structure of the new database. You should avoid modifying the master database since modification in the master database may corrupt the entire server.
The model Database
The model database provides you a template for creating new databases. All the objects in the model databases are automatically copied to the new user database. Any modification in the model database is also applied to all the user databases created on the server.
The msdb Database
The msdb database contains configuration information about various support services, such as SQL Server Agent, Database Mail and Service Broker. The SQL Server Agent service uses the msdb database to store scheduling information about jobs and alerts. Modification of the data of the msdb database should be avoided; however, if modifications are necessary, you should use the stored procedures and views of the msdb database to modify the database.
The tempdb Database
The tempdb database is used to store temporary tables generated automatically by SQL Server and the temporary tables created explicitly by the users. SQL Server uses the tempdb database to store the intermediate results of the queries when doing complex database operations. Whenever SQL Server restarts, the tempdb database is dropped and then recreated.
The Resource Database
The Resource database is a read-only database that stores all the system objects contained in SQL Server. Physically system objects are contained in the resource database, but logically they are present in the sys.shema in every database. The mssqlsystemresource.mdf and mssqlsystemresource.ldf are the physical files of the Resource database.
I hope you like my article.