Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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!

share|improve this question

migrated from stackoverflow.com Feb 2 '12 at 8:45

This question came from our site for professional and enthusiast programmers.

    
Have a look at this post - it discusses how to rebuild the log in emergency mode, but as it requires a restart of SQL server isn't something to do on your Prod Server (Nb - Jose Basilio's answer, not the accepted one) stackoverflow.com/questions/773059/… –  StuartLC Feb 1 '12 at 11:27
    
@nonnb I've done this already but received error Ad hoc updates to system catalogs are not allowed. when executing this statement Update sysdatabases set status = 32768 where name = "BadDbName" –  Ali Feb 1 '12 at 11:33

4 Answers 4

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...?

share|improve this answer

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.

share|improve this answer

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.

share|improve this answer
    
This is what the Problem is as my Question Title says. Is there any way I can recover my data from MDF? –  Ali Feb 1 '12 at 11:21
    
Corrected answer –  Oleg Dok Feb 1 '12 at 11:24

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..
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.