在任何情况下，如何使用代码成功分离数据库? [英] How to detach database successfully in any case using code?

问题描述

我知道以下代码可用于分离Sql服务器数据库:

I know the following code can be used to detach a Sql server database:

SqlCommand com = new SqlCommand("USE master; exec sp_detach_db mydatabase", con);
com.ExecuteNonQuery();

但是它并不总是有效，仅在某些情况下当前正在使用数据库，而又不知道为什么以及使用什么进程.如果使用Sql Server管理器，则可以刷新所有数据库并可以成功执行分离存储的proc.但是我想使用代码分离数据库(无论何时需要)，请给我任何解决方案吗?

我发现我的数据库确实在使用中，因为我曾使用sql连接从中读取数据，但是在分离前我尝试销毁所有sql内容(sql连接，sql dataadapter ...)，但它仍会引发异常.您是否对如何使数据库不再使用有任何想法?
您的帮助将不胜感激！
非常感谢！

But it doesn''t always work, just for some cases the database is currently in use without knowing why and what process is using it. If using Sql Server manager, I can refresh all the databases and can execute the detach stored proc successfully. But I want to detach the database (whenever I want) using code, could you please give me any solution?

I discovered that my database was really in use, because I had used sql connection to read data from it, however I tried diposing all sql stuffs (sql connection, sql dataadapter...) before detaching but it still throws the exception. Could you have any ideas on how to make the database not currently be in use any more?
Your helps would be highly appreciated!
Thank you very much!

推荐答案

您好，


单击此链接"以编程方式枚举，附加和分离SQL Server数据库 [ ^ ]

Hello,


Follow this link "Programmatically Enumerating, Attaching, and Detaching SQL Server Databases[^]

感谢您的良好链接.
在这里发布我的问题之前，我已经阅读了它.此外，这是在当前不使用数据库时如何使用代码分离和附加数据库的指南.我的情况是另一个问题，我们必须首先使数据库不被使用.

我在这里找到了一些解决方案，但是有时它们的运行速度非常慢:

Thank you for the good link.
I have read it before posting my question here. Moreover, that''s a guide through how to detach, attach database using code when the database is not currently in use. My situation is another problem, We have to make the database not be in use first.

I found some solutions here, however sometimes they works very slowly:

ALTER DATABASE mydatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE
or
ALTER DATABASE mydatabase SET OFFLINE WITH ROLLBACK IMMEDIATE
or
Declare @DBname sysname
Set @DBname = 'mydatabase'
Declare @spid int
SELECT @spid = MIN(spid) FROM master.dbo.sysprocesses WHERE dbid = db_id(@DBname)
WHILE @spid is not null
BEGIN
EXECUTE('Kill ' + @spid)
SELECT @spid = MIN(spid) FROM master.dbo.sysprocesses WHERE dbid = db_id(@DBname) AND spid > @spid
END

在上面的摘要之一之前使用MASTER，然后使用detach命令.
如果存在到数据库的某些连接，则它们的工作速度非常慢！
谢谢！

USE MASTER before one of snippets above and then use the detach command.
If there are some connections to the database, they work really slow!
Thank you!

这篇关于在任何情况下，如何使用代码成功分离数据库?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！