Corrupted MDF Solution

Corrupted MDF solution

---

Procedure for recover MDF files when the LDF files are not available or corrupted. Detaching and Attaching are not possible normally.

• Copy the original mdf file to a secure place.
• Create a new database with the same name and same .mdf & .ldf file names.
• Stop the SQL Server.
• Replace the Corrupted .mdf with newly created .mdf file and Delete the .ldf file of the Database.
• Restart the SQL Server.
• See in Enterprise Manager the Database is in Suspect Condition.
• Update the sysdatabases to update to Emergency mode. This will not use LOG files in start up

Run the query in Query Analyser using Master Database.

Sp_configure "allow updates", 1
GO
Reconfigure with override
GO
Update sysdatabases set status = 32768 
where name = ‘BadDbName’
GO
Sp_configure "allow updates", 0
GO
Reconfigure with override

GO

• Stop & Restart SQL server. Now the database will be in emergency mode, Check in Enterprise Manager.
• Now execute the undocumented DBCC to create a log file.

DBCC REBUILD_LOG(BadDbname,'c:\BadDbname.ldf') 
-- Undocumented step to create a new log file.
(Replace the BadDbname and log file name based on your requirement)

• Execute :

sp_resetstatus <BadDbname>

• Restart SQL Server. See in Enterprise Manager the Data base is in online. If there is any situation like <DBO Only> Just Detach and attach the mdf in normal process.
• The Currupted Database Mdf file is now repaired and You can browse through the tables. Check for any missing records.

Try it when You are in trouble to attach a mdf file seeing the error message like 

< Server: Msg 5173, Level 16, State 1, Line 1
Cannot associate files with different databases.
Server: Msg 1813, Level 16, State 1, Line 1
Could not open new database 'dbABC'. CREATE DATABASE is aborted.
Log file 'D:\Microsoft SQL Server\MSSQL\Data\dbABC_log.LDF' does not match the primary file. 
It may be from a different database or the log may have been rebuilt previously.>