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-level information for an SQL Server, including login accounts, configuration settings, linked servers and endpoints. 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 process will would probably be carried out by a DBA, but the process is,

For a planned move, record the new location up front, then move the databases with SQL server down.

With SQL server active, change the logical file location by running the SQL command for each database you want to move

ALTER DATABASE dbname MODIFY FILE
( NAME = logical_name ,
FILENAME = 'new_path/os_file_name' )

You then stop the SQL server and move the file(s) to the new location. Note that you cannot move the master or Resource database with this procedure.

The TEMPDB is a special case as it is re-created each time the SQL server is started, so for the TEMPDB there is no need to physically move the files to the new location, just run the ALTER DATABASE SQL statement, restart the server, and the new TEMPDB will be created in the new location.

In an unplanned situation, say after a hardware failure, you must start the database in recovery mode, run the ALTER DATABASE SQL command as above, stop the SQL server, move the file, then restart the server again. You would follow this process for every file you needed to recover. You may need SYSADMIN DBA access to run the SQL commands, depending on the status of the SQL server.

Moving the master and Resource Databases

The Resource database must be kept in the same location as the master database. To move them you have to change the start-up parameters for the SQL server. You find these in the Configuration Manager -> SQL Server 2005 Services -> Properties, under the ‘Advanced’ tab.

The location of the Master database and the error log is recorded here, the data file is identified by the –d parameter and the master database log by the –l parameter. The –e parameter identifies the error log. For example

-dC:\Program Files\MSSQLServer\MSSQL_A\DATA\master.mdf;-eC:\Program Files\MSSQLServer\MSSQL_A\LOG\ERRORLOG;-lC:\Program Files\MSSQLServer\MSSQL_A\DATA\mastlog.ldf

To move the master databases, change the parameters above to the new location, stop the SQL Server, move the files to the new location then restart the SQL Server in master-only recovery. You then change the location of the resource database by running the SQL command

ALTER DATABASE mssqlsystemresource MODIFY FILE
(NAME=data, FILENAME= 'new_path_name.mdf');
ALTER DATABASE mssqlsystemresource MODIFY FILE
(NAME=log, FILENAME= 'new_path_name.ldf');

Set the Resource database to read-only by running the following statement.

ALTER DATABASE mssqlsystemresource SET READ_ONLY;

Stop the SQL Server, move the Resource data and log files to the new location then restart the instance of SQL Server in normal mode.

back to top