Friday, February 16, 2007

SQL Server: How to change a database file name

Note: this is how to change the logical file name of a database file. To change the physical location of a database file, see How to Move a Database File.

I've had a couple of databases hanging around that have very bad logical file names. They were supposed to be test databases, but grew to be production databases (there is a law about this: there is no such thing as a prototype...)

I found a simple piece of code for how to change the names of these logical files:


alter database MyDatabase modify file (name='test', newname='MyDatabase')
alter database MyDatabase modify file (name='test_log', newname='MyDatabase_log')

This allows me to follow my naming convention and neot have to explain to the next guy why my database has logical file names that are animal names.

7 comments:

  1. Thanks Timothy!

    I was looking for this for a while. Excellent tip.

    ReplyDelete
  2. Glad to see it worked out for you. Much better than having names like "Joes_temporary_database"!

    ReplyDelete
  3. THANK YOU!!!!!!!

    ReplyDelete
  4. Very Useful..... Exactly what I was looking for.....

    ReplyDelete
  5. Tim,

    Do I run this in SQL Query Analyzer?

    I received this error message

    Server: Msg 156, Level 15, State 1, Line 1
    Incorrect syntax near the keyword 'file'.

    ReplyDelete
  6. Jason,
    Yes, run it in Query Analyzer. Try running the commands one at a time. If that doesn't work, email me at TimothyChenAllen@gmail.com. Thanks.

    ReplyDelete
  7. @Jason: If you have spaces in db name then you *must* escape it like this:

    alter database [my database] modify file (...)

    ReplyDelete

I moderate comments blog posts over 14 days old. This keeps a lot of spam away. I generally am all right about moderating. Thanks for understanding.