SQL Server System Databases

SQL Server includes the following system databases.

Master Database- Master database is primary system database of SQL Serer. It stores all system-level information for a SQL Server system, all other databases and their database files information.Therefore, SQL Server cannot start if the master database is unavailable.

Model Database- Is used as the template for all databases.When a CREATE DATABASE statement is issued, the first part of the database is created by copying in the contents of the model database. The rest of the new database is then filled with empty pages. Modifications made to the model database, such as database size, collation, recovery model, and other database options, are applied to any databases created afterward.

Tempdb Database- Is a workspace for holding temporary objects or intermediate result sets.tempdb is created every time SQL Server is started, the model database must always exist on a SQL Server system. The entire contents of the model database, including database options, are copied to the new database.

MSDB Database- Is used by SQL Server Agent for scheduling alerts and jobs.

Resource database- 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.

If you liked the article hit the Facebook like button.

Keep Learning!