SQL 如何在没有日志文件的情况下附加 MDF？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14248260/
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
How to attach MDF with no log file?
提问by 4thSpace
I'm trying to attach Yafnet.mdfin SQL Server Management Studio, which does not have a log file.
我正在尝试Yafnet.mdf在没有日志文件的 SQL Server Management Studio 中进行附加。
I get the error below. Any ideas how this can be done?
我收到以下错误。任何想法如何做到这一点？
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
Unable to open the physical file "C:\sql_logs\YAFnet_log.ldf". Operating system error 2: "2(The system cannot find the file specified.)". (Microsoft SQL Server, Error: 5120)
执行 Transact-SQL 语句或批处理时发生异常。(Microsoft.SqlServer.ConnectionInfo)
无法打开物理文件“C:\sql_logs\YAFnet_log.ldf”。操作系统错误 2：“2（系统找不到指定的文件。）”。（Microsoft SQL Server，错误：5120）
回答by Aaron Bertrand
For your initial situation, it seems you tried something like this (or whatever the GUI prepares for you when you go through the dialogs):
对于您的初始情况，您似乎尝试过这样的事情（或在您浏览对话框时 GUI 为您准备的任何内容）：
CREATE DATABASE YAFnet ON (FILENAME = N'C:\sql_data\YAFnet.mdf')
FOR ATTACH;
However, this method requires both an mdffile and an ldffile. Otherwise you get an error message similar to:
但是，此方法需要一个mdf文件和一个ldf文件。否则，您会收到类似于以下内容的错误消息：
Msg 5120, Level 16, State 101, Line 1
Unable to open the physical file "C:\sql_logs\YAFnet_log.ldf". Operating system error 2: "2(The system cannot find the file specified.)".
消息 5120，级别 16，状态 101，第 1 行
无法打开物理文件“C:\sql_logs\YAFnet_log.ldf”。操作系统错误 2：“2（系统找不到指定的文件。）”。
Now, there is a way to proceed even if you only have the mdffile. Assuming that you have an mdffile that was properly detached from SQL Server, you should be able to attach the mdffile without a log file using the following syntax:
现在，即使您只有该mdf文件，也有一种方法可以继续。假设您有一个mdf从 SQL Server 正确分离的文件，您应该能够mdf使用以下语法附加没有日志文件的文件：
CREATE DATABASE YAFnet ON (FILENAME = N'C:\sql_data\YAFnet.mdf')
FOR ATTACH_REBUILD_LOG;
However, it seems that in your case, the file wasn't properly detached from SQL Server:
但是，在您的情况下，该文件似乎未从 SQL Server 正确分离：
Msg 1813, Level 16, State 2, Line 1
The physical file name "C:\sql_logs\YAFnet_log.ldf" may be incorrect. The log cannot be rebuilt because there were open transactions/users when the database was shutdown, no checkpoint occurred to the database, or the database was read-only. This error could occur if the transaction log file was manually deleted or lost due to a hardware or environment failure.
消息 1813，级别 16，状态 2，第 1 行
物理文件名“C:\sql_logs\YAFnet_log.ldf”可能不正确。无法重建日志，因为在数据库关闭时有打开的事务/用户，数据库没有发生检查点，或者数据库是只读的。如果由于硬件或环境故障手动删除或丢失事务日志文件，则可能会发生此错误。
There are several possible explanations, including those mentioned in the error message. Perhaps it was retrieved from some invalid SAN shadow, or detached while read only, or recovered after SQL Server or the underlying system crashed, or corrupted during copy/download, or who knows what else.
有几种可能的解释，包括错误消息中提到的解释。也许它是从一些无效的 SAN 影子中检索到的，或者在只读时分离，或者在 SQL Server 或底层系统崩溃后恢复，或者在复制/下载过程中损坏，或者谁知道还有什么。
You will need to go back to Yaf's support, or their service provider's support, to see if there are proper backupsavailable or, failing that, alternate copies of the mdffile. Also keep in mind that none of us really knows what Yaf is or has any way to verify which Yaf you're talking about.
您将需要返回 Yaf 的支持或他们的服务提供商的支持，以查看是否有适当的备份可用，或者，如果没有，mdf文件的备用副本。还要记住，我们中没有人真正知道 Yaf 是什么，也没有任何方法可以验证您在谈论哪个 Yaf。
Otherwise, it seems like you are out of luck, since this particular mdffile is invalid and thus not going to get you very far.
否则，您似乎不走运，因为此特定mdf文件无效，因此不会让您走得太远。
This is precisely why the detach / attach and/or O/S level file copy approaches are not very useful methods of backup (or migration, for that matter) for SQL Server. You need a proper backup/recovery plan, which means taking proper full/diff/log backups appropriate for your tolerance for data loss. And detaching a database is almost always an inferior idea - when something happens to the mdffile during or after the detach, you now have ZEROcopies of your database.
这正是为什么分离/附加和/或 O/S 级文件复制方法对于 SQL Server 来说不是非常有用的备份（或迁移）方法的原因。您需要一个适当的备份/恢复计划，这意味着采取适当的完整/差异/日志备份适合您对数据丢失的容忍度。并且分离数据库几乎总是一个糟糕的想法 - 当mdf分离期间或之后文件发生某些事情时，您现在拥有数据库的零副本。
回答by Tarzan
There are a couple of things you can try. First:
您可以尝试几件事。第一的：
CREATE DATABASE Yafnet ON (NAME = 'Yafnet', FILENAME='*filepath*\Yafnet.mdf') FOR ATTACH;
If that doesn't work then try this:
如果这不起作用，请尝试以下操作：
Right click on SSMS and Run As Administrator. Then try to attach again. This should resolve the problem.
右键单击 SSMS 并以管理员身份运行。然后再次尝试附加。这应该可以解决问题。
