SQL Server System Databases - Master, Model, Temp ,MS DB and Resource

You may have observed after installing SQL Server that some databases are automatically created under system databases node. These are five system databases as master, model, msdb, tempdb and resource. Resource database is hidden and cannot be seen in this node rest four databases are visible in the node. If you have observed, you will not get delete option when you right click on it so basically you will not be able to drop system databases as they contain metadata information.

              

Master Database-  

The master database is primary system database of SQL Server. It keeps and manages all system level information i.e. system information's which are defined at instance level such as system configurations, logon accounts, linked server, all other databases and their database files etc. Regularly you have to take master database backup because if it is corrupted then you won’t be able to start SQL Server and you have to follow different strategy to restart it (SQL Server cannot start if the master database is unavailable). Also avoid to create user objects in master database.

Model Database- 

The model database acts as a template for all the databases which have been created on particular instance. If you create any object on model database and if you create any user database on that instance then those objects will also be created in new user database. So you can use model database in such scenarios where you have to repeat particular objects in all newly created database. 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- 

As the name suggests, tempdb holds temporary data like local temporary tables, global temporary tables, temporary table variables and cursors, internal objects created by database engine for sorting, online index activities etc. If you have observed in SSMS there is no option for tempdb backup and restore so you cannot backup or restore this database as this database is re-created every time SQL Server starts.

MSDB Database- 

SQL Server agent information is managed by msdb database, all jobs and details, different configured alerts and operators etc. are managed by it. For example, if you perform a backup of database then all operation informations and events like backup type, backup time, name of the source performing the backup and device where performing backups etc. are stored in msdb database. By default msdb is in simple recovery model but in some scenarios it is recommended to use full recovery model.

Resource database- 

The resource database is hidden and not visible in system database list or node. It is read-only database and contains system objects (such as sys.objects) linked to sys schema in all the databases. Prior to SQL Server 2005 all system stored procedures, system functions and system views were defined and linked in master database but later all these have been defined in resource database. Every instance of SQL Server is associated with only one resource data file. The default path for resource database  is <drive name>:\Program Files\Microsoft SQL Server\MSSQL11.<instance_name>\MSSQL\Binn\ as in [C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn].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.

Distribution Database: 

The distribution database stores metadata and history data for all types of replication, and transactions for transactional replication.

In many cases, a single distribution database is sufficient. However, if multiple Publishers use a single Distributor, consider creating a distribution database for each Publisher.