How to attach an .mdf file that was not properly detached?

Question

One of our clients had a SQL Server 2000 database, on a system which is no longer running. They have no idea what was in the database. They have given me the .mdf and .ldf files, and want us to investigate and extract information if any.

I don't think the database was properly detached when these files were backed up, since I am getting the following error when I try to attach these files to my SQL Server 2008 express edition:

Could not continue scan with NOLOCK due to data movement. Converting database 'GRMStemp' from version 539 to the current version 655. Database 'GRMStemp' running the upgrade step from version 539 to version 551. (Microsoft SQL Server, Error: 601)

What should I do to attach the database in this situation?

Answer 1

It sounds like the database is corrupt. Before you do anything further, make a copy of the files, and only work with copies.

You might try the following, in case the problem is isolated to the log:

CREATE DATABASE [GRMStemp]
  ON (FILENAME = 'c:\wherever\MDF_File.mdf')
  FOR ATTACH_REBUILD_LOG;

If that fails, there is a forum post on MSDN by Paul Randal which describes a process that might work. Basically:

• on your 2008 instance, create a database with a data file of the exact same size (I wouldn't bother trying to put a 2000 instance anywhere)
• shutdown SQL Server
• rename the new database's mdf file, and paste the old one in its place
• bring up the server and let the database attempt to be recovered and then go into suspect mode
• put the database into emergency mode with ALTER DATABASE GRMStemp SET EMERGENCY;
• run DBCC CHECKDB (GRMStemp, REPAIR_ALLOW_DATA_LOSS); which will rebuild the log and run full repair

BTW If the client has no idea what was in the database, and wasn't responsible enough to maintain proper backups, how important could the data be?

Answer 2

Error 601 means

When scanning with the NOLOCK locking hint or with the transaction isolation level set to READ UNCOMMITTED, it is possible for the page at the current position of the scan to be deleted. When this happens, Microsoft® SQL Server™ is not able to continue the scan.

You may want to try this:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
GO

CREATE DATABASE GRMStemp
    ON (FILENAME = '<mdf file path>'), 
    (FILENAME = '<ldf file path>') 
    FOR ATTACH; 
GO

Raj