SQL Server
Databases

–        
Sql server is basically database management system,
databases are mainly classified in two categories: System Databases & User
Databases.

–        
In this document, we will get system databases in
depth based on its 3 perspectives: Its functionality, storage & backups.

Database Classification:

System Databases

·       
System databases are used by system only i.e by sql
server & sql server services.

·       
There are total 4 system databases and they are –
Master, Msdb, Model, Tempdb. There is one more -Resource database which is
hidden.

·       
System database consists of all configuration of sql
server services and the different user databases which are attached to the
server.

 

1.     Master
Database

Functionality:

–        
Sql server services uses master database to record all
the system related information including of all logins, endpoints, linked
servers, accounts, configuration settings etc.

–        
Tables stored inside the master db are called as
master tables, they store all the parameters related to all users &
databases that sql server holds.

Storage:

–        
Master database is stored in master.mdf file and
transaction logs are stored in masterlog.ldf file.

Backups:

–        
Daily backup is must for master database.

–        
If Master database becomes unavailable, sql server
won’t function. You will not be able to access any of the user databases that
are attached to the server. You need to restore the master db from backup and
then restart system in this case.

–        
Master database must be backed up whenever there is
changes in metadata, creating/dropping of any database, modification in logins,
Modification in server or database configuration.

–        
Backus of master database are small and happened very
quickly.

–        
It is highly recommended not to create user objects in
master db, else master db is required to be backed up frequently.

 

2.     MSDB
Database

Functionality:

–        
MSDB Is used by sql server agent service to store the
information related to scheduled jobs (like backups jobs), alerts, operators,
job steps.

Storage:

–        
MSSB database is stored in MSDBdata.mdf & logs are
stored in MSDBlog.ldf.

Backups:

–        
Unlike master db, MSdb must be backed up nightly (or
once each day).

–        
Master db need to be backed up whenever you add/alter
jobs, operators, alerts so that you don’t lose any modifications made.

–        
Backups are small even if it has dozens of jobs,
backup window is also small.

 

3.     Model
Database

Functionality:

–        
The name itself defines model database is sort of
template or mode for new databases.

–        
While creating new user databases, suppose when you
below statement

CREATE
DATABASE Address;

It will
simply create database named as Address with the all configuration settings
from model database. Model database simply acts as a template while creating
new user databases.

Storage:

–        
Model database is stored in model.mdf. Transaction
logs are stored in modellog.ldf.

Backups:

–        
Model database does not require backups daily.

–        
In case if you make any changes in model database, it
is recommended to backup the model db.

 

4.     Tempdb
Database

Functionality:

–        
Tempdb database is used as a scratch pad for
temporarily holding place while processing data.

–        
Sql server automatically uses tempdb When sql server
processes large queries that are not able to handle in memory.

–        
Users can use tempdb, Tables name begins with sign #
are by default the tables from tempdb.

Storage:

–        
Primary data file is tempdb.mdf & log file name is
templog.ldf.

–        
Tempdb size is too small.

Backups:

–        
Backups are not necessarily required for tempdb.

–        
Tempdb gets re-created every time when sql server
services restarts or server reboots.

 

5.     Resource
database

–        
This is hidden database which is read-only.

–        
It records all system objects.

–        
Resource database cannot be backed up.

User Databases

·       
The name itself defines the user databases are created
and used by users for storing some intentional data- data could be business or
personal related.

·       
Note that you cannot create system database the way
you create user databases. You can create user database that can hold ”system information”,
this can’t be qualified as system database.