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
References:
- ALTER DATABASE, File & Filegroup options: http://msdn.microsoft.com/en-us/library/bb522469.aspx
- sys.database_files: http://msdn.microsoft.com/en-us/library/ms174397.aspx
Until we meet next time,
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.
LikeLike
@Bala Krishna: Glad that you liked it! Keep the feedback coming – really appreciate it.
LikeLike