Working with SQL Server System Databases

This post will brief about the SQL Server system databases.

SQL Server System Databses:

•  master

The master database records all of the system level information for a SQL Server system. It records all login accounts and all system configuration settings. master is the database that records the existence of all other databases, including the location of the database files. master records the initialization information for SQL Server.

• tempdb

The tempdb holds all temporary tables and temporary stored procedures. It also fills any other temporary storage needs such as work tables generated by SQL Server. It is used to hold the temporary user objects that are explicitly created, such as: global or local temporary tables, temporary stored procedures, table variables, or cursors and internal objects that are created by the SQL Server Database Engine, for example, work tables to store intermediate results for spools or sorting.

Operations within tempdb are minimally logged. This enables transactions to be rolled back. tempdb is re-created every time SQL Server is started so that the system always starts with a clean copy of the database.

• model

The model database is used as the template for all databases created on an instance of SQL Server. Because tempdb is created every time SQL Server is started, the model database must always exist on a SQL Server system.

• msdb

The msdb database is used by SQL Server Agent for scheduling alerts and jobs and by other features such as Service Broker and Database Mail.

Restrictions on these databases:

	master	tempdb	model	msdb
Adding filegroups	√	√	√
Backing up or restoring the database		√
Changing collation. The default collation is the server collation	√	√	√	√
Changing the database owner (owned by dbo)	√	√	√
Creating a full-text catalog or full-text index	√
Creating triggers on system tables in the database	√
Creating a database snapshot		√
Dropping the database	√	√	√	√
Dropping the guest user from the database	√	√	√	√
Enabling change data capture	√	√	√	√
Participating in database mirroring	√	√	√	√
Removing the primary filegroup, primary data file, or log file	√	√	√	√
Renaming the database or primary filegroup	√	√	√	√
Running DBCC CHECKALLOC		√
Running DBCC CHECKCATALOG		√
Setting the database to OFFLINE	√	√	√	√
Setting the database or primary filegroup to READ_ONLY	√	√	√	√
Creating procedures, views, or triggers using the WITH ENCRYPTION option			√

NOTE : The encryption key is tied to the database in which the object is created. Encrypted objects created in the model database can only be used in model.