SQL SERVER – 2005 – Restore Database Using Corrupt Datafiles (.mdf and .ldf) – Part 2

Blog reader Donald Crowther has posted following comment. I have not tested this solution and when I tried to test it, it did not work for me. However, I have received email from two of my Jr. DBA who have done experiment about this and they are suggesting it works.

If you have tried everything and you have given up to find solution. Try following suggestion. Make sure you have taken backup of your physical file and also this exercise you do at your own risk.
ALTER DATABASE test
SET emergency
GO
ALTER DATABASE test
SET single_user
GO
DBCC checkdb (test, repair_allow_data_loss)
ALTER DATABASE test
SET multi_user
GO

Reference : Pinal Dave (http://blog.SQLAuthority.com)

About these ads

16 thoughts on “SQL SERVER – 2005 – Restore Database Using Corrupt Datafiles (.mdf and .ldf) – Part 2

  1. Dear Pinal Deve,

    I have a backup from a customer, and i can restore it with out a problem, but the problem is i could not query the database, and it is giving following errors. The customer says he had a power failure.

    1. Following Error displayed when querying user table

    [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionRead (recv()).
    Server: Msg 11, Level 16, State 1, Line 0
    General network error. Check your network documentation.

    Connection Broken

    2. Following Error displayed after lists some records when querying table SysObjects

    Server: Msg 5242, Level 22, State 1, Line 1
    An inconsistency was detected during an internal operation in database ‘MBBalkh'(ID:33) on page (1:204).
    Please contact technical support. Reference number 6.

    3. When i try to repair using the above command given by Donald Crowther, after processing 45 minutes it is giving the following error.

    [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionCheckForData (CheckforData()).
    Server: Msg 11, Level 16, State 1, Line 0
    General network error. Check your network documentation.
    ODBC: Msg 0, Level 16, State 1
    Communication link failure

    Connection Broken

    Any help recovering my database ?

    Best regards,
    Seenivasan

  2. This script worked well for me when restoring a WSS 3.0 content database with only the mdf and no ldf.

    After running this script i was able to browse the data and attach it to a SharePoint site.

    Thanks.

  3. Pinal Dave,

    I have an sql 2005 express db that has been corrupted. The end user has no backups so I’ve tried using DBCC checkdb and I get the following error:

    SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0x17b8ed1d; actual: 0x79d02a32). It occurred during a read of page (1:1957) in database ID 7 at offset 0x00000000f4a000 in file ‘C:\bytedownload\BYTE_SKU.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.
    During redoing of a logged operation in database ‘BYTE_SKU’, an error occurred at log record ID (15907:16:98). Typically, the specific failure is previously logged as an error in the Windows Event Log service. Restore the database from a full backup, or repair the database.
    Could not open new database ‘BYTE_SKU’. CREATE DATABASE is aborted. (Microsoft SQL Server, Error: 824)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=824&LinkId=20476

    The dB was originally marked ‘suspect’ but I was able to set to Emergency/Single User. Now I can write Select statements against the tables but still the db is corrupt.

    Any suggestions as to what I might try next?
    Best Regards, Matt

  4. Hi,

    I have a 2 TB database and the checkdb gave be below output
    CHECKDB found 0 allocation errors and 949 consistency errors in database ‘PRD’.
    repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (PRD ).
    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

    After running with repair_allow_data_loss, I got below output

    CHECKDB found 0 allocation errors and 949 consistency errors in database ‘PRD’.
    CHECKDB fixed 0 allocation errors and 902 consistency errors in database ‘PRD’.
    repair_rebuild is the minimum repair level for the errors found by DBCC CHECKDB (PRD repair_allow_data_loss).
    The statement has been terminated.

    Please let me know how to repair the database completely. And is there any way to find out which datafile is most corrupted?

    Thanks in advance…

  5. Dear Mr. Pinal,

    I used above script – SQL SERVER – 2005 – Restore Database Using Corrupt Datafiles (.mdf and .ldf) – Part 2

    and restored from the suspect data.

    I will be thankfull to you.

  6. Thanks Dave for the useful tips to restore data. However, there are some situations when CHECKDB and DBCC command line tools do not work. In such cases, a third party sql database recovery application may help you to repair and recover data.

    One such globally used tool that worked in our case: http://www.serversdatarecovery.com/sqldatabase.html

    Hope it will also help to those who are also facing similar problem.

    Thanks again for these wonderful guide.
    Smike

  7. Pingback: SQL SERVER – Weekly Series – Memory Lane – #024 | SQL Server Journey with SQL Authority

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s