Page 55 - DCAP508_DATABASE_ADMINISTRATION

Unit 4: SQL Server Storage Architecture

In SQL Server 2005, the locations of all the files in a database are recorded in the primary file of Notes
the database and in the master database. The SQL Server Database Engine uses the file location
information from the master database most of the time. However, the Database Engine uses the
file location information from the primary file to initialize the file location entries in the master
database in the following situations:
When attaching a database using the CREATE DATABASE statement with either the FOR
ATTACH or FOR ATTACH_REBUILD_LOG options.

When upgrading from SQL Server version 2000 or version 7.0 to SQL Server 2005.
When restoring the master database.

4.3.1 Logical and Physical File Names

SQL Server files have two names:
logical_file_name
The logical_file_name is the name used to refer to the physical file in all Transact-SQL statements.
The logical file name must comply with the rules for SQL Server identifiers and must be unique
among logical file names in the database.
os_file_name
The os_file_name is the name of the physical file including the directory path. It must follow the
rules for the operating system file names.
The following illustration shows examples of the logical file names and the physical file names
of a database created on a default instance of SQL Server 2005:

Figure 4.3

LOVELY PROFESSIONAL UNIVERSITY 49