Datasheet

A model distribution database is installed by default and is used in the creation of a distribution
database used in replication. It is installed in the same location as the rest of the system databases and is
named
distmdl.mdf.
SQL Server 2005 Database Storage
All system and user databases (including the Resource database) are stored in files. There is always a
minimum of two files: one data file and one transaction log file. The default extension for data files is
.mdf, and the default for transaction log files is .ldf.
The default location for the system database files is
<drive>:\Program Files\Microsoft SQL
Server\MSSQL.X\MSSQL\Data\
, where <drive> is the installation drive and X is the instance number
(
MSSQL.1 for the first instance of the database engine). The following table lists the names and default
locations for system database files associated with the first instance of SQL Server.
System Database Physical Location
Master <install path>\MSSQL.1\MSSQL\Data\master.mdf
<install path>\MSSQL.1\MSSQL\Data\mastlog.ldf
Model <install path>\MSSQL.1\MSSQL\Data\model.mdf
<install path>\MSSQL.1\MSSQL\Data\modellog.ldf
MSDB <install path>\MSSQL.1\MSSQL\Data\msdbdata.mdf
<install path>\MSSQL.1\MSSQL\Data\msdblog.ldf
TempDB <install path>\MSSQL.1\MSSQL\Data\tempdb.mdf
<install path>\MSSQL.1\MSSQL\Data\templog.ldf
Resource <install path>\MSSQL.1\MSSQL\Data\Mssqlsystemresource.mdf
<install path>\MSSQL.1\MSSQL\Data\Mssqlsystemresource.ldf
When it comes to the system databases, the following guidance is given: Don’t mess with them. Your abil-
ity to manipulate the system databases in SQL Server 2005 has been extremely limited by the developers
at Microsoft. Overall, this is a good thing. Generally speaking, the only thing you are permitted to do
with system databases is back them up or move them to faster, more reliable disk arrays if they prove to
be a performance bottleneck. The ability to modify the data contained in system tables through ad hoc
updates that existed in prior releases has been almost completely removed from SQL Server 2005. To
modify the system catalog, the server must be started in Single-User mode and even then, activity is
restricted and is not supported by Microsoft.
Data Files and Filegroups
When a user database is created, it must contain at least one data file. This first data file is known as the
primary data file. The primary data file is a member of the default Primary filegroup. Every database has
one Primary filegroup when created and is made up of at least the primary data file. Additional data
17
Introducing SQL Server 2005
04_047046 ch01.qxp 10/18/06 12:18 AM Page 17