Introduction to System Databases – master, model, msdb, tempdb and resource

by Manzoor Siddiqui · Published July 19, 2015 · Updated September 24, 2015

303views

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. Here I am using SQL Server 2012, so let’s see brief information about these databases.

1. master database

The master database keeps and manages all system level information i.e. system informations which are defined at instance level such as system configurations, logon accounts, linked server 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. Also avoid to create user objects in master database.

2. 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.

3. 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.

4. 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.

5. 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 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].