Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
Lets eliminate the upgrade from the picture. Attach the database to a SQL Server 2000 instance. If successfull, take a backup and then restore the backup on the SQL Server 2008 instance. –  Remus Rusanu May 22 '13 at 6:50
    
@RemusRusanu: Is SQl Server 2000 same as MSDE 2000? microsoft.com/en-in/download/details.aspx?id=22661 –  Devdatta Tengshe May 22 '13 at 7:02
    
Yes it is, but make sure SP4 is installed on the MSDE2000. –  Remus Rusanu May 22 '13 at 7:03
3  
Make sure that you have another copy of your (corrupt?) files before attempting anything... –  Justicator May 22 '13 at 9:13
    
Marginally related: dba.stackexchange.com/questions/9885/… –  Aaron Bertrand May 22 '13 at 13:48
add comment

2 Answers 2

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?

share|improve this answer
    
Alas that doesn't work. The database wasn't critical in any way, so no major problems. –  Devdatta Tengshe May 23 '13 at 6:19
add comment

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

share|improve this answer
    
I Get the same error message with more details: Msg 601, Level 12, State 3, Line 2 Could not continue scan with NOLOCK due to data movement. –  Devdatta Tengshe May 22 '13 at 6:19
add comment

protected by Mark Storey-Smith Apr 22 at 20:25

Thank you for your interest in this question. Because it has attracted low-quality answers, posting an answer now requires 10 reputation on this site.

Would you like to answer one of these unanswered questions instead?

Not the answer you're looking for? Browse other questions tagged or ask your own question.