Rename The Logical Name Of SQL Server Database Files Using T-SQL

Rename the logical name of SQL Server database files using T-SQL:

1. Problem:

SQL Server database files have two names: one is the logical file name and the other is the physical file name. The logical file name is used with some T-SQL commands, such as DBCC SHRINKFILE. If you want to change the logical file names of your database files then this tip will be very useful. This way you can keep your logical database file names unique on your SQL Server instance.

Since you may want to move this database on a production server, or change the database name because of any reason, you may also want to change the names of the database files. You may want the database names consistent with the database names or ant other rules you obey for standardization.

2. Solution:

It is not mandatory to keep logical file names unique on a SQL Server instance, but ideally we should keep them unique to avoid any confusion.

When we restore a database, the logical database file names don't change and are the same as the source database. So if you are restoring to multiple databases with the same backup file, the logical file names will be the same for all of the restored databases.

Let's start with creating a database having three data files and a log file then we will change the logical file name of these database files to show the step by step method.

Rename the logical SQL Server database file name using T-SQL

Step 1: First we will create a database named "Test_DB" with 1 data files and 5 log files and one log file by running the below T-SQL code. You can change the name of the database, file path, file names, size and file growth according to your needs.

USE MASTER;

GO

CREATE DATABASE Test_DB

ON

( NAME = Test_DB,

FILENAME = 'E:\Data\Test_DB.mdf',

SIZE = 204800KB,

MAXSIZE = UNLIMITED,

FILEGROWTH = 102400KB )

LOG ON

( NAME = Test_DB_log1,

FILENAME = 'E:\Data\Test_DB_log1.ldf',

SIZE = 204800KB,

MAXSIZE = UNLIMITED,

FILEGROWTH = 102400KB ),

( NAME = Test_DB_log2,

FILENAME = 'E:\Data\Test_DB_log2.ldf',

SIZE = 204800KB,

MAXSIZE = UNLIMITED,

FILEGROWTH = 102400KB ),

( NAME = Test_DB_log3,

FILENAME = 'E:\Data\Test_DB_log3.ldf',

SIZE = 204800KB,

MAXSIZE = UNLIMITED,

FILEGROWTH = 102400KB ),

( NAME = Test_DB_log4,

FILENAME = 'E:\Data\Test_DB_log4.ldf',

SIZE = 204800KB,

MAXSIZE = UNLIMITED,

FILEGROWTH = 102400KB ),

( NAME = Test_DB_log5,

FILENAME = 'E:\Data\Test_DB_log5.ldf',

SIZE = 204800KB,

MAXSIZE = UNLIMITED,

FILEGROWTH = 102400KB ) ;

GO

Step 2: Next I created a backup of the database.

BACKUP DATABASE Test_DB TO DISK = 'D:\Data\Backup\Test_DB.bak'

GO

I ran the below T-SQL command to get the logical file names of database file for the database TEST_DB.

USE Test_DB

GO

SELECT file_id, name as [logical_file_name],physical_name

from sys.database_files

Step 3: Now we will change the logical file name for the log data files for database "Test_DB" from "Test_DB_log" to "Test_DB_New2_log.ldf" by running an ALTER DATABASE statement using MODIFY FILE according to their file_id. To modify the logical name of a data file or log file, we will specify the logical file name to be renamed in the NAME clause, and specify the new logical name for the file in the NEWNAME clause. Run the below command to create a procedure apply this change.

CREATE PROCEDURE Rename_Logical_Name

(

@Database sysname, @New_name sysname

)

AS

BEGIN

DECLARE @cmd1 nvarchar(200)

DECLARE @cmd2 nvarchar(200)

DECLARE @ldffile sysname

DECLARE @id sysname

DECLARE theCursor CURSOR

FOR (SELECT name,FILE_ID FROM sys.database_files WHERE type = 1)

OPEN theCursor

FETCH NEXT FROM theCursor INTO @ldffile,@id

WHILE @@FETCH_STATUS = 0

BEGIN

SET @cmd2 = 'ALTER DATABASE ' +@Database+' MODIFY FILE (NAME= '''+ @ldffile + ''', NEWNAME = '''+@ New_name +@ID+'_log.ldf'')'

EXEC (@cmd2)

FETCH NEXT FROM theCursor INTO @ldffile,@id

END

CLOSE theCursor

DEALLOCATE theCursor

--PRINT @cmd1

--PRINT @cmd2

--EXEC (@cmd1)

EXEC (@cmd2);

END

EXEC Rename_Logical_Name @Database = 'Test_DB',@New_name = 'Test_DB_New'

Step 4: Now we will check the logical file names to verify our change we made in the last step. Run the command below to check the logical names.

SELECT file_id, name as [logical_file_name],physical_name

from sys.database_files WHERE type = 1

We can see the logical file names has been changed without any downtime of the database.

NOTE: Do not change logical names of any data file of your production database until it is required or needed.