Renaming mdf and ldf-files - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Renaming mdf and ldf-files

Author

Topic

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2007-03-12 : 08:57:28

Hi all,

I used sp_renamedb to change the name of my database but I'd really like to change the name of the physical files as well. I have searched around quite a bit but I haven't found a way except for detatching and attaching the database but I would really like to avoid this if possible. I have also tried the following script without much luck:

ALTER DATABASE myDB
MODIFY FILE
(
    NAME = myDBtest,
    NEWNAME = myDB,
    FILENAME = N'L:\Instance_2\myDB.mdf'
)
GO

Msg 5037, Level 16, State 1, Line 1
MODIFY FILE failed. Do not specify physical name.

The current physical filename is "myDBtest.mdf". How can I change the filename??

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-03-12 : 09:13:38

you have to detach/attach as far as i know

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

Kristen
Test

22859 Posts

Posted - 2007-03-12 : 11:14:07

.. or do it as part of a RESTORE - which is how I normally do it. You can use MOVE at the same time to change the logical names.

Kristen

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2007-03-12 : 11:25:58

Allright...seems like it has to be a detatch/attach then. I find it odd that a simple file-rename can't be done "on the fly" like sp_renamedb or something but that's just the way it is I guess.

Thanx alot fellas!

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources