How to rename SQL Database !

  

Sometimes , we require to change or rename sql database ..

Here , given below commands for rename databases :

SQL Server 2005, 2008, 2008R2 and 2012 :

ALTER DATABASE oldName MODIFY NAME = newName

SQL Server 2000 :

EXEC sp_renamedb 'oldName', 'newName'

If there is chance of not able to renaming the database and it threwups

the error , it means it  locked or active .

Solution :

How it works:

1.Take the database into single-user mode (i.e. you)

2.Rename it

3.Return the database back to multi-user mode

For SQL Server 2000:

ALTER DATABASE orig_db_name SET SINGLE_USER WITH ROLLBACK

IMMEDIATE

EXEC sp_renamedb 'orig_db_name', 'new_db_name'

ALTER DATABASE new_db_name SET MULTI_USER

For SQL Server 2005 and above versions should use this slightly different version:

ALTER DATABASE orig_db_name SET SINGLE_USER WITH ROLLBACK

IMMEDIATE

ALTER DATABASE orig_db_name MODIFY NAME = new_db_name

ALTER DATABASE new_db_name SET MULTI_USER