SQL Server System Databases

MS SQL Server internally uses system databases to manage/maintain every SQL Server activity.
When we install SQL server following system databases created with default recovery model

1. Master(Simple): Records all system level info for an instance of SQL server, SQL server logins, link server
Configuration, service broker end points, system stored proc and functions
Full recovery model for master database is supported and can be changed if master database corrupted rest of the databases will also not work we need to run setup.exe to rebuild the instance of sql server.


2. Model(configurable: Is a template database, when we create new database this new database always contain the exact object/configuration of model database.


3. Msdb(Simple): Is used by SQL Server agent also by alert and jobs when we take backup(Log/diff/tran)
Respective entries also get stored in MSDB..BACKUPSET


4. Tempdb(Simple): Is a temporary database which generally hold temporary objects like (local/global temp table etc) and also intermediate result set. On start of SQL server instance tempdb database get re created & log space always reclaimed. We cannot backup tempdb database.


5. Distribution: When we configure any replication (i.e. Transactional, merge etc) a new Distribution database gets created having data file distribution.mdf and log file distribution_log.ldf


6. Resource: SQL server also uses read only Resource database to maintain all system level information under sys schema. These all system objects physically reside in resource database but logically appear in all databases under sys schema like sys.objects.

Resource database always has database id 32767 & uses mssqlsystemresource.ldf log and mssqlsystemresource.mdf as data file which always present at. :\Program Files\Microsoft SQL Server\MSSQL.\MSSQL\Binn\
SQL server cannot backup/restore detach/attach resource database like other databases, its .ldf & .mdf file should be copied and paste according to system requirement (copy paste is not possible with other databases).
Resource database info can be checked using below query.

SELECT
'Resource Database' AS 'Database Name'
, NAME AS [Database File]
, FILENAME AS [Database File Location]
FROM sys.sysaltfiles
WHERE DBID = 32767