Q:

How to rename database in multi-user mode

I am working on SQL SERVER 2008 & 2008 R2.
How can I rename a database in multi-user mode?
I am using sp_rename but it returns this error:

Msg 15225, Level 11, State 1, Procedure sp_rename, Line 338

A:

You can't rename a database while it is in use. Either wait for a maintenance window, or force the database to single user mode (which will kick everyone out):
USE [master];
GO
ALTER DATABASE foo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
--EXEC sys.sp_renamedb @dbname = N'foo', @newname = N'bar';
ALTER DATABASE foo MODIFY NAME = bar; -- preferred way
GO
ALTER DATABASE bar SET MULTI_USER;

A:

You can't use sp_rename to rename a database - the sp in question would be sp_renamedb. However, that is in line to be removed in a future version of SQL Server and the preferred method is:
ALTER DATABASE dbname MODIFY NAME = newdbname;

But you can't do this anyway without an exclusive lock on the database.

