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.

The following 7 databases are installed with SQL server

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.

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.

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.

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.

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.

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. It must be backed up as a file.

Pubs and Northwind

These are not really system databases, but sample user databases based on a book publishing company and a fictitious company called Northwind Traders. They are used in many of the examples and help pages.

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

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

then stop the SQL server, move the databases then restart the SQL server. For an unplanned move, say following a disk crash, then

• Stop the SQL server instance
• Start it up again in master only mode with the command NET START MSSQLSERVER /f /T3608
  This is the command for the default server instance. If you are using a named instance then substitute MSSQLSERVER with MSSQLinstancename
• For every file you are moving, run an alter database command as follows

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

• Stop the SQL server again
• Move the files to the new location
• Restart the SQL server manually
• Check that the moves have completed successfully and all the moved files are still visible to the system
• Backup the Master database as it will have changed to reflect the new locations

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.

Moving the Master Database

To move the Master Database you have to change the start-up parameters for the SQL server. You find these in the SQL server Configuration Manager -> SQL Server 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.

Moving the Resource Database

Microsoft recommends that you do not move the Resource database. It is possible to do it with the server in master only mode by using SQL ALTER DATABASE statements, but I recommend that you read the Microsoft documentation before deciding to go ahead.

back to top

By entering and using this site, you accept the conditions and limitations of use