Attaching an Mdf File Without Ldf File

How to attach a database by mdf file that has not been detached

Solution:

Assuming the DB name is ‘Sample’ and DB files path is ‘D:\DB’

Step 1: Open Microsoft SQL Server Management Studio to create a New Database with
Database name: Sample
Path: D:\DB

Step2: Stop the DB Server

Step3: Delete Sample.mdf and Sample.ldf files from folder D:\DB

Step4: Copy the old Sample.mdf to the folder D:\DB

Step5: Start the DB Server

Step6: execute the following sql to detach DB

exec sp_detach_db Sample,'true'

Step7: execute the following sql to attach DB and then refresh

exec sp_attach_single_file_db 'Sample','D:/DB/Sample.mdf'

SQL Server - can I load MDF without LDF file without losing data?

Assuming the database was detached cleanly, you should be able to use sp_attach_single_file_db or the newer CREATE DATABASE...FOR ATTACH syntax.

EXEC sp_attach_single_file_db 
    @dbname = 'YourDB', 
    @physname = N'C:\YourFile.mdf';

OR

CREATE DATABASE YourDB
      ON (FILENAME = 'c:\YourFile.mdf') 
      FOR ATTACH_REBUILD_LOG;

How to attach MDF with no log file?

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.

---

Related Topics