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.

oob
  • 215
  • 2
  • 6

1 Answers1

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.

squillman
  • 37,883
  • 12
  • 92
  • 146