System Databases: master, model, msdb, tempdb, and resource Responsibilities

Learning outcomes

ServiceHub now reaches the correct instance, but a recovery review reveals a dangerous sentence in the runbook: “back up every database the same way.” SQL Server’s system databases do not have identical roles, durability expectations, or restore procedures. A practitioner must know what each database represents before a failure, because master, model, msdb, tempdb, and the Resource database are not five ordinary user databases with special names.

1. master: instance identity and startup-critical metadata

master records instance-wide information such as logins, endpoints, linked servers, system configuration metadata, and the existence/location of databases. SQL Server requires master to initialize the instance. This is why an operator cannot treat it as “just another small database.” If master is unavailable, normal instance startup is compromised and recovery may require special startup/rebuild/restore procedures.

System objects such as sys.objects are not physically stored in master in modern SQL Server; they reside in the Resource database and are exposed logically through each database’s sys schema. That separation is one reason direct updates to system tables are unsupported—use documented catalog views and administrative statements instead.

2. model is a template; inheritance is real operational behavior

model is the template for newly created databases. Its settings can affect new user databases, including defaults such as recovery model and other database options. Parts of model are also involved when SQL Server recreates tempdb at startup. Therefore a casual “best-practice” modification to model is not local—it changes future database creation behavior.

The course avoids putting ServiceHub tables or organization-specific objects in model. Reproducible infrastructure should create databases explicitly from scripts so that defaults are reviewable in source control instead of hidden in mutable instance state.

USE master;GOSELECT name, recovery_model_desc, collation_name, compatibility_levelFROM sys.databasesWHERE name IN (N'model', N'tempdb');GOIF DB_ID(N'ServiceHubModelProbe') IS NOT NULLBEGIN    ALTER DATABASE ServiceHubModelProbe SET SINGLE_USER WITH ROLLBACK IMMEDIATE;    DROP DATABASE ServiceHubModelProbe;END;GOCREATE DATABASE ServiceHubModelProbe;GOSELECT name, recovery_model_desc, collation_name, compatibility_levelFROM sys.databasesWHERE name IN (N'model', N'ServiceHubModelProbe');GO

The new database will inherit important defaults from the instance/model creation environment, but do not overgeneralize every property as a byte-for-byte model copy. The lesson’s purpose is to make inheritance observable, then teach explicit configuration for production databases.

3. msdb is operational history and automation state

msdb is used by SQL Server Agent for jobs, schedules, alerts, operators, and related automation metadata. It also stores backup/restore history and other operational information. If the ServiceHub team painstakingly creates Agent jobs, operators, backup history, Database Mail configuration, and maintenance metadata but never protects msdb, a server rebuild can restore user data while losing the automation/control-plane state required to operate it.

SELECT TOP (20)    bs.database_name,    bs.type AS backup_type_code,    bs.backup_start_date,    bs.backup_finish_date,    bs.is_copy_only,    bs.has_backup_checksumsFROM msdb.dbo.backupset AS bsWHERE bs.database_name = N'ServiceHubLab'ORDER BY bs.backup_finish_date DESC;GO

An empty result does not mean the database has never been backed up everywhere; it means this instance’s current msdb does not contain matching history. History can be purged, msdb can be replaced/restored, and external backup systems can maintain separate evidence. Treat msdb history as one operational source, not an immutable audit ledger.

4. tempdb is a global scratch space that starts fresh

tempdb holds user-created temporary objects, internal work objects for sorts/hashes/spools, and row-version stores used by several engine features. It is shared by all sessions on an instance. SQL Server recreates tempdb every time the Database Engine starts, so objects do not survive an engine restart and SQL Server does not support normal BACKUP/RESTORE operations for tempdb.

SQL Server 2025 adds capabilities such as Accelerated Database Recovery support in tempdb and tempdb space resource governance, but those additions do not turn tempdb into durable application storage. Treating a temp table as a durable queue because it “has been there for weeks” mistakes uptime for persistence.

