Skip to main content
5 of 6
added 282 characters in body
Craig Efrein
  • 9.7k
  • 12
  • 62
  • 97

The transaction log file can't make up for a corrupted database file. Its just going to try and rollback or roll forward transactions on the database file as it is recovered.

One more possible thing to try although I doubt it will work. Even if it does, with a corrupt mdf, you're probably not going to get back everything you need.

As a preliminary step, and being sure that you have a copy of your mdf and ldf files somewhere safe, remove any entries of the original database

DROP DATABASE mydatabase

1. Create a new database

Use the the same physical file names and logical file names as was defined by the original corrupt database

--create the database
CREATE DATABASE mydatabase
  ON(NAME=’mydatabase,
        FILENAME='C:\my\path\to\file.mdf')
  LOG ON(NAME='mydatabase_Log',
        FILENAME='C:\my\path\to\file.ldf')

GO

2. Shutdown MSSQL

SHUTDOWN

Now copy in the corrupt mdf and ldf files over the newly created ones

3. Start MSSQL

You should now see the database in suspect mode

4. Put database into emergency mode SQL Server 2005+

-- EMERGENCY mode repair
ALTER DATABASE mydatabase SET EMERGENCY;
GO

or

4. Put database into emergency mode SQL Server 2000

USE master
GO
sp_configure 'allow updates', 1
RECONFIGURE WITH OVERRIDE
GO

UPDATE sysdatabases SET status= 32768 WHERE name = 'mydatabase'

restart MSSQL

If that command works as expected, you should have read-only access to the database

I looked over the systools website and they have a utility for fixing corrupt mdf files. I have never used it so I can't say it has worked for me.

http://www.sqlrecoverytool.com/

Craig Efrein
  • 9.7k
  • 12
  • 62
  • 97