15

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

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
Vikrant More
  • 5,182
  • 23
  • 58
  • 90
  • 2
    So people don't have to memorize what error numbers go with what error messages, next time can you please include the *text* of the error message, and the syntax you are using? I am sure many people assumed that `Msg 15225` was `The database could not be exclusively locked to perform the operation.` – Aaron Bertrand Jun 13 '12 at 12:23

3 Answers3

40

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;
Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
  • 2
    @VikrantMore you might be able to rename the database while it is in multi-user mode, but *only* if there aren't any users connected to it. The rename gets blocked by requiring single_user access, not strictly because of the mode it's in. You will get error Msg 5030 if you try to rename a database that is in use. – Aaron Bertrand Jun 13 '12 at 12:21
  • 6
    don't forget the `ALTER DATABASE bar SET MULTI_USER;` – Myster Aug 25 '13 at 22:46
  • I had trouble with the stated syntax but the following worked: EXEC sp_renamedb @dbname='foo', @newname='bar' – Mark Ainsworth Oct 07 '15 at 21:31
  • @Mark Can you be more explicit than "had trouble"? – Aaron Bertrand Oct 07 '15 at 21:56
7

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.

MartW
  • 12,348
  • 3
  • 44
  • 68
0

You can open sql server configuration manager and stop and start the service, this works for me with sql server 2008 and 2012 just fine.

Then rename the database right in the sql server management studio.

"Works for me"

Not really a "caveat" but say you want to restore the exact same database, you go into options and change the name of the mdf and ldf