SQL Server 2000 Corrupt Database

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.

All Forums

General SQL Server Forums

Data Corruption Issues

SQL Server 2000 Corrupt Database

Author

Topic

serverdba
Starting Member

4 Posts

Posted - 2006-06-16 : 17:38:19

my User database (DB) is on Drive E & System DB is on Drive C. going to replace Drive E due to HardDisk problem. i did DBCC CHECKDB on User DB & found no issue & errors.
as i am going to move Data file from Drive E to C & then Back from Drive C, once Drive is replace. my question is as error was about TORN PAGE ERROR & if i copy back from Drive C to Drive E which NEW & CLEAN Drive does this TORN PAGE ERROR will be back. Any help is appreciated.

<edit> Moved to Data Corruption Forum </edit>

Kristen
Test

22859 Posts

Posted - 2006-06-18 : 02:27:54

My understanding is that copying from E: to C: and then from C: to new E: drive will NOT change a Torn Page in any way.

However, if your DBCC CHECKDB did not report any problems then your file is fine. I would suggesting making a BACKUP to the C: drive AND copying the MDF / LDF files from E: to C: (when SQL Server is STOPPED) and then swapping out the drive, and then copying back the MDF/LDF files (to folders named the same as the original).

Then restart SQL and do another DBCC CHECKDB. If you do have errors at that point try restoring from the backup and doing another DBCC CHECKDB.

Either way keep the old drive for a few days in case you need to recover anything off it!

Kristen

sponguru_dba
Yak Posting Veteran

93 Posts

Posted - 2006-06-18 : 05:08:47

Hi
ServerDBA I try to simulate the problem what u faced,but iam unable,u can do one thing

steps(do stpes at enterprise manager window)

1.First detach the database what u want move (make sure that Database > Properties >check the TORN PAGE DETECTION and while doing before it ask for option update statics check that option also)
2.Move the .MDF and .LDF file to the drive u desired

3.Now agian say attach Database it ask for Datafile show the new path where u copied

u r problem may solved

otherwise Kristen suggeted good methode of DB moving

regars
SreenivasaRao
09342196319

Kristen
Test

22859 Posts

Posted - 2006-06-18 : 12:42:39

Always make sure you have a Backup before you Detach - in case the database will not reattach. If you have one anyway, as part of your maintenance routine, fine! - otherwise make one!

Kristen

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2006-06-18 : 12:58:57

If you have a torn page the only solution is to go to a backup and do a restore. You will then have to play the transaction logs all the way up and try to get a full recovery. There is no other solution.

attaching/detaching does nothing to fix this.

-ec

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2006-06-18 : 13:05:51

I agree with Eyechart and Kristen on this.

If you have a database that's corrupt in some way, it's extremely bad advice to detach it because you may never be able to attach it again if any critical pages (needed for attach) are damaged. Do not follow sponguru_dba's advice.

Sorry for not jumping in here earlier - I was flying back from TechEd yesterday.

Paul Randal
Lead Program Manager, Microsoft SQL Server Storage Engine + SQL Express
(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.)

sponguru_dba
Yak Posting Veteran

93 Posts

Posted - 2006-06-20 : 06:32:01

Thanks Paurandal,eyechart and Kristen for your suggestions

I have a suggetion it is better to do HW(Hard disk E: drive and C: drive) diagnostic for trouble like bad clusters,damesge in hard disk,try to solve that ,before that back up your DB is more impartent

Regards
SreenivasaRao
09342196319

Kristen
Test

22859 Posts

Posted - 2006-06-20 : 17:15:21

I'm not sure I have understood your reply exactly, but we make Backups to "different" drives - so if the Data file is on D: and the logs on E: we make backups to F:

(If only two drives we backup to the Logs drive)

So ... if the database is corrupted there is a good change that the Transaction Log, and its backups, are NOT.

We can then recover the last [known good!] FULL backup, and then all the transaction logs in order, and often get a 100% recovery, no data lost, because the corruption was in the MDF file, on a different drive, and the Log File, and the Log Backups, are uncorrupted.

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources