Each time you install any SQL Server Edition on a server; there are four primary system databases, each of which must be present for the server to operate effectively.
Master
- ·file locations of the user databases
- ·login accounts
- ·server configuration settings
- ·linked servers information
- ·startup stored procedures
Model
- ·A template database that is copied into a new database
- ·Options set in model will be applied to new databases
- ·Used to create tempdb every time the server starts
Msdb
- ·Support SQL Server Agent
- ·SQL Server Management Studio
- ·Database Mail
- ·Service Broker
- ·History and metadata information is available in msdb
- ·Backup and restore history for the databases
- ·History for SQL agent jobs
Tempdb
- ·The tempdbis a shared resource used by SQL Server all users
- ·Tempdb is used for temporary objects, worktables, online index operations, cursors, table variables, and the snapshot isolation version store, among other things
- ·It is recreated every time that the server is restarted
- ·As tempdb is non-permanent storage, backups and restores are not allowed for this database.
Reporting Services Databases
- ReportServer – available if you have installed Reporting Services
- ReportServerTempDB – available if you have installed Reporting Services
Replication System Database
- Distribution – available when you configure Replication
Resource Database
·Read-only hidden database that contains all the system information
·Can’t back up the Resource database
·Must copy paste the file
- C:Program FilesMicrosoft SQL ServerMSSQL11.MSSQLSERVERMSSQLBinn