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

16 years ago

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

SQL Scripts, SQL Stored Procedure

SQL SERVER – Validate Field For DATE datatype using function ISDATE()

SQL SERVER – 2005 Collation Explanation and Translation – Part 2

30 Comments. Leave new

Taleem
August 19, 2011 9:52 pm
this command run succesfulyy thnaks to all member who provide 11
Reply
shilpa
June 2, 2012 6:44 pm
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
- Manuel
  September 20, 2012 8:26 pm
  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
robin
March 4, 2013 10:54 am
excellent website for sql
Reply
Moin
May 8, 2013 5:01 pm
Hello Pinal,
Is there any logs so we can track DB name change
Reply
rudraksh
March 11, 2014 4:09 pm
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