Every SQL Server relies on five primary system databases, each of which must be present for the server to operate effectively. For those who are new to SQL server or who have never set up a server from scratch may find these databases mysterious. I think since you have already installed your SQL server 2012 so it will be easy for you to understand them. It’s important to understand their purposes and some key activities that you should be doing. Remember, except tempdb and resource database, all the databases should be backed up on regular basis.

Now, you must connect to you SQL instance and see where these databases are. Open Management Studio and click on the databases > system databases to see your very own databases. Let’s talk about each of them one by one.

 

Master    

THE most important database that is required to run SQL server. Though all four databases are required for SQL server to be up and running, the master database stores basic configuration information for the server instance and without master database SQL server will not run at all. Important things that master database contains

• Information about all databases and their logical and physical files
• Information about user logins
• Server configuration settings
• Startup procedures

You should be careful while playing with Master Database, you shouldn’t be alerting/updating/deleting any object or record in it. Causing this may crash your SQL server. However, I will not stop you and you must try modifying some of the tables in your testing environment. Try crashing it and recovering it – that’s how you will learn. Don’t attempt it in production.

 

Model

 

The model database is a template database that is copied into a new database whenever it is created on the instance. Even on a system where new databases are created infrequently, the model database must exist because it is used to create tempdb every time the server starts.  It’s a best practice to backup model whenever a change is made. This includes

• Any Database options, like recovery model, DB Collation etc
• Any objects created
• Initial data file size
• Initial tempdb size

MSDB

The msdb database is used to support a number of technologies within SQL Server, including the SQL Server Agent, SQL Server Management Studio, Database Mail, and Service Broker.

MSDB stores

• SQL Server Agent history
• Metadata information for Agent Job
• Backup and Restore history for the databases on the server

Depending on situations, this database can grow out of control and you must maintain the history as much as you should. It’s important to take backup of MSDB on regular interval. Sometime, changing database recovery model from Simple to full is advisable.

TEMPDB

The tempdb system database is like a shared temporary storage resource used by a number of features of SQL Server, and made available to all users.  Tempdb is used for

• Temporary objects
• Worktables
• Online index operations
• Cursors
• Temp tables variables and table variable

It is recreated every time when SQL server is restarted, which means that no objects in tempdb are permanently stored. Since tempdb is non-permanent storage, backups and restores are not allowed for this database.

Resource database

The Resource database is a read-only database that contains all the system objects that are included with SQL Server. This database is written at the time of SQL server installation only. 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. User data or user metadata is not stored in this database because it’s created at the time of installation only and no user or user activity is involved at this time. It makes upgrading to a new version of SQL Server an easier and faster procedure.

Now, you must have seen all the databases except Resource – Resource database is not visible in Management Studio. However, if you want, you can see it on <drive>:\Program Files\Microsoft SQL Server\MSSQL12.<instance_name>\MSSQL\Binn\ Try locating your resource database file – mssqlsystemresource.mdf