#0233 – SQL Server – Renaming database logical file names


Recently, one of my colleagues asked me an interesting question. They had a database with multiple files spread across various file-groups. The logical file names of these files were based on the product/company name. During a re-branding effort, they wanted to know if there was a way to change the logical file names and avoid transferring data over to a new database.

Here’s an example of the requirement. The script below creates a database with 3 files – a primary & secondary data file and a log file – all prefixed by the company name “AdventureWorks”.

/******************************************************************************
Create a test database
******************************************************************************/
USE master;
GO
IF EXISTS (SELECT * FROM master.sys.databases WHERE name = 'FileRenameTest')
BEGIN
    ALTER DATABASE FileRenameTest SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE FileRenameTest;
END;
GO

CREATE DATABASE [FileRenameTest]
    ON 
    PRIMARY(NAME = [AdventureWorks_Default], 
            FILENAME = 'C:Program FilesMicrosoft SQL ServerMSSQL11.SQL2K12MSSQLDATAFileRenameTest.mdf', 
            MAXSIZE = UNLIMITED, 
            FILEGROWTH = 51200 KB),
           (NAME = [AdventureWorks_Data], 
            FILENAME = 'C:Program FilesMicrosoft SQL ServerMSSQL11.SQL2K12MSSQLDATAFileRenameTest_Data1.mdf', 
            MAXSIZE = UNLIMITED, 
            FILEGROWTH = 51200 KB)
    LOG ON (NAME = [AdventureWorks_Log], 
            FILENAME = 'C:Program FilesMicrosoft SQL ServerMSSQL11.SQL2K12MSSQLDATAFileRenameTest.ldf', 
            MAXSIZE = UNLIMITED, 
            FILEGROWTH = 102400 KB);
GO

The requirement now is to rename the database logical files such that they begin with the new name – “Contosso”. To do so, we will be using the MODIFY FILE clause of the ALTER DATABASE statement:

/******************************************************************************
Rename logical files
******************************************************************************/
--Primary Data File
ALTER DATABASE FileRenameTest
MODIFY FILE (NAME = [AdventureWorks_Default], NEWNAME = [Contosso_Default]);
GO

--Secondary Data File
ALTER DATABASE FileRenameTest
MODIFY FILE (NAME = [AdventureWorks_Data], NEWNAME = [Contosso_Data]);
GO

--Log File
ALTER DATABASE FileRenameTest
MODIFY FILE (NAME = [AdventureWorks_Log], NEWNAME = [Contosso_Log]);
GO

Now, checking the file names using sys.database_files helps us to confirm that the logical file names have indeed changed.

/******************************************************************************
Check the logical file names
******************************************************************************/
USE FileRenameTest;
GO
SELECT file_id AS FileId,
       type AS FileType,
       name AS FileLogicalName,
       physical_name AS FilePhysicalName
FROM FileRenameTest.sys.database_files;
GO

image

References:

Until we meet next time,

Be courteous. Drive responsibly.

2 thoughts on “#0233 – SQL Server – Renaming database logical file names

  1. balakrishna141

    HI Nakul,

    Just for testing i have created one Database(Challenge) and i have Renamed to New Database name(Challenge_1), But the logical file name is not Renaming. So, after following your steps it has been Renamed

    Thanks for sharing such kind of scripts.

    Like

    Reply

Leave a Reply to Nakul Vachhrajani Cancel reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.