If a search brought you here, chance are that you can’t rename your database because SQL Server threw up an error about it being locked or active. This makes sense—obviously you can’t rename it if it’s being used by another user or application. But what if you want to rename it anyway?
- Take the database into single-user mode (i.e. you)
- Rename it
- Return the database back to multi-user mode
This script does just that 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
SQL Server 2005+ 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
If you want to rename the actual files on disk, too, this article does a fantastic job with that topic.
If you’re curious what
ROLLBACK IMMEDIATE does, this is a great explanation. In short, it rolls back other transactions if they are blocking the desired operation. Without this statement, the
ALTER DATABASE command may take as long as forever to run.
Unknown said on 2012-06-17
If an old name looks like:
C:\USERS\MBLOME\..\DATA\NORTHWND.MDF (which is the fact with MS) then don’t forget to surround the name with brackets