restore failing w/ nolock error when moving from SQL 2k to 2005

[swhitten]

We are attemping to migrate a customer to a new server with SQL 2005 SP3. They are currently on SQL 2000 SP3. They have 11 dbs. We made SQL 2000 backups of all dbs. Then we successfully restored the dynamics db and 9 of the 11 dbs to the SQL 2005 server. Using Management Studio, we right click on databases and restore. The Progress indicator reaches 100%, but then we get an error that says:
Restored failed. System.Data.SqlClient.SQLError: Could not continue scan with NOLOCK due to data movement.

Next we tried restoring using a script. It gets to 100 percent processed. Then it reports how many pages it processed for the mdf and ldf files. Then it says 'converting db from version 539 to current version 611'. Then it says 'running the upgrade step from version 539 to version 551.' At that point, we get errors.

Msg 601 Level 12 State 3 Line 1
Could not continue scan with NOLOCK due to data movement
Msg 3167 Level 16 State 1 Line 1
Restore could not start database
Msg 3013 Level 16, State 1, Line 1
Restore database is terminating abnormally.

Ideas? Thanks!

 

[DrSql]

Did you run the SQL Server 2005 upgrade avisor agains that database prior to the move else I would suggest to running that. You can obtain the copy

http://www.microsoft.com/downloads/...6B-7E05-4322-A677-95AB44F12D75&displaylang=en

Dr.Sql
Good Luck.

 

[swhitten]

We ran the advisor, and it found some torn pages. We went back to the original dbs and ran maintenance there - major issues. Trying to work on resolving them now. So in short, I think we have a data integrity issue with the original data/back ups rather than a restore issue.

Thanks for the suggestion!

 

[mrdenny]

Sounds like a database corruption issue on the source machine. Are the problems within indexes or the tables them selves? If the problem is within the indexes rebuilding the index should fix the problem.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog

 

[swhitten]

Thanks. We had two dbs with issues. For one, we were able to finally get it resolved by rebuilding some indexes and some other things. For the second one, we ended up having to make a new db, run sql queries on the old db to export the data out to a file, then DTS the data into the new db. It was a mess, but it is up and runnin.