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 tempdb is 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 Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn