Major System Databases of SQL Server

Master Database:

1. It is a system database which contains server’s configuration.
2. Used for Backup for master database.
3. SQL Server can't be started without it.

Msdb Database: Stores information regarding,

• Database backups.
• Agent information,
• DTS packages,
• SQL Server jobs,
• Replication information such as for log shipping.

Temp Database:

1. Temp Database holds temporary objects such as global temporary tables, local temporary tables and temporary stored procedure.
2. It stores version information.
3. This is a temporary database used to store temporary, tables, cursors, indexes, variables etc.
4. Temp Database is re-created every time SQL Server is started.
5. Auto shrink is not allowed for temp Database.

Model database

It is a Template database used in the creation of a new database

Resoure Database

1. Resource Database is a read-only database.
2. It contains all the system objects that are included with SQL Server.
3. The Resource database does not contain user data or user metadata.

Other Related Database of Sql Server Distribution, ReportServer and ReportServerTempDB

Distribution: Used for SQL Server replication only.

ReportServer: To store metadata and other object definitions.

ReportServerTempDB: Acts as a temporary storage for reporting services.