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