:::: MENU ::::

Logical and physical file names of SQL Server data and log files

  • Dec 10 / 2011
  • 0
dbDigger, SQL Server Training, Storage, System Stored Procedures

Logical and physical file names of SQL Server data and log files

SQL Server files have two types of file names.

Logical File Name

Logical file name of SQL Server data or log file is used to refer 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.

Physical File Name/ OS File Name

Physical file name is the name of the physical file including the directory path. It belongs to OS  hence must follow the rules for the operating system file names.

Here is a simple practical to exactly mark both the types.

Create a database

When a SQL Server database is created, by default same physical name is granted to the file in folder as that of its logical name. Following T-SQL script would create a database with different and meaningful logical and physical filenames.


CREATE DATABASE [LogiPhysiFiles]
ON PRIMARY
( NAME = N’LogiName_data’,
FILENAME = N’F:DBs2k5PhysiName_data.mdf’)
LOG ON
( NAME = N’LogiName_log’,
FILENAME = N’F:DBs2k5PhysiName_log.ldf’)
GO

A database has been created with following file names

Logical data file name      = LogiName_data
Physical data file name     = F:DBs2k5PhysiName_data.mdf
Logical log file name        = LogiName_log
Physical data file name     = F:DBs2k5PhysiName_log.ldf

We may get information about files of this database by using Files option in properties of this database in SSMS

Logical and physical file names of SQL Server data and log files

For T-SQL use following script to get files information of this database

USE [LogiPhysiFiles]
GO

SELECT Name AS LogicalName, filename AS PhysicalFile
FROM sys.sysfiles
GO

Following result shows both types of file names

Get SQL Server files info through T-SQL

Or use system stored procedure

USE [LogiPhysiFiles]
GO

EXEC SP_HELPFILE
GO
Consult us to explore the Databases. Contact us