Can I change database file name

Question

Can I change database file name

Bill Wagman

SSCommitted

Points: 1763
More actions
May 12, 2006 at 4:12 pm

#113017

Greetings, I have two databases with the same filename and don't know the ramifications of this. I recently was given this database and have found theat in Enterprise Manager when I select properties of a database and thenlook at the file name under the data tab it is not unique. Another database also has the same file name. The locations are unique however. These are user created databases. My question is twfold. First, how, if it all does SQL Server use the file name and what kind of problems, if any, can I expect as a result of this? Secondly, how can I change the file name without breaking things? Thanks.

Viewing 7 posts - 1 through 6 (of 6 total)

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

Mubeen Mohammed-203067 SSC Eights! Points: 877 More actions · Answer 1

Are you talking about Physical Names or Logical filenames. It will not be a problem having 2 databases with same logical file names.

Bill Wagman SSCommitted Points: 1763 More actions · Answer 2

Hello, I am talking about logical names. The physical file names are different and correct. Are there any potential problems having two files with the same logical file name? If one would like to change the logical file name is there a means whereby that can be done?

Thanks.

Mubeen Mohammed-203067 SSC Eights! Points: 877 More actions · Answer 3

Hi Bill,

I am giving you the detail information and I tested this before submitting in this site. Better you also test by creating test/temp database and follow the steps and once you get thru that.. you can apply to your original database and that to before taking full backup of your database.

--sp_helpfile

-- To change the Logical File Names

/*

Logical File names are those which Database internally uses.

You can see the Logical Names by using sp_helpfile

To change Logical Names, use the following commands from QA

*/

Use TestDB

go

exec sp_configure 'Allow updates',1

reconfigure with override

go

Update Sysfiles1

set name='ThisDataFile'

where fileid=1

GO

Update Sysfiles1

set name='ThisLogFile'

where fileid=2

go

exec sp_configure 'Allow updates',0

reconfigure with override

go

-- To change the Physical File Names

/*

You need to detach and attach the files.

First Detach the File and goto the Physical Location of the .mdf and .ldf files

Change the Physical Name to required Name

And then Attach the Database with new physical names.

To do so, here are the commands..

*/

EXEC sp_detach_db 'TestDB', 'true' -- Execute this from EM, if this doesn't executed

-- After Detach, please go to the Physical Location and Rename the .mdf and .ldf files

-- Then execute the following command with new Filenames which you changed.

EXEC sp_attach_db @dbname = N'TestDB',

@filename1 = N'C:\Program Files\Microsoft SQL Server\MSSQL\Data\ThisIsDataFile.mdf',

@filename2 = N'C:\Program Files\Microsoft SQL Server\MSSQL\Data\ThisIsLogFile.ldf'

Thanks-Mubeen

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

Bill,

I have used the following, and it does not require the "over-rides" mentioned above.

USE MASTER

GO

ALTER DATABASE DatabaseName

MODIFY FILE(NAME='Alpha_log', NEWNAME='Omega_log')

GO

This command changes the logical file name for the core data and/or log files of the database. Run it for logical file name you want to change.

Elliott

Bill Wagman SSCommitted Points: 1763 More actions · Answer 5

Thank you both. I will try this. I appreciate your assistance.

Ian Yates SSCoach Points: 19738 More actions · Answer 6

Good info. There won't be a problem having two DBs containing a file with same logical filename (although I think that within a DB itself you need unique logical filenames).

I would've thought the system tables would've needed direct updating - nice to see the standard ALTER DATABASE syntax supporting this. Thanks!!