x

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!!!

more ▼

asked Feb 25, 2011 at 11:53 AM in Default

mirthcyy gravatar image

mirthcyy
52 2 2 2

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.
Feb 28, 2011 at 11:26 AM mirthcyy
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
Feb 28, 2011 at 11:43 PM Fatherjack ♦♦
What is the DBCC Error you got?
Apr 30, 2012 at 01:03 PM Cyborg
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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]: http://www.red-gate.com
more ▼

answered Feb 25, 2011 at 12:12 PM

Fatherjack gravatar image

Fatherjack ♦♦
42.7k 75 79 108

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x729
x474

asked: Feb 25, 2011 at 11:53 AM

Seen: 2150 times

Last Updated: Mar 14, 2013 at 11:21 AM