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.
Thanks Timothy!
ReplyDeleteI was looking for this for a while. Excellent tip.
Glad to see it worked out for you. Much better than having names like "Joes_temporary_database"!
ReplyDeleteTHANK YOU!!!!!!!
ReplyDeleteVery Useful..... Exactly what I was looking for.....
ReplyDeleteTim,
ReplyDeleteDo 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'.
Jason,
ReplyDeleteYes, 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.
@Jason: If you have spaces in db name then you *must* escape it like this:
ReplyDeletealter database [my database] modify file (...)