在 SQL Server 2008 R2 中重命名数据库时出错
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16685269/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Error on renaming database in SQL Server 2008 R2
提问by Vikram Bose
I am using this query to rename the database:
我正在使用此查询重命名数据库：
ALTER DATABASE BOSEVIKRAM MODIFY NAME = [BOSEVIKRAM_Deleted]
But it shows an error when excuting:
但是执行的时候报错：
Msg 5030, Level 16, State 2, Line 1
The database could not be exclusively locked to perform the operation.
消息 5030，级别 16，状态 2，第 1 行
无法以独占方式锁定数据库以执行操作。
Is anything wrong with my query?
我的查询有什么问题吗？
回答by Squid
You could try setting the database to single user mode.
您可以尝试将数据库设置为单用户模式。
https://stackoverflow.com/a/11624/2408095
https://stackoverflow.com/a/11624/2408095
use master
ALTER DATABASE BOSEVIKRAM SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE BOSEVIKRAM MODIFY NAME = [BOSEVIKRAM_Deleted]
ALTER DATABASE BOSEVIKRAM_Deleted SET MULTI_USER
回答by Samiey Mehdi
Set the database to single mode:
ALTER DATABASE dbName SET SINGLE_USER WITH ROLLBACK IMMEDIATETry to rename the database:
ALTER DATABASE dbName MODIFY NAME = NewNameSet the database to Multiuser mode:
ALTER DATABASE NewName SET MULTI_USER WITH ROLLBACK IMMEDIATE
将数据库设置为单模式：
ALTER DATABASE dbName SET SINGLE_USER WITH ROLLBACK IMMEDIATE尝试重命名数据库：
ALTER DATABASE dbName MODIFY NAME = NewName将数据库设置为多用户模式：
ALTER DATABASE NewName SET MULTI_USER WITH ROLLBACK IMMEDIATE
回答by Justin Woodmancy
In SQL Server Management Studio (SSMS):
在SQL Server Management Studio (SSMS) 中：
You can also right click your database in the Object Explorerand go to Properties. From there, go to Options. Scroll all the way down and set Restrict Accessto SINGLE_USER. Change your database name, then go back in and set it back to MULTI_USER.
您也可以在对象资源管理器中右键单击您的数据库并转到Properties。从那里，转到Options。一直向下滚动并将限制访问设置为SINGLE_USER。更改您的数据库名称，然后返回并将其设置回MULTI_USER。
回答by Andrey Gordeev
回答by t_plusplus
This did it for me:
这为我做到了：
USE [master];
GO
ALTER DATABASE [OldDataBaseName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
EXEC sp_renamedb N'OldDataBaseName', N'NewDataBaseName';
-- Add users again
ALTER DATABASE [NewDataBaseName] SET MULTI_USER
GO
回答by Dhwani
That's because someone else is accessing the database. Put the database into single user mode then rename it.
那是因为其他人正在访问数据库。将数据库置于单用户模式，然后重命名。
This link might help:
http://msdn.microsoft.com/en-IN/library/ms345378(v=sql.105).aspx
此链接可能会有所帮助：http:
//msdn.microsoft.com/en-IN/library/ms345378(v=sql.105).aspx
and also:
http://msdn.microsoft.com/en-us/library/ms345378.aspx
回答by slayernoah
Change database to single user mode as shown in the other answers
如其他答案所示，将数据库更改为单用户模式
Sometimes, even after converting to single user mode, the only connection allowed to the database may be in use.
有时，即使在转换为单用户模式后，唯一允许连接到数据库的连接可能仍在使用中。
To close a connection even after converting to single user mode try:
要在转换为单用户模式后关闭连接，请尝试：
select * from master.sys.sysprocesses
where spid>50 -- don't want system sessions
and dbid = DB_ID('BOSEVIKRAM')
Look at the results and see the ID of the connection to the database in question.
查看结果并查看与相关数据库的连接的 ID。
Then use the command below to close this connection (there should only be one since the database is now in single user mode)
然后使用下面的命令关闭这个连接（应该只有一个，因为数据库现在处于单用户模式）
KILL connection_ID
杀死连接_ID
Replace connection_id with the ID in the results of the 1st query
将 connection_id 替换为第一次查询结果中的 ID
回答by K GANGA
1.database set 1st single user mode
1.数据库设置第一个单用户模式
ALTER DATABASEBOSEVIKRAM SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASEBOSEVIKRAM SETSINGLE_USER立即回滚
2.RENAME THE DATABASE
2.重命名数据库
ALTER DATABASEBOSEVIKRAM MODIFY NAME =[BOSEVIKRAM_Deleted]
ALTER DATABASEBOSEVIKRAM MODIFY NAME =[BOSEVIKRAM_Deleted]
3.DATABAE SET MULIUSER MODE
3.DATABAE设置多用户模式
ALTER DATABASEBOSEVIKRAM_Deleted SET MULTI_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASEBOSEVIKRAM_Deleted SET MULTI_USER WITH ROLLBACK IMMEDIATE
回答by Chronozoa
Another way to close all connections:
关闭所有连接的另一种方法：
Administrative Tools > View Local Services
管理工具 > 查看本地服务
Stop/Start the "SQL Server (MSSQLSERVER)" service
停止/启动“SQL Server (MSSQLSERVER)”服务
回答by KamalDeep
use master
ALTER DATABASE BOSEVIKRAM SET SINGLE_USER WITH ROLLBACK IMMEDIATE
exec sp_renamedb 'BOSEVIKRAM','BOSEVIKRAM_Deleted'
ALTER DATABASE BOSEVIKRAM_Deleted SET MULTI_USER
