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

Just received question from one of the DBA

Question: I do not have full backup of my database. My .mdf and .ldf are corrupted. Is there any way I can restore database now?

Answer: Sorry. I do not think there is any way you can do it. Try attaching this files to database using db_attach but if that does not work, it will be very difficult make it work.

If any of blog reader know fix for this, please post here.

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

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

  1. Very difficult, but I have found the following can sometimes work:

    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

    Hope this helps

    dvc

    Like

  2. Pingback: SQL SERVER - 2005 - Restore Database Using Corrupt Datafiles (.mdf and .ldf) - Part 2 Journey to SQL Authority with Pinal Dave

  3. Recommend use this tool-fix mdf,as far as i know it is free and has many other features,utility can repair data from corrupted databases in the MS SQL Server format (files with the *.mdf extension),supports data extraction via the local area network,can save recovered data as SQL scripts, it is also possible to split data into files of any size,compatible with all supported versions of Microsoft Windows, such as Windows 98, Windows Me, Windows NT 4.0, Windows 2000, Windows XP, Windows XP SP2, Windows 2003 Server, Windows Vista,tool supports the following database formats: Microsoft SQL Server 7.0, 2000, 2005,also can repair .mdf files of Microsoft SQL Server 2005, repair mdf file of Microsoft SQL Server 2005 (64-bit).

    Like

  4. Hi Pinal,

    Accidently i removed mdf file with dbservicess off, when i chenged it to on and see the db list in database other db showing without expand sign why this any way let me try above t-sql.

    Thank you

    Like

  5. I do not have full backup of my database. Only My .mdf file corrupted. Is there any way I can recover my mdf file by using the .ldf file now?

    Like

  6. Hi Jeeva,

    Please let us know the complete error message and where you are facing the error? what resolution you have tried yet?
    As the .mdf is the file where data is stored and we do not have much options to recover from corrupted .mdf file.

    Regards,
    Pinal Dave

    Like

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