Skip to content

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