ERROR 823 (Torn Page) When Attaching Database

[cyork]

Hi,

I was updating a table when my server crashed. Once the server was back online I could not access the database so I tried detaching it. Now when I try to re-attach it I get error message 823 (torn page). The help file says to restore the database from a back-up but this is unfortunately not an option for me.

The database has several un-related tables so only the table I was working on should have corrupted data but the others should be fine but how do I get at them?

Any ideas on how I can re-attach this database?

Thanks,

Chris

 

[bperry]

I don't work on admin issues like backups, but i can give you this quick comment until someone more knowledgeable responds...

Ordinarily[sup]*[/sup], you cannot restore individual database tables. What you are actually restoring to the disk is a physical file, which typically contains all kinds of tables, plus other database objects as well. So when you do a restore, all the objects in that file get restored.


* The only exception I'm aware of is if you specifically create more than one physical database file, and then specifically designate one particular table to be the only object in that file. But that's not the situation you've got here.

 

[tlbroadbent]

From what I have read, you don't have any options except restoring from backup. If you don't have backups you may be out of luck. You could open a ticket with Microsoft Support and someone may be able to help you recover. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[cyork]

I have made arrangements to try and get a copy of the original database. Unfortunately it does not have any of the changes or data manipulation I have done. I wish I had a backup but this company is determined to cut IT spending at any cost...

Thank you for your quick responses guys.

Chris

 

[tlbroadbent]

Cutting IT cost by not doing backups makes as much sense as cutting transportation costs by never checking or changing the oil in my car. I may save money for a few months or years but the big cost to replace an engine easily offsets those savings.

The costs of not having backups include lost data, data recovery time (if it is even possible), downtime, analysis time, lost trust, perhaps lost customers, etc.

IT departments without a backup policy and procedure abound. It is hard imagine an IT department with policy of no backups. Sorry you got caught in such a situation. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[bperry]

I wish I had a backup but this company is
determined to cut IT spending at any cost

I agree with Terry, that's the most remarkable statement I've ever heard. The managers at this company must be pretty scary.

I've heard it said that "The only job a DBA has to get right is backups and restores; everything else is just extra." Obviously an overstatement, but there's more than a thread of truth in there.

I like the way a former co-worker of mine put it:

"We do backups for the same reason that they put erasers on pencils: because we're gonna need them."

 

[bperry]

tbroadbent:
I am wondering if, instead of detaching, he had done a dbcc checkdb with a REPAIR_REBUILD option, perhaps that would have dug him out of the hole. But once the Detach command worked, he was out of luck.

what's your guess?

 

[tlbroadbent]

I found a number of articles that indicated possible fixes for the torn page condition. One common thread was an attached database being repaired. None of the articles addressed the issue of detached database with a torn page condition. Having glanced at those articles, but never having encountered the situation, I can't say if they would work.

I wonder if Chris could attach the databse using the following scenario and then attempt to repair.

1) Rename the physical files by adding a short suffix or prefix to each file name.
2) Create a new database of the same name using the original file names.
3) Stop SQL Server.
4) Delete the new files.
5) Rename the old files to the original names.
6) Start SQL Server.

The database would probably still be marked suspect but would be available to attempt a fix. I don't have the fixes bookmarked but they should be easy to find using Google or Google group search. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.