November 14, 2007 at 2:08 pm
This is a duzy:
Was called to assist the DBA with an HR database that had not been backed up since Feb 2007, great. The following set of activities have been run with the following results. Any assistance would be greatly appreciated:
Database Version: SQL 2000-8.00.194 (SQL Server 2000 RTM) - No service packs
DatabaseName: LongShine
Main Table trying to recover: C41
select * from longshine..c41
/*
Server: Msg 823, Level 24, State 2, Line 1
I/O error (bad page ID) detected during read at offset 0x000000034ee000 in file 'E:\SQL\MSSQL\data\ls\LongShine_Data.MDF'.
Connection Broken
*/
DBCC CHECKDB('Longshine')
/*
CHECKDB found 0 allocation errors and 87 consistency errors in database 'LongShine'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (LongShine ).
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
*/
dbcc dbreindex('C41')
/*
Server: Msg 823, Level 24, State 2, Line 1
I/O error (bad page ID) detected during read at offset 0x00000002100000 in file 'E:\SQL\MSSQL\data\ls\LongShine_Data.MDF'.
Connection Broken
*/
DBCC CHECKTABLE ('LongShine.dbo.C41')
/*
There are 10511 rows in 411 pages for object 'C41'.
CHECKTABLE found 0 allocation errors and 40 consistency errors in table 'C41' (object ID 821577965).
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKTABLE (LongShine.dbo.C41 ).
*/
The DBA has told me that she has tried to run the repair_allow_data_loss and they are missing around 61 rows of data from this year they are trying to query. I was trying to identify if I could read the actual data pages with using a dbcc traceon(3406) dbcc page('longshine,1,AnyPageNumber,1) in order to try to piece the data together but I cannot. I would even try to rebuild, not sure what to do or if anything is even possible. Anyone want to advise or work with me to see if the data can be read and pieced back together?
John :w00t:
John LaSpada
November 14, 2007 at 2:43 pm
You need to use T3604, not T3406.
The data in the repaired database will not be available. The page that had the 823 error cannot be read by the operating system - that data is gone. All repair did would have been to deallocate the page (I'm guessing as you didn't post the full list of errors)
(To anyone that's going to suggest using a log analysis or data recovery tool - neither will work in this situation so don't bother.)
The only chance you have is to restore the old backup and see if the same page in the restored database has the missing rows of data - if so you may be able to copy the rows over into your main database. If the page has different rows, then you need to work out which rows are missing in the main database and then see if any of those rows exist in the restored database - not trivial to do.
Unfortunately this is more complicated than can be done over a forum but you may be able to make headway with the info above. Here's an old blogpost I did while back in the Storage Engine team at MS - this may help you too. http://blogs.msdn.com/sqlserverstorageengine/archive/2007/01/18/fixing-damaged-pages-using-page-restore-or-manual-inserts.aspx
Let me know how you get on.
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
November 14, 2007 at 3:15 pm
Paul,
That was a typo. I just checked my saved code and it was dbcc traceon(3604). Did you see anything else you would do in my situation or know of a way I can read the data pages even with a text editor.
Thanks,
John
John LaSpada
November 14, 2007 at 3:24 pm
No problem.
Nope - what I posted is what I would do in this situation.
Thanks
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply