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
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
Or use system stored procedure
USE [LogiPhysiFiles] GO EXEC SP_HELPFILE GO