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

  • this command run succesfulyy thnaks to all member who provide 11

    Reply
  • 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.

    Reply
    • 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

      Reply
  • excellent website for sql

    Reply
  • Hello Pinal,

    Is there any logs so we can track DB name change

    Reply
  • 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?

    Reply
  • Poonam Choudhary
    October 5, 2016 7:14 am

    Hi Dave,

    I want to rename a database that is in mirroring. Kindly share the steps for that.

    Reply

Leave a Reply

Menu