SQL Server System Databases

There are basically 2 types of Databases available in SQL server.

1. System Databases.

2. User Databases,

  • The System databases are used to store system wide data and metadata.
  • User database are created by users who have the appropriate level permissions to store application data.

SQL Server System Databases

System Databases:

  • System databases are mandatory databases.
  • Once install SQL services 5 system database are installed,

1. Master.

2. Model.

3. MSDB.

4. Temp db.

5. Resource (Hidden).

  • Once we configure Replication automatically ‘Distribution‘ database is created,
  • We cannot implement High availability’s like Log shipping,Mirroring,Replication on system database.

Master

  •  Master database is important database in Sql server .
  • With out master database SQL Server cannot start.
  • Hear all the information like MDF,LDF,Login account information,System configuration settings, Lined server information recorded in Master databases,
  • Master database recovery model is ‘SIMPLE’.

Model: 

  • Model database it act as a template of new database.
  • If we create any new database the Model database structure will be created on new database.
  • Model database recovery model is ‘FULL’.

MSDB:

It consists of total automation information such as all Job information, schedule information,Backups,Restores information stored hear. MSDB database recovery model is  ‘SIMPLE’

TEMPDB: 

Temp db store in all temporary objects. We cannot take backup on temp db, Because when SQL server restarts new temp db will create old one deleted.  

Resource Database: 

Resource database is hidden database.It consists of all system defined objects physically. We cannot work on Resource database. It maintain service packs changes. The DB file are present in C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn .

 Note:  Once we installed Reporting services automatically 2 databases are created.

1. Reportserver.

2. ReportserverTempDB.

 

POA for Log Shipping Creation

Types of SQL Server Backups

What is SQL Server Log Shipping

Get SQL Dba Tutorial Updates!

Signup now and receive an email once I publish new content.

I agree to have my personal information transfered to MailChimp ( more information )

I will never give away, trade or sell your email address. You can unsubscribe at any time.

Add Comment