SQL Server System Databases

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