Introduction

A SQL Server database, as most probably already know, is made up of a data file(s) and a log file. The file in a database is broken down by the physical and logical name. This article is going to look at the logical name in a database and how (if needed) you would change it using T-SQL or PowerShell. 

Logcical Name

This is a property of a database file that you are really only going to see used to perform administration against the files of a database. In some commands you can actually use the file ID or the logical name. Using the logical name can just make the script more easily to read, and understand what file your are touching. In the sections that follow I am just going to go over how you find the value and how to modify it.

The below is a small list of common places it is used:

1. Restoring a database where you are moving the physical file to a new location.
2. Certain DBCC SHRINKFILE command (yes there are cirumstances this has to be done).
3. Expanding the size of a file for a database.

Using T-SQL

There are two catalog views that can be used to determine what the logical name is for each file in your database: sys.master_files and sys.database_files. Regarding finding the file logical name you will simply pull the name column:

/* return the logical name for all databases */SELECT DB_NAME(database_id) AS database_name, file_id, name AS logical_name
FROM sys.master_files;
/* return the logical name for the current database context */USE [NORTHWND];
GO
SELECT file_id, name AS logical_name
FROM sys.database_files

As the log file of a database will always have a file_id of 2 you could use this metadata function to return the logical name: FILE_NAME(). The catch with using this function is it is works in the context of the current database. So this would only return the logical file name of the NORTHWND database:

USE [NORTHWND];
GO
SELECT DB_NAME(database_id) AS database_name, file_id, FILE_NAME(file_id) AS logical_name
FROM sys.master_files

An example of where you might actually use this function could be querying the virtual file stats for a database, such as this:

USE [NORTHWND];
GO
SELECT DB_NAME(database_id) AS database_name, FILE_NAME(file_id) AS logical_name, io_stall_reads_ms, io_stall_write_ms, io_stall
FROM sys.dm_io_virtual_file_stats(DB_ID(), NULL)

Now that you know where to find it, how about changing it. This is done using the ALTER DATABASE...MODIFY FILE command such as this:

USE [master];
GO
ALTER DATABASE [NORTWND] MODIFY FILE ( NAME = NorthwindBadName, NEWNAME = Northwind_2 );
GO

Change it with PowerShell

If you are not very familiar with using PowerShell, you can read a previous article I did on the basics, or the Stairway to PowerShell series by Ben Miller. I am just going to show a short illustration using both SQL Server PowerShell Provider (SQLPS) and the SQL Server Management Objects (SMO). I will provide comments in the code for you to follow along, you can just paste this into the PowerShell ISE if you want to play along.

Using SQLPS Provider

<# I have multiple versions of SQLPS on my machine and only want to work with SQL Server 2012 #>
Get-Module SQLPS | where {$_.Path -match "110"} | Import-Module
### The above command will import the provider and SMO assembly ###
<# Finding the data file logical name #>
$dataFiles = dir SQLSERVER:\SQL\MANATARMS\SQL12\Databases\NORTHWND\FileGroups
$dataFiles.Files | select name, id
<# rename NorthwindBadName to Northwind2 #>
$dataFiles.Files["NorthwindBadName"].Rename("Northwind2")
<# Finding the log file logical name #>
$logFile = dir SQLSERVER:\SQL\MANATARMS\SQL12\Databases\NORTHWND\LogFiles
<# rename Northwind_log to NorthwindLog #>
$logFile.Rename("NorthwindLog")

Using SMO

<# I have multiple versions of SQLPS on my machine and only want to work with SQL Server 2012 #>
Get-Module SQLPS | where {$_.Path -match "110"} | Import-Module
### The above command will import the provider and SMO assembly ###
$server = 'MANATARMS\SQL12'
<# Create an object of our SQL Server instance #>
$s = New-Object Microsoft.SqlServer.Management.Smo.Server $server
<# populate a variable with our database #>
$db = $s.Databases["NORTHWND"]
<# Find the data file logical name #>
$db.FileGroups.Files | select name, id
<# rename the data file #>
$dataFile = $db.FileGroups.Files | where {$_.name -eq "NorthwindBadName"}
$dataFile.Rename("Northwind2")
<# Find the log file logical name #>
$db.LogFiles | select name, id
<# rename the log file #>
$logFile = $db.LogFiles | where {$_.name -eq "Northwind_log"}
$logFile.Rename("NorthwindLog")