#
SQL Server System Databases
This tutorial explains to you what/ which are the SQL Server System Databases.
When you install a Microsoft SQL Server 2016, you will see some databases created automatically. These are named "SQL Server System Databases". These databases are used by the SQL Server to managed its work.
Here are the SQL Server System Databases and their roles:
master(= Core System Database)
The "master" database Records all the system-level information for an instance of SQL Server. In the "master" system database we have: all the logins, linked servers, endpoints, the instance configuration, the databases of the instances, files for each database and other system-wide configuration settings.
Because the master database hold all the information related to these things, it's important that you take a backup of the master database after configuring any of these server level changes.
tempdb
The tempdb system database is a global resource that is available to all users connected to the instance of SQL Server and is used to hold the following:
- intermediate results of query processing and sorting;
- global or local temporary tables, temporary stored procedures, table variables or cursors;
- online index operations;
- row versions that are generated by data modification transactions in a database that uses read-committed using row versioning isolation or snapshot isolation transactions.
It is important to know that tempdb is re-created every time the SQL Server service is started.
Resource
The Resource database is a read-only database that contains all the system objects that are included with SQL Server. SQL Server system objects, such as sys.objects, are physically persisted in the Resource database, but they logically appear in the sys schema of every database. The Resource database does not contain user data or user metadata.
msdb
The msdb database is used by SQL Server Agent for scheduling alerts and jobs and by other features such as SQL Server Management Studio, Service Broker and Database Mail. For example, SQL Server automatically maintains a complete online backup-and-restore history within tables in msdb.
model
The model database is a template database that is copied into a new database whenever it is created on the instance. Database options set in model will be applied to new databases created on the instance, and any objects created in model will be copied over as well. Modifications made to the model database, such as collation, database size, recovery model, and other database options, are applied to any databases created afterward.