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

Feed on: Posts; Comments

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

July 13, 2007 by pinaldave

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

Posted in Pinal Dave, SQL, SQL Authority, SQL Documentation, SQL Download, SQL Error Messages, SQL Joins, SQL Performance, SQL Query, SQL Scripts, SQL Security, SQL Server, SQL Server DBCC, SQL Tips and Tricks, T SQL, Technology | 10 Comments

10 Responses to “SQL SERVER - 2005 - Use ALTER DATABASE MODIFY NAME Instead of sp_renameDB to rename”

on July 13, 2007 at 8:26 am Chris L

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?
on July 13, 2007 at 8:32 am pinaldave

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://www.SQLAuthority.com)
on July 13, 2007 at 8:34 am pinaldave

Chris L,

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

Regards,
Pinal Dave (SQLAuthority.com)
on July 18, 2007 at 1:23 pm Anjum

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………
on January 22, 2008 at 11:48 am ramu

how to add contraint in alter query
on March 21, 2008 at 7:33 am mike

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?
on May 16, 2008 at 8:18 am Pedro

How about the physical name? How do you rename it?
on August 1, 2008 at 9:14 pm Eric

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?
on August 26, 2008 at 12:15 pm Guru

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
on October 24, 2008 at 11:30 am Shabana

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