SQL SERVER – 2005 – Use ALTER DATABASE MODIFY NAME Instead of sp_renameDB to rename

To rename database it is very common to use for SQL Server 2000 user :
EXEC sp_renameDB 'oldDB','newDB'
sp_renameDB syntax will be deprecated in the future version of SQL Server. It is supported in SQL Server 2005 for backwards compatibility only. It is recommended to use ALTER DATABASE MODIFY NAME instead. New syntax of ALTER DATABASE MODIFY NAME is simple as well.

/* Create Test Database */
CREATE DATABASE Test
GO
/* Rename the Database Test to NewTest */
ALTER DATABASE Test MODIFY NAME = NewTest
GO
/* Cleanup NewTest Database
Do not run following command if you want to use the database.
It is dropped here for sample database clean up. */
DROP DATABASE NewTest
GO

Reference : Pinal Dave (https://blog.sqlauthority.com), BOL

,
Previous Post
SQL SERVER – Validate Field For DATE datatype using function ISDATE()
Next Post
SQL SERVER – 2005 Collation Explanation and Translation – Part 2

Related Posts

30 Comments. Leave new

  • Yes this will drop it.

    This is sample example. Not the real time scenario. In real world, you do not need to drop the new database.

    Regards,
    Pinal Dave http://blog.SQLAuthority.com)

    Reply
  • Chris L,

    I have updated my post based on your question.
    Thanks for suggestion.

    Regards,
    Pinal Dave (SQLAuthority.com)

    Reply
  • So, if you rename the old database with the new name and call drop on the test database, won’t that drop the old DB with the new name?

    Reply
  • most of the time when i need any help i got here everything
    and it is not good for me besides it is beneficial for all the DBA community.
    you did great job………

    Reply
  • how to add contraint in alter query

    Reply
  • I renamed some databases since we need to standardize the naming convention. Unfortunately, it broke the SSIS package that used to be working. Somehow, it does not want to save the password even going directly to the configuration file. Any suggestion?

    Reply
  • How about the physical name? How do you rename it?

    Reply
  • when i am try to rename database it shows me following error msg
    Msg 5030, Level 16, State 2, Line 1
    The database could not be exclusively locked to perform the operation.

    how do i resolve this problem?

    Reply
  • Hi Sir

    I was able to rename the database..but the logical name of the database didnot get renamed..is there any query to rename the database’s logical name as well

    Reply
  • Thank you so much Rich !!!! your seven stpes help me a great deal…

    Reply
  • I am getting the same error as Eric:
    Msg 5030, Level 16, State 2, Line 1
    The database could not be exclusively locked to perform the operation.

    How was this resolved?
    Thanks in advance!

    Reply
  • Imran Mohammed
    March 1, 2009 10:08 am

    @Wendy,

    If you want to change database name, then no user should be connected to database.

    First keep the database in single user mode and then change the database name or disconnect all users from that database and then you should be able to rename database.

    Regards
    IM.

    Reply
  • I have problem ALTER DATABASE
    ALTER Database PersonnelMS SET Partner=’TCP://192.168.0.4:5022′

    It shows message
    Msg 1486, Level 14, State 2, Line 2
    Database Mirroring Transport is disabled in the endpoint configuration.

    Reply
  • Thanks, Done

    Reply
  • Thanks for info, its working fine for me.

    Reply
  • Neither method works for me. I’ve dug though loads of documentation trying to find the answer and nothing works. MS products are garbage.

    Reply
  • Server: Msg 5030, Level 16, State 2, Line 1
    The database could not be exclusively locked to perform the operation.

    This Means?

    Reply
  • Hi Saurabh,

    First alter the database to single_user mode. This way all the connection would be broken and database would be available to be exclusively locked. Then again alter the database to multi_user mode and perform your operation.

    Regards,
    Pinal Dave

    Reply
  • Hi Dave,

    I have a situation where the db name is wrongly cased.
    Say, “MyDataWarehouse” and would like to change to “MyDatawarehouse”. How can we do it?

    SQL 2005. I tried renaming it in SQL Mgmt Studio and it did not work.

    The other option is Either
    – Take the db offline, drop the db and recreate the db and attach the files.
    – Take complete backup, drop the db, recreate the db and restore it…

    Cheers,

    Sreedhar

    Reply
    • Hi Sreedhar,

      You can change the case with Alter Database Command as specified in start of the forum.

      Ex.
      ALTER DATABASE MyDataWarehouse MODIFY NAME = MyDatawarehouse

      Regards,
      Siddhesh

      Reply
  • Dear Pinaldave,

    1) Is anything like database alias exists there in SQL Server 2005? i.e. a feature similar to table alias.

    2) Is there any way of overcoming the nvarchar(max) size which is 4000 chars. So that a longer, infact a very long, sql string can be passed to SP_ExecuteSQL procedure?

    Actually, I need to run my crystal reports’ SP’s on different databases (Currrent and Historic/Archive DB) based on the reporting date passed as prameter to SP.

    I need your expert opnion on this and if possible a Way to acheive this.

    Regards,

    Imran

    PS: Table names in the both the current and history databases are the same i.e. same schema.

    Reply

Leave a Reply

Menu