How To Recover Corrupt .mdf & .ldf Files

Published by admin on December 4, 2015

The importance of having a strong database management system is very much compulsory for personal and professional usage. It not only helps to manage official work but helps in keeping track of administrative record too. There can be so many reasons behind the corruption of the database that results to finally loss of your important data. Many time it is also seen that while MDF file is get corrupted, DB admin shows the error when tries to execute DBCC CHECKDB command. It will also shows error when admin tries to restore files from an upgrade backup.

This DBCC CHECKDB is mainly used to repair corrupt MDF files. It perfectly recovers the MDF files that got damaged due to some logical corruption. The admin restore data from the updated backup as when required. Main issue arises when no database backup is available using which it can be restored again.

You may get stuck with the following error

Error 5101: DEVICE ACTIVATION ERROR

This error generally comes when the physical file name ‘C:\%path to db%\db_log.LDF ‘ found incorrect. When it is corrected you might get the error, “db Attach failed”.

   

LDF is the log file that comes attached with MDF database files. It is the most common error which Admin gets when they try to open their MDF files in order to access the Database objects. To fix this error, admin runs DBCC CHECKDB (‘dbname’, REPAIR_REBUILD) command. However, while executing this command admin gets another error that is mentioned below

Server: Msg 945, Level 14, State 2, Line 1

 Database can’t be opened due to inaccessible files, disk space or insufficient memory. See the SQL server error log for further more details about this.

  

Steps for recovery when LDF is corrupted

First use sp_attach_single_file_db

CODE

USE master;GOEXEC sp_detach_db @dbname = ‘AdventureWorks’;EXEC sp_attach_single_file_db @dbname = ‘AdventureWorks’,     @physname = N’C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_Data.mdf’;

If this won’t work try the following steps:

1) keep a copy of your.MDF file

2) create a new database called( default file location.

3) stop using SQL Service

4) Delete the fake_Data.MDF and copy your.MDF to where fake_Data.MDF is actually saved and rename the file with the same name of fake_Data.MDF

5) now Start SQL Service

6) your fake database will appear as suspect in EM.

7) Open Query Analyzer and execute the following code in the Master database:

CODE

sp_configure ‘allow updates’,1     go     reconfigure with override     go     update sysdatabases set        status=-32768 where dbid=DB_ID(‘fake’)     go     sp_configure ‘allow updates’,0     go     reconfigure with override     go

This will take your database in the emergency recovery mode

8) Once again Stop SQL Service

9) Delete the fake_Log.LDF file

10) Then restart the SQL Service

11) In Query Analyzer run the following (with correct path for log)

CODE

dbcc rebuild_log(‘fake’,’h:\fake_log.ldf’)    go    dbcc checkdb(‘fake’) — to check for errors    go

12) now, rename the files, run the following code in Query Analyzer. This time you are actually accessing the database so that you can use DTS or BCP to move the data to another databaseCODE

    use master    go    sp_helpdb ‘fake’    go    /* Make a note of the names of the files , you will need them    in the next bit of the script to replace datafilename and    logfilename – it might be that they have the right names  */    sp_renamedb ‘fake’,’your’    go    alter database your        MODIFY FILE(NAME=’datafilename’, NEWNAME = ‘your_data’)    go    alter database PowerDVD301        MODIFY FILE(NAME=’logfilename’, NEWNAME = ‘your_Log’)    go    dbcc checkdb(‘your’)    go    sp_dboption ‘your’,’dbo use only’,’false’    go    use your    go    sp_updatestats    go

13) you should now have a working database. However the log file size is too small so unfortunately your files will be called fake_Data.MDF and fake_Log.LDF but you can solve this issue by detaching the database properly and then renaming the files and reattaching it again.

14) Run the following code in QA

CODE

sp_detach_db your    –now rename the files then reattach    sp_attach_db ‘your’,’your.mdf’,’your.ldf’

These are the few errors that come and disturb you at your work which finally results to losing lots of information. So to fix it you can try the above mentioned solution but if in case using these steps you are not able to fix your corrupt .mdf and .ldf file then you must try a trusted third party repair tool to fix out this corruption issue. It’s better to repair your corrupt SQL .mdf and .ldf files using SQL Repair Tool as it is the most reliable tool for recovering damaged SQL files. Efficient in repairing MDF and LDF files, and recovers views, indexes, tables, stored procedures, unique/primary keys etc. that is saved in your damaged SQL database.

 STEPS TO FIX CORRUPT SQL DATABASE:

Step 1: Stop running MS SQL server, the repair process is done on the copy of corrupt database so tap to ‘OK’ button and continue.

Step 2: now click on the ‘Select database’ button and select the path of corrupt MDF file. After that search for your corrupt database file by using ‘Look in’ and ‘File Type’ button. To start with the repairing process click on the ‘Scan file’ button.

Step 3: The complete listing of recoverable objects of database is in tree view on the left side of the window. Tapping to the object you can see the preview.

Step 4: Click on the ‘Start Repair’ icon to begin the process. To save the repaired file in the desired location click on the browse button and assign the path, otherwise the repaired file will be saved in the ‘Default SQL Location’. Click on the ‘Ok’ button.

447 Total Views 1 Views Today