Archive

Posts Tagged ‘Sql errors’

Renaming MS SQL Server database

February 8, 2010 2 comments

Renaming a database is not done very often. However, I have done it myself many times.  this can be done in two ways. The common method used for rename is by using ” Alter Database ” script. An example is as shown below.

ALTER DATABASE OldDbName MODIFY NAME = NewDbName

The above script will rename the database “OldDbName” with new name “NewDbName”. Another way of acheiving the same is by using the stored Procedure “SP_RENAMEDB” The syntax is as below

EXEC SP_RENAMEDB 'OldDbName' , 'NewDbName'

“Alter” is preferred over the SP_RenameDB as the SP will be deprecated in the future version of  SQL Server.

SQL Server might throw an error while renaming a database as below.

This is due to the existing conections to the database. to overcome this error,

1.you need to drop all the connections to the database.  Refer to my previous blog for more info on this.https://awesomesql.wordpress.com/2010/02/08/script-to-drop-all-connections-to-a-database/

2. You can set the database to single user mode . refer the link for more info on this. https://awesomesql.wordpress.com/2009/08/04/changing-the-collation-of-a-database/

Advertisements