Attaching MDF without LDF

Question

I only have an .mdf file of my database which is not attaching.

I am using the following command:

EXEC sp_attach_single_file_db @dbname = 'Test_Data', 
@physname = N'D:\Test_Data\Test_Data.mdf';

and I get the following error message:

Msg 1813, Level 16, State 2, Line 1
Could not open new database 'Test_Data'. CREATE DATABASE is aborted.
Msg 824, Level 24, State 2, Line 1
SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:24; actual 0:0). It occurred during a read of page (1:24) in database ID 11 at offset 0x00000000030000 in file 'D:\Test_Data\Test_Data.mdf'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

Please help!

Answer 1

Your MDF is actually corrupt, based on that error message. This means you can't attach it.

You could create a database with the same name, substitute MDF (and rename LDF), and hope it comes online or at least suspect to start recovering it.

Otherwise, you'll need to find a 3rd party tool that understands MDF files to get data back.

Or just restore from your latest backup...?

Answer 2

You could try this - if your MDF is intact (which according to your error message it doesn't seem to be.....):

USE master
GO 

CREATE DATABASE Test_Data
ON PRIMARY 
(FILENAME = N'D:\Test_Data\Test_Data.mdf')
FOR ATTACH_REBUILD_LOG
GO 

The FOR ATTACH_REBUILD_LOG will attempt to create a new (empty) log file for your database.

Answer 3

The DB was not closed/shut down correctly and it needs its LDF file to open/attach. Find one and do a graceful shutdown. After it you may try to attach single file DB from its MDF

Otherwise your way will be long and hard. Use OrcaMDF free library to access the data, or various commercial tools.

Answer 4

Maybe you should try this. I got the answer from an MSDN thread.

• make a copy of your mdf file and change the extention (.mdf) to (.ldf)
• attach the mdf file using SSMS (note you have to locate the .ldf file)
• if you click ok ( error will occur) thats normal.
• select the ldf file and click remove. SQL server will automatically create a new ldf file for your attached mdf file.
• click OK now and your done..