SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Data Corruption Issues
 Corrupt SQL 2000 Database - Error 823
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 06/09/2006 :  08:50:19  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
Robert writes "Is it possible to recover any data from a database that is throwing the following error in query analyzer?

select * from table1

server: msg 823, level 24, atate 2, line 1
I/O error (bad page ID) detected during read at offset 0x00000007b7e000 in file '... dbname.mdf'


I can select top n from some tables with no error, if n is too large, I get the same error. Other tables give this error even for top 1.

After setting the db in single_user mode, I tried dbcc checkdb with repair_allow_data_loss option and received the following:

msg 8966, level 16, state 1, line 1
could not read and latch page ( 1:15807 ) with latch type SH. sysindexes failed.

I tried dbcc checktable on sysindexes with repair_allow_data_loss option and received the following:

msg 8966, level 16, state 1, line 1
could not read and latch page ( 1:15807 ) with latch type SH. sysindexes failed.
msg 8966, level 16, state 1, line 1
table error: object id 0, indexid 0, page ID (1:15807) the pageid in the page header = (0:0)
The error has been repaired.

I get the same message each time I run the DBCC command.

I can select * from sysindexes with no problem.

If I select * from sysindexkeys, I get the following:

msg 601, level 12, state 3, line 1
could not continue scan with nolock due to data movement.

I'm wondering if sysindexkeys is corrupt and causing this problem?...

I have also tried to bcp data out of the database with no success.

Any help or advice would be greatly appreciated.

Thanks in advance.

Robert"

paulrandal
Yak with Vast SQL Skills

USA
899 Posts

Posted - 06/09/2006 :  12:39:46  Show Profile  Visit paulrandal's Homepage  Reply with Quote
First question, why did you go straight to repair? (Check out my recent blog post on bad advice during disaster recovery https://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/07/621060.aspx)

The answer to your question is yes, even though its a sysindexes page that is corrupt. Repair isn't going to help you at all - repair can't fix pages in the sysindexes clustered index. Do you have any backups? These are your best bet.

Can you give more details on bcp failing? What exactly did you try to do and how did it fail?

Thanks

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.)
Go to Top of Page

pricer
Starting Member

USA
1 Posts

Posted - 06/20/2006 :  21:43:21  Show Profile  Reply with Quote
Paul,

First, I apologize for letting this thread go stale. My department has been going through a move and reorganization for the last week...

I know, excuses are for those who need them...

I am trying to help out one of our customers. They did not backup the database before it became corrupted... big surprise. They sent me a backup of the corrupted database and asked me to try and recover any data I can.

Repair wasn't my first choice, it was just the latest in many attempts to get any useful data out of the database. I agree with you, it doesn't work for this problem...

btw, I did read your post on bad advice and found it very helpful.


Here is the bcp command:

C:\>bcp databaseName..sample out "C:\bcp-sample.csv" -c -t, -r\n -S serverName -E

When I execute it, I get the following error:

SQLState = S1000, NativeError = 823
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]I/O error (bad page ID) detected during read at offset 0x00000007b7e000 in file 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\databaseName.mdf'.

This is basically the same error I get when I try a select statemnt against the table in query analyzer.

I really appreciate your help on this.
Go to Top of Page

paulrandal
Yak with Vast SQL Skills

USA
899 Posts

Posted - 06/20/2006 :  21:54:29  Show Profile  Visit paulrandal's Homepage  Reply with Quote
The page that's bad in sysindexes (15807) is the one that the 823 is complaining about (15807 x 8192 converted to hex = 7b7e0000). It looks like it contains the metadata for the 'sample' table so there's no way you can get this table data out without manually poking about in the database using DBCC PAGE. CSS can't do this and its beyond the scope of this forum to do so you'll need to tell the client that the data is irretreivable unless they want to go to a data recovery firm (and they need a better backup strategy).

Thanks

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.)
Go to Top of Page

Davidpoul
Starting Member

India
14 Posts

Posted - 07/09/2009 :  03:43:07  Show Profile  Visit Davidpoul's Homepage  Reply with Quote
Yes it is possible to recover any data from a database that is throwing the error in query analyzer? Not only this you are able to recover your deleted data also. To recover your data from database you need to take the help of SQL Recovery software from stellar. It helps you to recover your data just in few clicks. You can download it from http://www.mssqldatabaserecovery.com

David Poul
Go to Top of Page

adomlinks
Starting Member

Spain
1 Posts

Posted - 01/21/2010 :  08:02:37  Show Profile  Reply with Quote
If the sysindexkeys gets corrupted and also shows these errors after running bcp command or query analyzer, you need to take the help of a third-party data recovery tool. One such suggested software product for recovering SQL database contents is SysTools SQL Rcovery software. You can download this software from http://www.sqlrecoverytool.com or mssqlrepair.org

Adom Watson

View SQL Recovery & MS SQL Repair for SQL Diaster Recovery.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000