SELECT    d.name,    d.state_desc,    d.recovery_model_desc,    d.create_date,    d.collation_nameFROM sys.databases AS dWHERE d.name = N'tempdb';GOSELECT    file_id,    name AS logical_name,    type_desc,    size * 8.0 / 1024 AS size_mb,    growth,    is_percent_growth,    physical_nameFROM tempdb.sys.database_filesORDER BY file_id;GO

The create_date for tempdb corresponds to the current instance startup’s recreated database, which is useful corroborating evidence. File layout deserves deliberate capacity planning, but Chapter 12 will tune tempdb with workload evidence rather than repeating “one file per core” folklore.

5. Resource database: shipped system objects, not a user-maintained database

The Resource database is a read-only database containing the SQL Server system objects that ship with the product. Each instance has its own Resource files. It is hidden from normal sys.databases enumeration and is serviced as part of SQL Server updates. You should not modify or move its files. SQL Server cannot back it up through ordinary BACKUP DATABASE.

SELECT    SERVERPROPERTY('ResourceVersion') AS resource_version,    SERVERPROPERTY('ResourceLastUpdateDateTime') AS resource_last_update;GOSELECT OBJECT_DEFINITION(OBJECT_ID(N'sys.objects')) AS sys_objects_definition;GO

The Resource version is useful when troubleshooting servicing consistency, but do not turn it into a home-grown patch-management mechanism. Use supported servicing/build evidence and current Microsoft update documentation.

6. Deliberately wrong approach: “back up all five system databases nightly”

The sentence sounds safe but is technically imprecise. Microsoft recommends protecting master, model, and msdb according to their change rate/business need. tempdb is recreated and cannot be backed up/restored normally. Resource is a product file rather than an ordinary SQL backup target. If replication is configured, distribution introduces another system database that must be considered.

7. Hands-on lab: prove responsibilities without changing system databases

SELECT    d.database_id,    d.name,    d.state_desc,    d.recovery_model_desc,    d.compatibility_level,    d.collation_name,    d.create_dateFROM sys.databases AS dWHERE d.database_id <= 4ORDER BY d.database_id;GOSELECT    DB_NAME(mf.database_id) AS database_name,    mf.name AS logical_file_name,    mf.type_desc,    mf.physical_name,    mf.size * 8.0 / 1024 AS size_mbFROM sys.master_files AS mfWHERE mf.database_id IN (1,2,3,4)ORDER BY mf.database_id, mf.file_id;GOSELECT SERVERPROPERTY('ResourceVersion') AS resource_version,       SERVERPROPERTY('ResourceLastUpdateDateTime') AS resource_last_update;GO

Then inspect the disposable ServiceHubModelProbe created earlier, record whether its recovery model and collation reflect model/server defaults, and remove only the probe:

USE master;GOIF DB_ID(N'ServiceHubModelProbe') IS NOT NULLBEGIN    ALTER DATABASE ServiceHubModelProbe SET SINGLE_USER WITH ROLLBACK IMMEDIATE;    DROP DATABASE ServiceHubModelProbe;END;GO

Verification checklist

• You can state why master is startup-critical.
• You can explain one real consequence of changing model.
• You can identify what important operational state msdb contains.
• You can explain why tempdb cannot be your durable business-data store.
• You can describe Resource as shipped system-object storage rather than a normal user database.
• You did not directly update system tables or alter a system database for the exercise.

8. Production judgment and next bridge

System-database protection belongs in the same recovery design as user data. A user-database backup restores business rows, but it does not automatically reconstruct logins/SIDs, Agent jobs, operators, server configuration, linked servers, certificates, endpoints, or every external dependency. Keep those artifacts scripted where possible and protect the system databases that contain irreplaceable instance state.

The next lesson moves from logical database roles to physical storage layout: data files, log files, filegroups, autogrowth, and instant file initialization. The key transition is that a database is not “one file,” and file extensions do not define engine semantics.

Authoritative references

• System databases — roles of master, model, msdb, tempdb, and Resource
• tempdb database — recreation, workloads, files, and SQL Server 2025 tempdb changes
• Back up and restore system databases — which system databases require protection and recovery caveats
• Resource database — read-only system-object storage and servicing behavior