Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
debug
Starting Member
29 Posts |
 Posted - 2003-07-14 : 04:18:43
|
| Hi All,Just wanted your views in the following scenario :I had a database in which data in one of the table got corrupt due to page corruption.Now, I just want to know if DBCC CHECKDB would be better of shall I BCP the data out in some other table..Drop the Corrupt table ...recreate it and then again BCP the data in that fresh table.I just want to know your views in this according to your own experience.DU |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-07-14 : 09:22:47
|
| Do a checkdb...Also, I'm not sure (but I'd put money on it) that if you export a corrupted table wouldn't you be exporting the corruption?Brett8-) |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-07-14 : 13:01:13
|
| Brett, the corruption does not get exported when you bcp the data out of the table. This is actually a popular method to fix the corrupted table. Debug, yes you should bcp the data out, drop the corrupted table, recreate it, then bcp the data again. But you also need to find out if there are any hardware problems. Check the Event Viewer. Disk problems are a common cause of corruption. After you have successfully rebuilt the table, run DBCC CHECKDB again to ensure that the corruption no longer exists.Tara |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-07-14 : 13:12:33
|
| Thanks...but that can't be true in all cases, no?I mean if the ibject is hosed, I'm assuming the definition of that object is hosed, so how does it know to get the data out cleanly?I bet this done more frequntly back in 6.5 days...Rarley see any corruption anymore..some with 7.0None in 2000Brett8-) |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-07-14 : 13:28:20
|
| Debug,Have you tried any of the repair options available with DBCC CHECKDB?REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILDTara |
|
|
|
efelito
Constraint Violating Yak Guru
478 Posts |
Posted - 2003-07-14 : 14:36:14
|
| If the dbcc's aren't working and restoring from a backup to a point before the corruption happened is not an option, many people look to bcp to save them. If the corruption is in the table itself you may get some errors in bcp for the corrupted records, but you will get the majority of your data and only have to deal with minor data loss. Most of the time I've come across corruption it has been in the indexes on a given table. In those cases you can use bcp without any dataloss. Sometimes you can even just drop and rebuild the indexes on the table and its all good. Corruption has definitly been on the decline, but it does still happen in all versions.Jeff Banschbach, MCDBAEdited by - efelito on 07/14/2003 14:37:59 |
|
|
|
debug
Starting Member
29 Posts |
Posted - 2003-07-21 : 02:58:51
|
| Thanks a lot all... I tried out Tara's method....and prefer to do that in future....i had a data loss of 20 rows and that's acceptable.REgards,DU |
|
|
|
|
|
|
|
|
DBCC CHECKDB Vs. BCP