Change Logical File Name

Question

Change Logical File Name

Elliott Berkihiser

SSCarpal Tunnel

Points: 4068
More actions
May 25, 2005 at 2:06 pm

#102553

Hello,
I would like to be able to change the logical file names associated with a database. The programmers sometime use an existing database as a starting point to create a new one. It's easy to get the physical mdf and ldf names to match the new database name, but the logical name remains the same.
I'm thinking of trying to change the name of those files in the sysfiles table. This would require re-configuring SQL Server to "allow updates". Although I would change this option back immediately after performing the name change, I see that BOL characterizes this configuration as very risky.
Has anyone had experience in changing the logical file names? Thanks
??

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

ChrisMoix-87856 SSCertifiable Points: 7288 More actions · Answer 1

I think you can accomplish this through the ALTER DATABASE MODIFY FILE command. I DEFINITELY would not modify the system tables to accomplish this. From Books Online (under Alter Database):

MODIFY FILE

Specifies the given file that should be modified, including the FILENAME, SIZE, FILEGROWTH, and MAXSIZE options. Only one of these properties can be changed at a time. NAME must be specified in the <filespec> to identify the file to be modified. If SIZE is specified, the new size must be larger than the current file size. FILENAME can be specified only for files in the tempdb database, and the new name does not take effect until Microsoft SQL Server is restarted.

To modify the logical name of a data file or log file, specify in NAME the logical file name to be renamed, and specify for NEWNAME the new logical name for the file.

Thus:

MODIFY FILE (NAME = logical_file_name, NEWNAME = new_logical_name...).

For optimum performance during multiple modify-file operations, several ALTER DATABASE database MODIFY FILE statements can be run concurrently.

ChrisMoix-87856 SSCertifiable Points: 7288 More actions · Answer 2

One side note. What i described above is for SQL Server 2000. From what I understand, you cannot accomplish this in SQL Server 7. From another forum (I googled it):

" Note that this will not change the logical file names. That must be done separately and in SQL Server 7 it is not possible to change the logical file names at all.

--

HTH

----------------

Kalen Delaney

SQL Server MVP

www.SolidQualityLearning.com

"

Elliott Berkihiser SSCarpal Tunnel Points: 4068 More actions · Answer 3

The suggestion worked perfectly in SQL 2000. Thanks for your help.

Elliott

Munawar Ali-375714 SSC Enthusiast Points: 146 More actions · Answer 4

Thank You.

I wrote

ALTER DATABASE JANG MODIFY FILE (NAME = N'GPSJANGLOG.LDF',NEWNAME = N'PBCRM1')

I dont understand why Put N'OldFleName' and N'NewFileName'

Why N?

RichB SSCrazy Eights Points: 9651 More actions · Answer 5

N makes it unicode. Why? Well I don't think it matters with your example, but with some exciting unicode characters it makes all the difference!

Munawar Ali-375714 SSC Enthusiast Points: 146 More actions · Answer 6

Any Good Reason of Unicode Characters used after N'

Just for knowledge.

CDBA Hall of Fame Points: 3266 More actions · Answer 7

Alternately, go to EM and do the following:

1. Select the DB

2. Right click --> Go to DBProperties

3. Select the Data/File u want to rename/alter

4. Click on the Grid containg it's name and alter it

CDBA

Dhananjay.Desai SSC Journeyman Points: 83 More actions · Answer 8

Dhananjay.Desai

SSC Journeyman

Points: 83

May 12, 2008 at 9:29 am

#813914

Thanks, Alter Database worked perfectly.