How to attach MDF with no log file? [closed]

Question

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?

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)

Answer 1

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;

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

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

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;

However, it seems that in your case, the file wasn't properly detached from 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.

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.

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.

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.

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.