Return to Answer

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/

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'

5. restart MSSQL

5a. (optional) Since you're data is now accessible via Query Analyzer, you could just export your important data into a new database.

-- create the new database
CREATE DATABASE mynewdatabase

-- import data from the old database into the new
SELECT * INTO mynedatabase..sometable FROM mydatabase..sometable.

This won't help though with your triggers and metadata, but at least you'd have your data accessible in case the repair fails

6. Repair the database

This is the method I've used for repairing the database once its in emergency mode.

USE mydatabase
GO 

-- put database into single user mode
sp_dboption 'mydatabase', 'single_user', 'true'
GO 

-- repair the database, please be sure you have an extra copy somewhere of
-- your two db files
DBCC CHECKDB ('mydatabase' repair_allow_data_loss)
GO 

-- turn off emergency mode
USE master
sp_configure 'allow updates', 1
UPDATE sysdatabases SET status= 0 WHERE name = 'mydatabase'
GO

-- check space allocation consistency
DBCC CHECKALLOC ('mydatabase')

-- verify db integrity
DBCC CHECKDB ('mydatabase')

-- take the database out of single user mode
sp_dboption 'mydatabase', 'single_user', 'false'
GO 

-- disallow updates to the system tables
USE master
GO 
sp_configure 'allow updates', 0
GO

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

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

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/

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/

You can try the following, to at least get the database into EMERGENCY mode which, if it works, will give you read-only access.

First Create a new database using the existing files

--move database files manually to new server
CREATE DATABASE mynewdatabase
      ON(NAME=’mydatabase’,
            FILENAME='C:\DataFiles\mydatabase.mdf')
      LOG ON(NAME='mydatabase_Log',
            FILENAME='C:\LogFiles\mydatabase_Log.ldf')
      FOR ATTACH

Then if it shows up in SSMS, try putting it into EMERGENCY mode

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

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

Let me know.

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

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

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/