mirthcyy avatar image
mirthcyy asked

Restore a corrupted database

Hi guys, I have a database with one table corrupted. I tried to use DBCC CHECKDB to fix the corruption with data loss but I can't. The database can still be used with no problem. Only one table is not accessible. Now I need to move that database (sql server 2000) to another server (sql server 2008). I tried to restore the backup file but it gave me the error "Could not continue scan with NOLOCK due to data movement" so I have to restore with CONTINUE_AFTER_ERROR option. So the database is restored but it's offline. I brought it online but it's not accessible. Also tried to move to sql server 2005. Same result. Could someone here please help? Is there any other way to fix the corruption? I tried several tools but it only recover a few tables. If corruption can't be fixed, how can I just restored the corrupted database which can still be used? It's a SAP Database so it has over 10k tables and even more stored proces and views. Too hard to script out everything and bcp out the data. Thanks a lot!!!
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Cyborg avatar image Cyborg commented ·
What is the DBCC Error you got?
1 Like 1 ·
mirthcyy avatar image mirthcyy commented ·
I tried the above solution and the red gate frozen completely when using Object Level Recovery. Even after 1 day it's not coming back so I will move redgate backup to another server and try again. Is there any other way for this? The database just has too many objects.
0 Likes 0 ·
Fatherjack avatar image Fatherjack ♦♦ commented ·
I'd be surprised if there are too many objects but that is something that RedGate would be better placed to answer than me. Try restoring the whole database to a different server and then copy the data from the 'problem' table to the database that is missing its data
0 Likes 0 ·

1 Answer

Fatherjack avatar image
Fatherjack answered
My solution to this would be to get everything but the problem table into another **new** database(*) from you current system and then restore the data to the problem table from a backup. It will be a bit of a job but it should be reliable. (*) - I'd go for something like SQL Compare and SQL Data Compare from [RedGate][1] as those are what we use but there are other products that will help you. SQL Backup does Object Level Recovery so just the required table could be picked out on its own. They do 14 day free trial of full products so you could get this done expense free. I would expect they'd love to hear from you if their tools help you out. [1]:
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.