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 (http://blog.SQLAuthority.com), BOL

About these ads

29 thoughts on “SQL SERVER – 2005 – Use ALTER DATABASE MODIFY NAME Instead of sp_renameDB to rename

  1. 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?

  2. 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………

  3. 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?

  4. 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?

  5. 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

  6. 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!

  7. @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.

  8. 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.

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

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

    This Means?

  11. 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

  12. 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

    • 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

  13. 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.

  14. when i am renaming the database
    using this query.

    ALTER DATABASE KYRPLUS
    Modify Name = KYRPLUS2june ;

    the error occurred:-

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

    • Hi shilpa.

      As Pinal Dave wrote in some reply on January 1, 2010 at 5:43 pm, the steps should be:

      use master
      go
      /*
      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.
      */
      ALTER DATABASE KYRPLUS SET SINGLE_USER WITH ROLLBACK IMMEDIATE
      GO
      /*
      Then again alter the database to multi_user mode and perform your operation.
      */
      ALTER DATABASE KYRPLUS SET MULTI_USER WITH ROLLBACK IMMEDIATE
      GO
      /*
      perform your operations. In this case, modify the name of the database
      from: KYRPLUS
      to: KYRPLUS2june
      */
      ALTER DATABASE KYRPLUS
      MODIFY name = KYRPLUS2june
      GO

  15. Hi Pinal,
    1) if MODEL and MSDB databases are in suspect mode will my SQL server be alive/comeup?
    2) if i have a table of 1TB, and fragmentation is 85%,apart from rebuild index,what other options do we have?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s