SQL System Databases

An SQL server consists of a number of databases, each with an associated log file for transactional backout. There are two types of database, System and User. User databases are discussed in the next page.

5 system databases are installed with SQL server, with an optional 6th database if replication is running.

 Database  Type  Database file  Log file
 Master  System  Master.mdf  Mastlog.ldf
 Resource  System  Mssqlsystemresource.mdf  Mssqlsystemresource.ldf
 Model  System  Model.mdf  Modellog.ldf
 Msdb  System  Msdbdata.mdf  Msdblog.ldf
 Tempdb  System  Tempdb.mdf  Templog.ldf
 Distribution  System  distmdl.mdf  distmd;.ldf

Master

The master database holds all the system information for an SQL Server, including login accounts, configuration settings, SQL server initialisation information, remote server information, ongoing processes, system error messages, tapes and disks available on the system, and active locks. The master database also stores the location of all the other databases, so it is critical. Without the master database, the other databases cannot be found. You should back up the master database with a regular schedule, and take extra backups after a major upgrade. The recovery model should always be simple.

Model

The model database is used as the template for all new databases created on a system. When a CREATE DATABASE statement is issued, the first part of the database is created by copying in the contents of the model database, then the remainder of the new database is filled with empty pages. Because tempdb is created every time SQL Server is started, the model database must always exist on a SQL Server system. The model database should be backed up after major upgrades. It is small, so you should always take full backups.

Msdb

The msdb database contains operational records such as database backup and restore history, job definitions for replication agents, push/pull subscription and snapshot agents, and maintenance plan history records. It also hold records for task scheduling and alerting. You should backup the MSDB after you have updated it.

Tempdb

The tempdb is a work area for temporary tables and temporary stored procedures. These are dropped automatically when the SQL server is stopped, so there is no need to save the tempdb between server sessions. The tempdb is re-created every time SQL Server is started so the system starts with a clean copy of the database.
By default, tempdb autogrows as needed while SQL Server is running, but it is reset to its initial size each time the database server is started. If the size defined for tempdb is small, then you will use a lot of system resource autogrowing the tempdb to the size needed to support your workload. You can avoid this overhead by using ALTER DATABASE to increase the size of tempdb. No backups are required for the tempdb.

The Resource database

This database was introduced with SQL Server 2005. It contains all the System objects, and so makes upgrading and roll-back easier, as updates just need to be applied to the resource database, instead of having to drop and create each system object individually. The resource database is physically called Mssqlsystemresource.mdf, and should not be renamed or SQL Server will not start. The Resource database must be kept in the same location as the master database and should be neither compressed or encrypted, as this will cause performance and upgrade problems. The resource database is read only and cannot be backed up by using a TSM TDP, or with any other database backup utility. It must be backed up as a file.

The Distribution database

A system database that is stored on the Distributor server. The distribution database does not contain any user tables; it is used by the replication components of SQL Server, to store data including transactions, snapshot jobs, synchronization status, and replication history information. See the replication section for details.

Moving System databases

SQL Server Systems databases have both a physical and a logical name, and the relationship between these two is recorded internally. This means that if the system databases are moved to a different directory, then the logical relationship must be updated. This action would probably be carried out by a DBA, but the process is,

For a planned move, simply record the new location up front with the command