SQL Server Tips: Logical names don’t match with physical files names

During a copy of a database from an environment to another (from Production to pre-production for instance), people usually don’t changed the logical name of the data or log files when they rename the database and the physical file names.

In my test environment, I create 2 copies of AdventureWorks and you can see that the logical name is the same for all three databases.

I can choose 2 ways to detect if logical names are good or not.
The first way is to compare the database name and the logical name.
The second choice is to compare the logical name and the physical name.
I prefer the second, simply to have the same name between the file and its logical representation.

To have just the database with the “bad logical name”, I create a where clause between the logical name and the physical name:

mf.Physical_Name NOT LIKE '%'+mf.name+'%'

To isolate the file name from the full path name, I use this query:

REVERSE(LEFT(REVERSE(mf.Physical_Name),CHARINDEX('\', REVERSE(mf.Physical_Name), 1) - 1))

But with this query, I have the extension file .mdf, .ldf, ….
To have the new logical name, I remove the extension file from my previous query. To do this, I use the length of the file name:

LEFT(REVERSE(LEFT(REVERSE(mf.Physical_Name),CHARINDEX('\', REVERSE(mf.Physical_Name), 1) - 1)),LEN(REVERSE(LEFT(REVERSE(mf.Physical_Name),CHARINDEX('\', REVERSE(mf.Physical_Name), 1) - 1))) - 4) as new__logical_name

Cool, I have the logical name that I wanted!
The next step is to create directly the change query.
To change the logical name from a database, I use the query:

ALTER DATABASE [database_name] MODIFY FILE (NAME=N'logical_name',NEWNAME=N'new_logical_name')

I have now the modified script. I copy these queries in a new query window and run it:

This operation is ONLINE and is fast.
Msdn reference: https://msdn.microsoft.com/en-us/library/bb522469.aspx

I restart my query to detect if the change is done:

Here is the query to copy:

SELECT db.name AS database_name, mf.name as actual_logical_name,

LEFT(REVERSE(LEFT(REVERSE(mf.Physical_Name),CHARINDEX('\', REVERSE(mf.Physical_Name), 1) - 1)),

LEN(REVERSE(LEFT(REVERSE(mf.Physical_Name),CHARINDEX('\', REVERSE(mf.Physical_Name), 1) - 1))) - 4) as new__logical_name,

'ALTER DATABASE ['+db.name +'] MODIFY FILE (NAME=N'''+ mf.name + ''',NEWNAME=N'''+

LEFT(REVERSE(LEFT(REVERSE(mf.Physical_Name),CHARINDEX('\', REVERSE(mf.Physical_Name), 1) - 1)),

LEN(REVERSE(LEFT(REVERSE(mf.Physical_Name),CHARINDEX('\', REVERSE(mf.Physical_Name), 1) - 1))) - 4) +''')' as change_script

FROM sys.master_files mf INNER JOIN sys.databases db ON db.database_id = mf.database_id

WHERE db.database_id>4 AND mf.Physical_Name NOT LIKE '%'+mf.name+'%'

It is not an optimized query and it is not a performance issue to rename logical names, but just a good practice.