Introduction - Hi everyone in this post I will explain the difference between the Database name, Logicla name and Physical name of a database and steps to find it.
Description -
The Database name refers to the name of the database that we can see in the Object explorer window of the SQL Server Management Studio.
The Logical name refers to the physical file or database name by SQL Server in all Transact-SQL statements, Logical Name will be used internally by the SQL Server.
The Physical name refers to the physical file name of the database which we can see in the folder structure of windows explorer.
Steps to find the database name, physical name and logical name of a database
Database Name - The database name is the name we see in the Object Explorer window of SQL Server Management studio.
Logical Name -
Right Click on the database and click on properties
In the Database properties window that opens, click on Files page/tab in the left pane
Now in the Right pane the fist row value under the heading "Logical Name" is the Logical Name of the database
The second row value is the Log File of the database.
Physical Name -
In the above opened Files page/tab
The first row value under the heading "File Name" gives the Physical name.
Note -
For a newly created database the Database name, Physical Name and Logical Name will be the same.
If the database name has been changed any time then the Physical name and Logical name will be different.
The three might also be different when back up restoration work is done.
We can write queries in the query editor of Management studio only using Database name and not using Logical name/Physical name
ie Consider a table with name TEST_TABLE under the database with database name as TEST_DB
and the Logical name as TEST_DB_LOGNAME and physical name as TEST_DB_PHYSNAME
Hence we cannot use the logical name or physical name in SQL queries like
Use TEST_DB_LOGNAME
SELECT * FROM TEST_TABLE
Use TEST_DB_PHYSNAME
SELECT * FROM TEST_TABLE
Instead we have to use database name like
USE TEST_DB
SELECT * FROM TEST_TABLE
Description -
The Database name refers to the name of the database that we can see in the Object explorer window of the SQL Server Management Studio.
The Logical name refers to the physical file or database name by SQL Server in all Transact-SQL statements, Logical Name will be used internally by the SQL Server.
The Physical name refers to the physical file name of the database which we can see in the folder structure of windows explorer.
Steps to find the database name, physical name and logical name of a database
Database Name - The database name is the name we see in the Object Explorer window of SQL Server Management studio.
Logical Name -
Right Click on the database and click on properties
In the Database properties window that opens, click on Files page/tab in the left pane
Now in the Right pane the fist row value under the heading "Logical Name" is the Logical Name of the database
The second row value is the Log File of the database.
Physical Name -
In the above opened Files page/tab
The first row value under the heading "File Name" gives the Physical name.
Note -
For a newly created database the Database name, Physical Name and Logical Name will be the same.
If the database name has been changed any time then the Physical name and Logical name will be different.
The three might also be different when back up restoration work is done.
We can write queries in the query editor of Management studio only using Database name and not using Logical name/Physical name
ie Consider a table with name TEST_TABLE under the database with database name as TEST_DB
and the Logical name as TEST_DB_LOGNAME and physical name as TEST_DB_PHYSNAME
Hence we cannot use the logical name or physical name in SQL queries like
Use TEST_DB_LOGNAME
SELECT * FROM TEST_TABLE
Use TEST_DB_PHYSNAME
SELECT * FROM TEST_TABLE
Instead we have to use database name like
USE TEST_DB
SELECT * FROM TEST_TABLE
Further Reading:
Best Practice for renaming a SQL Server Database
https://www.mssqltips.com/sqlservertip/1891/best-practice-for-renaming-a-sql-server-database/
Best Practice for renaming a SQL Server Database
https://www.mssqltips.com/sqlservertip/1891/best-practice-for-renaming-a-sql-server-database/
