Corrupted MDF solution Procedure for recover MDF files when the LDF files are not available or corrupted. Detaching and Attaching are not po...
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
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.
-- Undocumented step to create a new log file.
(Replace the BadDbname and log file name based on your requirement)
- Execute :
- 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.>
COMMENTS