Knowledge Base Article Number:
Q365270
Question by:
Adam Cogan
Answered by:
David Klein Last
Updated:
22/06/2007 6:54:01 PM
Spot an error? Please
comment about this article
Question:
How do I change the logical file name of my SQL Server Database? I originally named it 'TestData', but I want to change it now, so it is consistent with the database name and the physical file names. I can't do it through the GUI interface. Do I need to modify system tables?
Answer:
In SQL Server 2000 and above, this is simple. You don't need to modify system tables at all.
You can use the following:
To rename the logical file name of the pubs data file to pubs_data.
ALTER DATABASE pubs
MODIFY FILE (NAME = pubs, NEWNAME = pubs_data)
Now you don't have to live with that inappropriate logical file name on your server (e.g. TestData666.)
In SQL Server 7, this functionality is not supported. The logical file name is primarily a cosmetic thing - so it is not essential to change it. If you must, however, you can use
DTS to transfer the database to a new file set with the correct names. There are ways to bypass the system tables and rename it from there, but this is risky and is not recommended.
NOTE: The logical file name is not important because it only has to be unique per database, not per server, so you CAN have several databases on the one server all with the same logical file names.
Keywords:
Hot Tip - SQL Server 2000
New Search
SSW Custom Support