1

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!

2
  • 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

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

4

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.

1

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.

2
  • 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
0

Error 1813;

SQL Server logs are corrupted and they need to be rebuilt to make the database operational.

Follow all the steps in order. Replace the yourdatabasename name with real name of your database.

  1. Create a new database with same name which you are trying to recover or restore. (In our error message it is yourdatabasename). Make sure the name of the MDF file (primary data file) and LDF files (Log files) same as previous database data and log file.

  2. Stop SQL Server. Move original MDF file from older server (or location) to new server (or location) by replacing just created MDF file. Delete the LDF file of new server just created.

  3. Start SQL Server. Database will be marked as suspect, which is expected.

  4. Make sure system tables of Master database allows to update the values.

  5. Change database mode to emergency mode. The following statement will return the current status of the database

  6. Restart SQL Server (This is must, it is not done SQL Server will through an error)

  7. Execute this DBCC command in query window of Management Studio, this will create a new log file. Keep the name of this file same as LDF file just deleted from new server :

    DBCC TRACEON (3604)

    DBCC REBUILD_LOG(yourdatabasename,'c:\yourdatabasename_log.ldf')

    GO

  8. Reset the database status using the following command.

    sp_RESETSTATUS yourdatabasename

-1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy