none
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

    Monday, June 07, 2010 4:13 AM

Answers

  • 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
    • Marked as answer by emerald77 Monday, June 07, 2010 11:00 AM
    Monday, June 07, 2010 6:45 AM
  • 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
    • Marked as answer by emerald77 Monday, June 07, 2010 11:00 AM
    Monday, June 07, 2010 7:12 AM

All replies