SQL SERVER SYSTEM DATABASES

Hi All,

SQL DBA Readers..This is Nagaraju.Nadiminti..

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

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

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 nonpermanent
storage, backups and restores are not
allowed for this database.

Resource database

The Resource database is a readonly 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


Review your progress now and go through with the questions below.