none
Messed up SQL database name

    Question

  • My database name on disk is all fouled up.

    Ok, what I did was backed up an existing working database.  Then I tried to restore it with a different name. I was basically trying to make a copy of the database and use it for some beta testing.  When I restored the database, I specified a new file location name. Unfortunately, the stupid thing restored it on top of the existing database name and simply renamed the file name on disk only.  It didn't give me a copy of the database with a new name.

    That is NOT what I wanted to happen.

    Now is has permanently taken the "new" name and no matter what I do, it won't go back to the old name.  It is very frustrating.  Even when I repeat the process of restoring the database with the original file name, it won't work.  It restores back the new name instead of the old name eventhough in options, it has the old name as the file location

    Can someone help, please.  This is a problem because the new file name is "beta" which is NOT good and is liable to get the SQL file deleted.


    Thanks



    • Edited by codeaholic Thursday, May 09, 2013 5:41 PM
    Thursday, May 09, 2013 5:27 PM

Answers

All replies

  • Hello,

    Please right click on the database and choose rename.


    Hope this helps.

    Regards,
    Alberto Morillo
    SQLCoffee.com

    Thursday, May 09, 2013 5:47 PM
    Moderator
  • The database filename is locked...it will not allow me to rename it.

    This is SQL 2003 I believe with Enterprise Mgr 8.  So if you are talking via Enterprise Manager - there is no rename.


    Thank you


    • Edited by codeaholic Thursday, May 09, 2013 9:11 PM
    Thursday, May 09, 2013 6:28 PM
  • Hello,

    Please use the following statement:

    EXEC sp_renamedb 'currentname', 'newname'

    For more information:

    http://msdn.microsoft.com/en-us/library/aa238879(v=sql.80).aspx


    Hope this helps.

    Regards,

    Alberto Morillo
    SQLCoffee.com

    Thursday, May 09, 2013 6:43 PM
    Moderator
  • The name of the database is "fine".  The issue is the name of the database file at the "file level".  It is no longer the same as the database name.

    For example,

    The database name is: gooddatabase

    the name of the file at MSSQL/data is Erase_me_data.mdf and eraseme_log.ldf for this database named gooddatabase



    • Edited by codeaholic Thursday, May 09, 2013 9:10 PM
    Thursday, May 09, 2013 8:16 PM
  • This will change the database name but not the database file name.


    • Edited by codeaholic Thursday, May 09, 2013 9:10 PM
    • Proposed as answer by ShoppingCart Friday, May 10, 2013 3:38 AM
    Thursday, May 09, 2013 8:27 PM
  • Ok, I fixed this by creating a new backup that apparently then realized the database file name change and did a new restore changing the name back.
    Friday, May 10, 2013 3:38 AM