SQL Server 2005 恢复挂起
我有一个数据库在 SQL 企业管理器中显示为“（正在恢复...）”
如果我执行 SP_WHO，则没有恢复过程。
服务器上的磁盘和 CPU 活动非常低，
我认为它根本没有恢复。
我怎样才能摆脱这个？
我尝试重命名底层 MDF 文件，但即使我执行“NET STOP MSSQLSERVER”，它也会告诉我该文件已打开。
我尝试使用 PROCEXP 来查找打开文件的进程，但即使是最新的 PROCEXP 似乎也无法在 Windows Server 2003 R2 x64 上执行此操作。 下部窗格视图是空白的。
在 SQL Server 日志中，它显示“数据库被标记为 RESTORING，并且处于不允许运行恢复的状态”
I have a database showing up in SQL Enterprise Manager as "(Restoring...)"
If i do SP_WHO there is no restore process.
The disk and CPU activity on the server is very low
I think it is not restoring at all.
How can I get rid of this?
I've tried renaming the underlying MDF file, but even when I do "NET STOP MSSQLSERVER" it tells me the file is open.
I've tried using PROCEXP to find what process has the file open, but even the latest PROCEXP can't seem to do that on Windows Server 2003 R2 x64. The lower pane view is blank.
In the SQL Server log it says "the database is marked RESTORING and is in a state that does not allow recovery to be run"
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
Sql Server 有两种备份类型：
恢复时，Sql Server 会询问您是否要在完整备份后恢复附加日志。 如果您选择此选项（称为“WITH NORECOVERY”），数据库将处于“正在恢复”状态。 它将等待更多事务日志被恢复。
您可以通过以下方式强制它退出恢复模式：
如果此命令出现错误，请分离数据库，删除 MDF 文件，然后从头开始恢复。 如果一直失败，您的备份文件可能已损坏。
这是恢复选项的屏幕截图，其中选择了默认值。 第二个选项将使数据库处于正在恢复状态。
恢复选项图片 http://img193.imageshack.us/img193/8366/captureu.png
PS1。 您正在运行 64 位版本的 Process Explorer 吗？ 验证您是否在任务管理器中看到 procexp64.exe。
PS2。 这更像是 serverfault 的问题。
Sql Server has two backup types:
When restoring, Sql Server asks you if you want to restore additional logs after the full backup. If you choose this option, called WITH NORECOVERY, the database will be left in Restoring state. It will be waiting for more transaction logs to be restored.
You can force it out of Restoring mode with:
If this command gives an error, detach the database, remove the MDF files, and start the restore from scratch. If it keeps failing, your backup file might be corrupted.
Here's a screenshot of the restore options, with the default selected. The second option will leave the database in Restoring state.
Image of the restore options http://img193.imageshack.us/img193/8366/captureu.png
P.S.1. Are you running the 64 bit version of process explorer? Verify that you see procexp64.exe in the task manager.
P.S.2. This is more like a question for serverfault.
执行 RESTORE DATABASE/RESTORE LOG 命令时，默认使用WITH RECOVERY 选项。 如果您陷入“恢复”过程，您可以通过执行以下命令将数据库恢复到在线状态：
您可以在这篇文章中寻找更多选项和一些第三方工具 https://stackoverflow.com/a/21192066/2808398
WITH RECOVERY option is used by default when RESTORE DATABASE/RESTORE LOG commands is executed. If you're stuck in "restoring" process you can bring back a database to online state by executing:
You can look for more options and some third party tools on this SO post https://stackoverflow.com/a/21192066/2808398
如果您想摆脱文件的锁定，我建议您使用 Unlocker http://www.emptyloop .com/unlocker/
它将为您提供解锁文件或终止锁定文件的进程的选项。 在 mdf 和 ldf 文件上运行此命令。
另一种选择是尝试从 Enterprise Manager 或 Sql Management Studio 分离文件，然后重新附加数据库。 您可以在运行unlocker之前尝试此操作，看看sql server是否会释放mdf和ldf文件。
注意：如果您终止该进程，您可能会丢失数据或数据可能会损坏，因此仅当您尝试删除它并且您有良好且经过测试的备份时才使用此选项。
If you are trying to get rid of the lock on the file I would recommend getting Unlocker http://www.emptyloop.com/unlocker/
It'll give you an option to unlock the file, or kill the process that has locked the file. Run this on the mdf and ldf files.
Another option is to try to Detach the files from Enterprise Manager or Sql Management Studio and then reattach the db. You can try this before running unlocker to see if sql server will just release the mdf and ldf files.
CAUTION: If you kill the process you might lose data or the data might get corrupted so use this only if you are trying to get rid of it and you have a good and tested backup.