How to rename database in multi-user mode

Question

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

Answer 1

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;