locked
How to rename mdf filename to match Database name RRS feed

  • 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 7, 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 7, 2010 11:00 AM
    Monday, June 7, 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 7, 2010 11:00 AM
    Monday, June 7, 2010 7:12 AM

All replies

  • 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 7, 2010 11:00 AM
    Monday, June 7, 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 7, 2010 11:00 AM
    Monday, June 7, 2010 7:12 AM
  • Olaf Helper,

    Thanks for your help.

    Regards,

    Bob

    Monday, June 7, 2010 11:01 AM
  • Tibor,

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

    Regards,

    Bob

    Monday, June 7, 2010 11:02 AM