SQL Server Express 无法附加或删除 mdf 文件
我的数据库文件 Whist.mdf 通过 Management Studio 连接到 SQL Server Express，但它停止工作，因为我的 asp.net 解决方案中的 NHibernate 引发了一些连接异常。我刚刚将其从 Management Studio 中分离出来，因为我也无法打开其中的数据库。
但是当我尝试重新连接它时，我收到此错误：
为服务器“MyPCName\SQLECPRESS”附加数据库失败。 (Microsoft.SqlServer.Smo) 无法打开物理文件“C:\Program 文件\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\Whist.mdf”。 操作系统错误 32：“32（进程无法访问该文件，因为 它正在被另一个进程使用）”。（Microsoft SQL Server，错误： 第5120章
我已经用谷歌搜索过，但找不到适合我的解决方案。例如：
- http://www.byteblocks.com/post/2010/01/06/Unable-to-open-the-physical-file-Microsoft-SQL-Server.aspx
- "无法打开用户默认数据库。登录失败。 ”安装 SQL Server Management Studio Express 后
有一些关于用户实例的信息，但我无法弄清楚它是否与此有关：
我有尝试删除 Windows 中的 mdf 文件，但我也无法这样做：
由于文件已在系统中打开，因此操作无法完成
我尝试停止 SQL Server 进程，关闭浏览并从 cmd 中删除并重新启动...
希望有人可以提供帮助 - 我想这只是一件简单的事情..
My database file Whist.mdf was attached to a SQL Server Express through Management Studio, but it stopped working in that sense that NHibernate in my asp.net solution fired some connection exceptions. I just detached it from Management Studio because I couldn't open the database in there either.
But when I try to reattach it I get this error:
Attach database failed for Server 'MyPCName\SQLECPRESS'. (Microsoft.SqlServer.Smo) Unable to open the physical file "C:\Program
Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\Whist.mdf".
Operating system error 32: "32(process cannot access the file because
it is being used by another process)". (Microsoft SQL Server, Error:
5120)
I have googled it but can't find a solution that works for me. E.g.:
- http://www.byteblocks.com/post/2010/01/06/Unable-to-open-the-physical-file-Microsoft-SQL-Server.aspx
- "Cannot open user default database. Login failed." after installing SQL Server Management Studio Express
There is something about User Instance but I cant figure out if it have anything to do with that:
I have tried to delete the mdf file in windows but I can't do that either:
The action can't be completed because the file is open in system
I have tried stopping the SQL Server process, closing explore and delete from cmd and rebooting...
Hope someone can help - I guess it is just a simple thing..
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
我认为你走在正确的道路上。您收到的错误消息表明某些 SQL Server 进程使数据库文件保持打开状态。 SQL Server 被设计为一次只允许单个 SQL Server 进程打开数据库文件。
您怀疑 SQL Server Express 的用户实例是这里的犯罪进程也可能是正确的。用户实例是一个特殊的 SQL Server 进程，它不作为服务运行，而是由尝试连接到它的应用程序启动并拥有。在您的情况下，有两个用户实例可能会出现问题，一个由您提到的 ASP.NET 应用程序拥有，另一个由您拥有并由 Visual Studio 启动。如果您使用 IIS Express 或为 ASP.NET 应用程序配置了 IIS 应用程序池以您的帐户运行，那么我们只需担心一个用户实例。
我的建议是您不要使用用户实例，也不要尝试直接连接到数据库文件。检查连接字符串并从所有连接字符串中删除
User Instance=true。您还想删除AttachDBFilename=...属性。如果数据库文件已添加到任何 Visual Studio 解决方案中，请将其删除（请注意不要删除文件本身）。最后一步是从服务器资源管理器的数据连接节点中删除与数据库文件的所有连接。
然后重新启动计算机以确保在后台运行的所有用户实例均已关闭。计算机再次启动后，您应该能够使用 SQL Server Management studio 将数据库文件附加回主 SQL Server Express。让我们将数据库命名为
Whist要连接到数据库，请使用以下连接字符串：
当您从 ASP.NET 应用程序进行连接时，请确保您已在 SQL Server Express 中为该应用程序创建了一个帐户。
I think you are on the right track. The error messages you are getting indicate that some SQL Server process is keeping the database file open. And SQL Server is designed to only allow the database file to be open by a single SQL Server process at a time.
You are also probably right to suspect User Instance of SQL Server Express to be the guilty process here. User Instance is a special SQL Server process that doesn't run as a service and instead is started and owned by the application that tries to connect to it. In your case there are two User Instances that can be the problem, one owned by the ASP.NET application you mentioned and one owned by you and started by Visual Studio. If you are using IIS Express or you configured the IIS App Pool for the ASP.NET application to run as your account then we only have one User Instance to worry about.
My recommendation is that you don't use User Instances and don't try to connect directly to the database file. Go over your connection strings and remove
User Instance=truefrom all of them. You want to removeAttachDBFilename=...property as well.If the database file is added to any of your Visual Studio solutions remove it (just be careful not to delete the file itself). The last step is to remove any connections to the database file from your Data Connections node in the Server Explorer.
Then restart the machine to make sure any User Instances that were running in the background were shutdown. After the machine starts again you should be able to attach the database file back to your main SQL Server Express using SQL Server Management studio. Let's name the database
WhistTo connect to your database use the following connection string:
When you connect from the ASP.NET application, make sure that you have created an account in your SQL Server Express for the application.
结果对我来说是守护进程工具以及将 .mdf 文件关联为图像的问题。
Turned out to be an issue with Daemon tools for me as well associating the .mdf file as an image.
如果您想从数据库复制 .mdf 和 .ldf 文件，您可以先将数据库文件设置为“脱机”。请按照以下步骤操作：
然后您可以将此数据库文件复制到任何文件夹。
If you want to copy your .mdf and .ldf file from your database, you can set your database file to 'offline' first . Please follow this steps:
Then you can copy this database file to any folders.