Solved

Recovering a database that has gone suspect due to "torn page"

Posted on 2002-06-10
9
540 Views
Last Modified: 2007-12-19
I have a 6gb MS-SQL 2000 Database which has gone suspect because of a spontaneous system reboot which left the database inaccessible due to a "torn page".
 
Does anyone know if there's a way to recover from a torn page, or know of a way to mount/attach the database or extract data from just the .mdf file?
0
Comment
Question by:sniles
  • 6
  • 2
9 Comments
 
LVL 35

Expert Comment

by:David Todd
Comment Utility
Hi,

If you have the .mdf file

look up

sp_attach_db
sp_attach_single_file_db

I'm not sure re the other questions

Regards
  David
0
 
LVL 5

Expert Comment

by:spcmnspff
Comment Utility
Do things in this exact order:

You need to run sp_resetstatus to reset the suspect flag. But First you have to allow updates to sytem tables.  

1.) Run this:

    USE master
    GO
    sp_configure 'allow updates', 1
    GO
    RECONFIGURE WITH OVERRIDE
    GO

2.)Then run this to switch the suspect bit in the sysdatabases table:

    exec sp_ResetStatus 'dbName'

3.) Then this to set the allow updates back:

    sp_configure 'allow updates', 0
    GO
    RECONFIGURE WITH OVERRIDE
    GO

4.) Stop and start the SQL Server Services.  
With any luck, the database should come up back online.

5.) Now run this to fix any corruption:

DBCC Checkdb 'dbName' Repair_Rebuild

Now, we should have a healthy, happy database. =)
Let me know if there's any complications . .  .
0
 
LVL 5

Expert Comment

by:spcmnspff
Comment Utility
Sorry, those set of instructions are incomplete.  Use these instead:

1.) Run this:

   USE master
   GO
   sp_configure 'allow updates', 1
   GO
   RECONFIGURE WITH OVERRIDE
   GO

2.)Then run this to switch the suspect bit in the sysdatabases table:

   exec sp_ResetStatus 'dbName'

3.) Then this to set the allow updates back:

   sp_configure 'allow updates', 0
   GO
   RECONFIGURE WITH OVERRIDE
   GO

4.) Stop and start the SQL Server Services.  
With any luck, the database should come up back online.

5.) Now switch the database to single user mode:

    EXEC sp_dboption 'dbName', 'single user', 'TRUE'


6.) Now run this to fix any corruption:

    DBCC Checkdb 'dbName' Repair_Rebuild

7.) Now switch back to multi user mode:
    EXEC sp_dboption 'dbName', 'single user', 'FALSE'


Let Me Know . . .  =)
0
 
LVL 5

Expert Comment

by:spcmnspff
Comment Utility
One more thing!  Here's the correct syntax for the dbcc command:

DBCC Checkdb ('dbName', Repair_Rebuild)


=)
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

Author Comment

by:sniles
Comment Utility
I need to find a way to first attach a standalone .mdf file with a torn page.  sp_single_file_db is typically what I've used before, however I've never had a database fail to attach due to a torn page/corruption.  If I could get it attached, I could then execute the dbcc checkdb, or at least try and export 6 days worth of data.  

The scenario was get the production database back in operation from a last known good backup (which was 6 days old).  I copied off the .mdf and log file before restoring so I could attempt to restore/attach it on another server to retrieve the data.
0
 
LVL 5

Expert Comment

by:spcmnspff
Comment Utility
Is the database completelt detached and not even listed as a database from EM. Or the database suspect i.e. is the database listed as DatabaseName(suspect) from EM?  

If this the case i s the latter, resetting the flag and stopping and starting should bring SQL server up with the database attached (in the state is was before the hard shutdown).

Other wise you may want to try the create table statement itself.  This is the manual way to reattach a database.  See the BOL.
0
 
LVL 5

Accepted Solution

by:
spcmnspff earned 200 total points
Comment Utility
My previous suggestion isn't going to work, sniles. Our only hope is our last backup which you have said was 6 days old.  Which recovery model is the database in?  Hopefully it's full recovery. Is it possible that, barring any dump or backup log statements, you have 6 days of transaction logging in the .ldf file.  In this case you can restore the backup to a separate server and apply the transaction log to it.
0
 

Author Comment

by:sniles
Comment Utility
Thanks so much for your help.  I did end up going to the 6 day old backup, but I really appreciate your input here.  At least I know that it was given a thorough try.
0
 
LVL 5

Expert Comment

by:spcmnspff
Comment Utility
I have since discussed this with a collegue he remembered back in the 6.5 days when this kinda thing occured more often that Microsoft had a tool that allowed you to explore the corrupted database page by page and extract any data.  He said the corruption was apparent, he could see data that belonged to one table in another table, etc.  And he could not exlpore any pages past the page that was torn.  I'm not sure if Microsoft still supports this, if this tool can be found elswhere, or even if it would work with 7.0.  But it makes a great story. Sort of like the Wild West for SQL server . . . Anyway thanks . . . =)

 
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now