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.


  1. Thanks Timothy!

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

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

  3. THANK YOU!!!!!!!

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

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

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

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

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


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.