Rename DB and OS Files

Question

Rename DB and OS Files

Sylvain Coutu

Ten Centuries

Points: 1077
More actions
July 22, 2004 at 8:19 am

#102118

Hi,
How would you run sp_renamedb and change the OS file name.
For now what I am do is I run sp_renamedb 'MyNewName' and then detach the DB and go to the OS and rename the file. Then I reattach the DB and specify the new name in the window.
Is there a less manual way to do that.
I tried looking at
ALTER DATABASE MyDB MODIFY FILE (NAME = LogicalName,
FILENAME ='File Path')
But from what I read in BOL
...FILENAME can be specified only for files in the tempdb database, and the new name does not take effect until Microsoft SQL Server is restarted...
Thanks

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 1

I would simply detach the database, rename the MDF, NDF and LDF files and reattach with new database name.

The alter database you mentioned is used for rename the database logical file name. It does't need to restart SQL Server.

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 2

I'd detach, then rename and attach as you mentioned. Not sure if there's an easier way. Don't forget this is a fairly big change. All connection string code needs to change that rferences the database name.

Sylvain Coutu Ten Centuries Points: 1077 More actions · Answer 3

Thanks for your replies.

For your information I need to rename db in test environement. So no need for references to be modified.

I rename DB with version and increment with each release. I keep a few version on the server. This way developpers have a way to "rollback" in time of previous release.

Our dev DB are pretty small so this is an easy task.

I was looking at way to do this Like Oracle does and let you ALTER the DATABASE and change file name.