Link to home
Create AccountLog in
Avatar of bowser17
bowser17

asked on

Copy data from a corrupted SQL server database (MS SQL 2000)

Environment: Windows 2003 server, Microsoft SQL Server 2000
We had a DB go suspect, and after restoring it, i ran dbcc checkdb with the   REPAIR_ALLOW_DATA_LOSS, followed by REPAIR_REBUILD, and ran it until it couldn't fix any more errors.  here is a sample of the messages out of checkdb:

Server: Msg 1505, Level 16, State 1, Line 1
CREATE UNIQUE INDEX terminated because a duplicate key was found for index ID 1. Most significant primary key is '379346'.
Server: Msg 8980, Level 16, State 1, Line 1
Table error: Object ID 2105058535, index ID 1. Index node page (1:145727), slot 309 refers to child page (1:159) and previous child (1:6633), but they were not encountered.
Server: Msg 8977, Level 16, State 1, Line 1
Table error: Object ID 2105058535, index ID 1. Parent node for page (1:172) was not encountered.
Server: Msg 8980, Level 16, State 1, Line 1
Table error: Object ID 2105058535, index ID 1. Index node page (1:145727), slot 290 refers to child page (1:184) and previous child (1:185), but they were not encountered.
.........
DBCC results for 'AlarmMaster'.
        Repairing this error requires other errors to be corrected first.
        Repairing this error requires other errors to be corrected first.
Server: Msg 8980, Level 16, State 1, Line 1
Table error: Object ID 2105058535, index ID 1. Index node page (1:145727), slot 289 refers to child page (1:185) and previous child (1:191), but they were not encountered.
Server: Msg 8977, Level 16, State 1, Line 1
Table error: Object ID 2105058535, index ID 1. Parent node for page (1:186) was not encountered.
Server: Msg 8980, Level 16, State 1, Line 1
Table error: Object ID 2105058535, index ID 1. Index node page (1:145727), slot 288 refers to child page (1:191) and previous child (1:417), but they were not encountered.
Server: Msg 8935, Level 16, State 1, Line 1
Table error: Object ID 2105058535, index ID 1. The previous link (1:37596) on page (1:296) does not match the previous page (1:8064) that the parent (1:145727), slot 95 expects for this page.
Server: Msg 8935, Level 16, State 1, Line 1
Table error: Object ID 2105058535, index ID 1. The previous link (1:225229) on page (1:298) does not match the previous page (1:7720) that the parent (1:87659), slot 77 expects for this page.
Server: Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 2105058535, index ID 1. Page (1:298) is missing a reference from previous page (1:225229). Possible chain linkage problem.
Server: Msg 8980, Level 16, State 1, Line 1
Table error: Object ID 2105058535, index ID 1. Index node page (1:145727), slot 287 refers to child page (1:417) and previous child (1:423), but they were not encountered.
Server: Msg 8977, Level 16, State 1, Line 1
Table error: Object ID 2105058535, index ID 1. Parent node for page (1:418) was not encountered.
        Repairing this error requires other errors to be corrected first.
        Repairing this error requires other errors to be corrected first.
        Repairing this error requires other errors to be corrected first.
        Repairing this error requires other errors to be corrected first.
        Repairing this error requires other errors to be corrected first.
        Repairing this error requires other errors to be corrected first.
        Repairing this error requires other errors to be corrected first.
        Repairing this error requires other errors to be corrected first.
Server: Msg 8977, Level 16, State 1, Line 1

etc...



My thinking was that if i can copy each record out of a table one by one until i hit a bad page, then i can at least recover some of the data.  I was going to write a stored proc, but its been a long time since i've done this stuff, and cannot remember enough to do it.  i wanted to try to keep it generic so i could just call something like:  exec sp_copyBadTable <tableName> <destDatabase>  or something like that.  I am imagining some cursor work, but since each table has different fields i am not sure how to capture that.
Avatar of chapmandew
chapmandew
Flag of United States of America image

YOur best best, for sure, is to use DTS to copy your tables to a different database (completely new)...you can do all of them at one time....

right click on the suspect database and select 'Export', a wizard will then guide you through the process of copying data from your tables to a new db....you'll need to create the new db first though.

HTH,
Tim
Avatar of httpskk
httpskk


This is example for repair database "PUBS"  Start this query :

DBCC CHECKALLOC ('pubs')
GO
DBCC CHECKDB ('pubs')
GO
DBCC CHECKCATALOG  ('pubs')
GO
      
      USE pubs
      DECLARE @SQL  Nvarchar(1000)
      DECLARE @Table  Nvarchar(100)
      DECLARE curMoveDown CURSOR
                                    LOCAL
                                    FORWARD_ONLY
                                    OPTIMISTIC
                               FOR       
       select Name from dbo.sysobjects where OBJECTPROPERTY(id, N'IsUserTable') = 1
      -- ---------------------------------------------------------------------------
      OPEN curMoveDown
      FETCH NEXT FROM curMoveDown INTO @Table
      WHILE @@FETCH_STATUS = 0
      BEGIN
            DBCC CHECKTABLE (@Table)
             
      FETCH NEXT FROM curMoveDown INTO @Table
      END
      CLOSE curMoveDown
      DEALLOCATE curMoveDown      
        

 
 
 
 
Avatar of bowser17

ASKER

chapmandew: Already tried DTS, it fails.
httpskk:  Can you explain why this would be better or different than checkdb by it self?  According to microsoft, checkdb exactly what you are doing in that script.  http://technet.microsoft.com/en-us/library/ms176064.aspx
Does it give you a reason why it fails?
ASKER CERTIFIED SOLUTION
Avatar of bowser17
bowser17

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account