#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.

Advertisements

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

Let me know what you think about this post by leaving your feedback here!

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 )

Google+ photo

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

Connecting to %s