Four system databases for SQL Server

(1) The master database is the most important database for the SQL Server system, and it records all system information for the SQL Server system. These system information includes all login information, system setup information, SQL Server initialization information, and other system databases and user database related information. Therefore, if the master database is unavailable, SQL Server cannot start. In SQL Server 2005, system objects are no longer stored in the master database, but are stored in the Resource database.

(2) The model database is used as a template for all databases created on the instance of SQL Server. Because tempdb is created every time SQL Server is started, the model database must always exist in the SQL Server system. When the CREATE DATABASE statement is issued, the first part of the database is created by copying the contents of the model database, and then the remainder of the new database is populated with empty pages. If you modify the model database, all of the databases that you create later inherit those modifications. For example, you can set permissions or database options or add objects, such as tables, functions, or stored procedures.

(3) The msdb database is a proxy service database that provides storage space for its alarms, task scheduling, and recording operators ' operations. If you are using a job in your database, remember to back up and restore msdb when you do a database migration or a server change, and the madb restore is in single-user mode.

(4) tempdb is a staging database that provides storage space for all temporary tables, temporary stored procedures, and other temporary operations. The tempdb database is used by all databases of the entire system, regardless of which database the user uses, and all temporary tables and stored procedures that they create are stored on tempdb. Each time SQL Server starts, the tempdb database is re-established. When a user disconnects from SQL Server, its temporary tables and stored procedures are automatically deleted.

Four system databases for SQL Server