How to rename mdf filename to match Database name

Question

I am using SQL Server 2008 Developer Edition.

I originally created a database called Test1.  Later on, I renamed Test1 to AcmeStore.  However, I noticed that the corresponding .mdf and .ldf files retained the Test1 filename.  How can I rename the .mdf and .ldf filenames to AcmeStore?  (I quickly discovered that renaming them using Windows Explorer is NOT the way to do it!)  Any help would be greatly appreciated.

Thanks,

Bob

Answer 1

Hello Bob,

As long as the database files are in use, you can't rename the files (of course).

Detach the database first, then you can rename them and attach the database with the new file names/same database name.

See also: How to: Move a Database Using Detach and Attach (Transact-SQL), even if you don't really "move" the database, only renaming the files.

---

Olaf Helper ----------- * cogito ergo sum * errare humanum est * quote erat demonstrandum * Wenn ich denke, ist das ein Fehler und das beweise ich täglich http://olafhelper.over-blog.de

Answer 2

FWIW, as of 2005, it is recommended that we use ALTER DATABASE instead of detach/attach for these operations. I think that these might be some issue with Service Broker, for instance, if the old method is used. Here's hdo this using ALTER DATABASE: http://msdn.microsoft.com/en-us/library/ad9a4e92-13fb-457d-996a-66ffc2d55b79.aspx

---

Tibor Karaszi, SQL Server MVP | http://www.karaszi.com/sqlserver/default.asp | http://sqlblog.com/blogs/tibor_karaszi

Answer 3

Olaf Helper,

Thanks for your help.

Regards,

Bob

Answer 4

Tibor,

Thanks for your help, especially the recommendation to use ALTER DATABASE.

Regards,

Bob