如何在没有日志文件的情况下附加 MDF? [英] How to attach MDF with no log file?

问题描述

我正在尝试在没有日志文件的 SQL Server Management Studio 中附加 Yafnet.mdf.

I'm trying to attach Yafnet.mdf in SQL Server Management Studio, which does not have a log file.

我收到以下错误.任何想法如何做到这一点?

I get the error below. Any ideas how this can be done?

执行 Transact-SQL 语句或批处理时发生异常.(Microsoft.SqlServer.ConnectionInfo)

无法打开物理文件C:\sql_logs\YAFnet_log.ldf".操作系统错误 2:2(系统找不到指定的文件.)".(Microsoft SQL Server，错误:5120)

推荐答案

对于您的初始情况，您似乎尝试过这样的事情(或在您浏览对话框时 GUI 为您准备的任何内容):

For your initial situation, it seems you tried something like this (or whatever the GUI prepares for you when you go through the dialogs):

CREATE DATABASE YAFnet ON (FILENAME = N'C:\sql_data\YAFnet.mdf')
FOR ATTACH;

但是，此方法需要 mdf 文件和 ldf 文件.否则，您会收到类似于以下内容的错误消息:

However, this method requires both an mdf file and an ldf file. Otherwise you get an error message similar to:

消息 5120，级别 16，状态 101，第 1 行
无法打开物理文件C:\sql_logs\YAFnet_log.ldf".操作系统错误 2:2(系统找不到指定的文件.)".

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.)".

现在，即使您只有 mdf 文件，也有一种方法可以继续.假设您有一个从 SQL Server 正确分离的 mdf 文件，您应该能够使用以下语法附加没有日志文件的 mdf 文件:

Now, there is a way to proceed even if you only have the mdf file. Assuming that you have an mdf file that was properly detached from SQL Server, you should be able to attach the mdf file without a log file using the following syntax:

CREATE DATABASE YAFnet ON (FILENAME = N'C:\sql_data\YAFnet.mdf')
FOR ATTACH_REBUILD_LOG;

但是，在您的情况下，该文件似乎没有与 SQL Server 正确分离:

However, it seems that in your case, the file wasn't properly detached from SQL Server:

消息 1813，级别 16，状态 2，第 1 行
物理文件名C:\sql_logs\YAFnet_log.ldf"可能不正确.无法重建日志，因为在数据库关闭时有打开的事务/用户，数据库没有发生检查点，或者数据库是只读的.如果由于硬件或环境故障手动删除或丢失事务日志文件，则可能会发生此错误.

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.

有几种可能的解释，包括错误消息中提到的解释.也许它是从一些无效的 SAN 影子中检索到的，或者在只读时分离，或者在 SQL Server 或底层系统崩溃后恢复，或者在复制/下载过程中损坏，或者谁知道还有什么.

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.

您需要返回 Yaf 的支持或他们的服务提供商的支持，以查看是否有适当的备份mdf 文件.另请记住，我们中没有人真正知道 Yaf 是什么，也没有任何方法可以验证您在谈论哪个 Yaf.

You will need to go back to Yaf's support, or their service provider's support, to see if there are proper backups available or, failing that, alternate copies of the mdf file. 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.

否则，您似乎不走运，因为这个特定的 mdf 文件无效，因此不会让您走得太远.

Otherwise, it seems like you are out of luck, since this particular mdf file is invalid and thus not going to get you very far.

这正是为什么分离/附加和/或 O/S 级文件复制方法对于 SQL Server 来说不是非常有用的备份(或迁移)方法的原因.您需要一个适当的备份/恢复计划，这意味着采取适当的完整/差异/日志备份适合您对数据丢失的容忍度.分离数据库几乎总是一个糟糕的主意 - 当 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 mdf file during or after the detach, you now have ZERO copies of your database.

这篇关于如何在没有日志文件的情况下附加 MDF?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！