SQL Server 备份还原问题？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7811100/
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
SQL Server backup restore issues?
提问by Kulahan
Ok, so I'm having a bit of an issue - I executed some code on my SQL Server and didn't realize that I didn't have the WHERE bit selected. Of course, when I saw the "608 rows affected"rather than "1 row affected", I freaked a bit.
好的，所以我遇到了一些问题 - 我在我的 SQL Server 上执行了一些代码，但没有意识到我没有选择 WHERE 位。当然，当我看到"608 rows affected"而不是 时"1 row affected"，我有点害怕。
Luckily, I have a backup saved, but for some reason, I'm getting a couple issues. Now, I took the server down, so I know that it's not being used by anyone, but it's giving me the following error
幸运的是，我保存了一个备份，但由于某种原因，我遇到了一些问题。现在，我关闭了服务器，所以我知道它没有被任何人使用，但是它给了我以下错误
"Restore failed for Server 'myserver'.
System.Data.sqlclient.sqlerror: Exclusive access could not be obtained because the database is in use. (Microsoft.SqlServer.Smo)"
“服务器‘myserver’的恢复失败。
System.Data.sqlclient.sqlerror：无法获得独占访问，因为数据库正在使用中。(Microsoft.SqlServer.Smo)"
I saw something that stated I should be using
我看到一些说明我应该使用的东西
Alter Database Databases
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
RESTORE DATABASE PRODUCT
FROM DISK = ''
but I'm having three reservations about this code. First, I'm completely unsure of how to turn multi_user back on. Second, I don't know where the program stores its backups. Third, this SQL is a bit above my head - I'm relatively new to the language, honestly, so I'm not sure how this will affect things.
但我对这个代码有三个保留意见。首先，我完全不确定如何重新打开 multi_user。其次，我不知道程序在哪里存储备份。第三，这个 SQL 有点超出我的理解 - 老实说，我对这门语言比较陌生，所以我不确定这会如何影响事情。
Anyone have any answers to my troubles?
有人对我的烦恼有答案吗？
采纳答案by user2145577
Set the database to single user is correct. When you are complete with your restoration you'll execute this
将数据库设置为单用户是正确的。完成恢复后，您将执行此操作
alter database YourDb
set multi_user
As for where your .bak file resides, you'll have to find it prior to restoring.
至于 .bak 文件所在的位置，您必须在恢复之前找到它。
回答by Michael Fredrickson
I might suggest instead of overwriting the existing database from the backup, that you instead recover the backup with a different database name... this way you can have the current and previous databases side-by-side.
我可能建议您不要从备份中覆盖现有数据库，而是使用不同的数据库名称恢复备份……这样您就可以并排使用当前和以前的数据库。
Then, you can simply write an update statement to recover the data from just that one specific table instead of resetting the whole database.
然后，您可以简单地编写一条更新语句来仅从该特定表中恢复数据，而不是重置整个数据库。
EDIT: The specifics would depend on your environment, but the restore would look something like this:
编辑：细节将取决于您的环境，但恢复看起来像这样：
restore database PRODUCT_OLD
from disk='C:\PRODUCT.bak'
with
move 'PRODUCT_Data' to 'C:\PRODUCT_OLD_Data.MDF',
move 'PRODUCT_Log' to 'C:\PRODUCT_OLD_Log.LDF'
And then the update statement would also be based on your specific table...
然后更新语句也将基于您的特定表...
回答by user2145577
right on the database click tasks->takeoffline, when its succeed do the same thing but put it Bring Onlinethen try to restore your database
在数据库上点击tasks-> takeoffline，当它成功时做同样的事情但把它Bring Online然后尝试恢复你的数据库
回答by n8wrl
Scary.
害怕。
Ok, some things to check:
好的，要检查一些事情：
- Make sure you are the only person connected to the server.
- Make sure no other applications, web servers, app servers, etc. hold connections to your DB.
- Make sure your SQL manager has no open windows to tables or other objects in your database.
- 确保您是唯一连接到服务器的人。
- 确保没有其他应用程序、Web 服务器、应用程序服务器等连接到您的数据库。
- 确保您的 SQL 管理器没有打开数据库中的表或其他对象的窗口。
THEN you should be able to do the restore w/o single user stuff.
那么你应该能够在没有单用户的情况下进行恢复。
回答by Rajesh Panicker
Go to the activity Monitor and see if users are still logged in then kill the process for that user using the respective db. Then go ahead restore the backup
转到活动监视器并查看用户是否仍处于登录状态，然后使用相应的数据库终止该用户的进程。然后继续恢复备份
