0

In Microsoft SQL Server Management Studio Express for Sql Server Express 2005, I needed to copy a database for testing and keep it on the same server as the old database. I did the following:

  1. Right Click on Databases

  2. Created new database

  3. Detached the database I wanted to copy

  4. "Restored" my new database from the backup file of my old database. I did this by clicking the 'Overwrite the existing database' box on the Options pane, and I changed the paths in the 'restore as' options so that they pointed to my new .mdf and .ldf files.

Everything is working like I want. Problem is, when I right-click -> Properties -> Files on my new database, the logical name of the .mdf file is the same as the logical name of the old .mdf file. They are actually different files - they just share the same logical name?

I guess maybe this isn't a short-term problem, but I can see it confusing somebody down the road. Any way to change the logical name of the .mdf file?

UPDATE EDIT - Apparently you can just change the logical name through the GUI by, get this, clicking on it and typing a new name. I could swear that was not possible when I posted this, but maybe it was and I somehow missed it! Either way - the solution below should still work but doing it through the GUI is also an option.

1 Answer 1

2

The logical filename is an identifier for SQL's internal purposes. It abstracts the actual physical filename so that you can relocate the physical file without having to change a bunch of things inside SQL.

To change the logical name of a file do this:

ALTER DATABASE <Database name>
MODIFY FILE  ( NAME = <current_logical_name>, NEWNAME = <new_logical_name>)

Have a look at this article for more on info on consolidating and renaming files.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .