Rename DB and OS Files

  • 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

  • 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.

  • 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.

  • 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.   

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

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