Changing File Name Of Database 3

[webuser]

I need to change the internal file name of a database. I moved the database from one server to another, and had to change the physical file name of the database. In other words, the database used to be called project_data.mdf, and is now program_data.mdf. Although everything works correctly, when I go to the database properties, under Data Files, the file name still says the old name, project_data.
This seems at best awkward and at worst could cause some problem down the road. Is there a way to change this?

Thanks in advance.

 

[MeanGreen]

3 steps:

1. sp_detachdb

2. rename file to program_data.mdf or whatever you want data file name to be. Also rename ldf (log) file to appropriate name.

3. sp_attachdb with new database name and new file names.

Hope this helps.

 

[webuser]

I appreciate your response. That is exactly what I did. While it works, and the database attaches correctly, what seems to be the 'internal' file name is still the old name. Like I said, if in EM I go to the Database properties, Data Files Tab, the 'File Name' listed, instead of being program_data still says project_data, even though the physical file name is program_data...

 

[jbatliner]

You could create a new database with the name that you want and simply import the old database into it.

 

[webuser]

Maybe it's me, but in the past when I've imported databases, I've often lost relationships, primary keys etc. and have had to recreate them. Also, we are sending this database to multiple vendors, and I thought the easiest way to do this was to detach/attach. I don't want other people to have to deal with creating new databases, importing data etc. Does this make sense?

 

[jbatliner]

It does make sense but unfrotunately I am pretty new to SQL server and do not have anymore sugestions.

Good luck!

 

[MeanGreen]

Ok.. Here we go:

sp_detach_db 'project'

At command prompt:

ren project_data.mdb program_data.mdb
ren project_log.ldb program_log.ldb

EXEC sp_attach_db @dbname = N'program',
@filename1 = N'c:\MSSQL\Data\program_data.mdf',
@filename2 = N'c:\MSSQL\Data\program_log.ldf'

When you are done you will have a "Program" database using Program data file.


This will make your data name agree with the DB Name.

Hope it helps!

 

[webuser]

I did exactly what you said, but it did not work... I actually did this before, only I used the attach_single_db. Anyway, I still have the same problem. The database name is correct, the physical file name is correct, but under 'File Name' in the 'Data Files' tab of Database properties, it still says the old name....

 

[MeanGreen]

Ok. Can you post the exact statements for each step.. and I will try to see what is changing? I took my pubs database, copied it, named it mypubs, and everything was perfect. Remember that when you run the sp_attach_db that the "C:\" or "D:\" is on the machine you are running the query analyzer against and not necessarily your local harddrive. So if you were renaming your "LOCAL" project_data.mdf to program_data.mdf, then ran your sp_attach_db for your server, then the files it would be attaching are on the "C:\" drive of the server and NOT your local machine.

Hope this helps.

Just a thought.

 

[tlbroadbent]

I believe you refer to the logical file name. This can be changed easily using the Alter Database statement.

Alter Database Program
MODIFY FILE (NAME = Project_Data, NEWNAME = Program_Data) Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[webuser]

Thanks! This is exactly what I needed. It worked like a charm! Thanks everyone for the help.

 

[JohnBates]

One of your best tips ever, Terry.
Thanks!