desperate for even partial recovery

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Data Corruption Issues

desperate for even partial recovery

Author

Topic

fsilber
Starting Member

11 Posts

Posted - 2008-02-12 : 10:32:34

I have been asked to see what I can recover from a development server whose database became suspect during a power failure.

One developer from another group who's time is limited tried to repair the database using checkdb, but it is still suspect.

There are no recent backups of anything whatsoever. Needless to say, we are lacking in DBA skills here. At this point, we don't care whether we get the data back, but we are desperate to recover the table definitions, user-defined functions and stored procedures -- if not all of them than most of them; if not most of them than some.

What are our options here?

Are their any good third-party tools to help us with this problem?

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2008-02-12 : 11:46:39

Lumigent and/or Idera have some log recovery tools.

You may be able to do some select * from tablex where col1 between < xyz and abc type queries .... if you know the table names (systables may be able to help you there....and syscomments). Sometimes tables are partially accessible, and if you re-run the query with different params you may be able to skip around the faulty data.

Time to invest in some backup and source code management practices....(starting when this exercise is complete). Search here for comments from member PaulRandal - he wrote the DBCC code.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-02-12 : 23:53:39

Don't think those tools can work on suspect db, get Microsoft support involved.

fsilber
Starting Member

11 Posts

Posted - 2008-02-13 : 09:31:33

quote:
Originally posted by rmiao

Don't think those tools can work on suspect db, get Microsoft support involved.

I tried getting Microsoft support involved. They said that SQL Server 2000 SP3 is no longer supported; I would have to upgrade to SP4 before they will even talk to me.

I am not a DBA, and I think learning to upgrade a database would be as difficult and risky as trying to recover this one. I've been reading some good stuff at http://www.sqlskills.com/blogs/paul on using EMERGENCY MODE.

I'm trying to fill in the details now, as his writing presumes a modicum of DBA knowledge, and I'm basically starting from scratch.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-13 : 10:33:15

He is newly married now, but otherwise Paul Randal is present in this forum.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-13 : 10:35:04

And support for SP4 with SQL Server 2000 is due April 8, 2008.

E 12°55'05.25"
N 56°04'39.16"

fsilber
Starting Member

11 Posts

Posted - 2008-02-18 : 11:16:16

I tried following the directions I read online (http://www.sqlskills.com/blogs/paul/), but I'm having trouble.

I allowed updates to system catalogs.

I set the status to EMERGENCY MODE by issuing "upate systdatabases set status = status | 32768 where name = ..." (Some web pages suggested setting the status to 32768 rather than OR-ing it with the current suspect status. Could that be the problem?)

I altered the database to set SINGLE_USER

When I ran "DBCC CHECKDB ('MEMTiltData') WITH ALL_ERRORMSGS, NO_INFOMSGS" I got:

Server: Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 22759634, index ID 2. Page (1:285494) is missing a reference from previous page (1:895053). Possible chain linkage problem.
Server: Msg 8981, Level 16, State 1, Line 1
Table error: Object ID 22759634, index ID 2. The next pointer of (1:295759) refers to page (1:295756). Neither (1:295756) nor its parent were encountered. Possible bad chain linkage.
Server: Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 22759634, index ID 2. Page (1:399418) is missing a reference from previous page (1:295759). Possible chain linkage problem.
Server: Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 22759634, index ID 2. Page (1:888178) is missing a reference from previous page (1:1191228). Possible chain linkage problem.
Server: Msg 8935, Level 16, State 1, Line 1
Table error: Object ID 22759634, index ID 2. The previous link (1:891741) on page (1:891740) does not match the previous page (1:1020415) that the parent (1:582952), slot 135 expects for this page.
Server: Msg 8936, Level 16, State 1, Line 1
Table error: Object ID 22759634, index ID 2. B-tree chain linkage mismatch. (1:1020415)->next = (1:891740), but (1:891740)->Prev = (1:891741).
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 22759634, index ID 2: Page (1:895053) could not be processed. See other errors for details.
Server: Msg 8939, Level 16, State 1, Line 1
Table error: Object ID 22759634, index ID 2, page (1:895053). Test (IS_ON (BUF_IOERR, bp->bstat) && bp->berrcode) failed. Values are 2057 and -1.
Server: Msg 8976, Level 16, State 1, Line 1
Table error: Object ID 22759634, index ID 2. Page (1:895053) was not seen in the scan although its parent (1:454896) and previous (1:18838) refer to it. Check any previous errors.
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 22759634, index ID 2: Page (1:1191228) could not be processed. See other errors for details.
Server: Msg 8939, Level 16, State 1, Line 1
Table error: Object ID 22759634, index ID 2, page (1:1191228). Test (IS_ON (BUF_IOERR, bp->bstat) && bp->berrcode) failed. Values are 2057 and -1.
Server: Msg 8976, Level 16, State 1, Line 1
Table error: Object ID 22759634, index ID 2. Page (1:1191228) was not seen in the scan although its parent (1:1326427) and previous (1:1057677) refer to it. Check any previous errors.
CHECKDB found 0 allocation errors and 12 consistency errors in table 'PrimaryTrackingNumberBank' (object ID 22759634).
CHECKDB found 0 allocation errors and 12 consistency errors in database 'MEMTiltData'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (MEMTiltData ).

Then I tried running "So I tried running: DBCC CHECKDB ('MEMTiltData', REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS, NO_INFOMSGS" and I got:

Server: Msg 3908, Level 16, State 1, Line 1
Could not run BEGIN TRANSACTION in database 'MEMTiltData' because the database is in bypass recovery mode.

Why is it not correcting my errors? Again, I don't care about losing data in this analytical database; I can replace the data. I just need the stored procedures and such, and I have no recent backup.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-02-18 : 12:44:19

You can change status like this, but the db is still in suspect.

fsilber
Starting Member

11 Posts

Posted - 2008-02-18 : 13:52:45

quote:
Originally posted by rmiao

You can change status like this, but the db is still in suspect.

Yes, but running

DBCC CHECKDB ('MEMTiltData', REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS, NO_INFOMSGS

was supposed to change that.

fsilber
Starting Member

11 Posts

Posted - 2008-02-18 : 14:43:26

Actually, I just saw that what I did in EmergencyMode does indeed let me see the database in QueryAnalyzer, but not in Enterprise Manager.

All I care about is the structure of the database (schema and stored procedures), so I don't care whether any data was lost. Can I assume, despite the error message:

Server: Msg 3908, Level 16, State 1, Line 1 --Could not run BEGIN TRANSACTION in database 'MEMTiltData' because the database is in bypass recovery mode.)

that the corruption has been repaired? Can I now simply reset the status and restart to try and bring the database online?

Or is EmergencyMode a one-way trip, requiring me to copy the contents into a new database?

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-02-18 : 20:29:09

No the db is still in suspect, above sql message told you that.

fsilber
Starting Member

11 Posts

Posted - 2008-02-19 : 10:23:13

quote:
Originally posted by rmiao

No the db is still in suspect, above sql message told you that.

Which above sql message told me that?

I thought the whole point of running CHECKDB with option REPAIR_ALLOW_DATA_LOSS in emergency mode was supposed to rip out the corrupt leaving me with a database that _isn't_ suspect (albeit at the possible cost of transactional integrity).

How, then, am I supposed to remove the corruption so that the database isn't suspect anymore?

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-02-19 : 12:22:32

After running sp_resetstaus 'DBname' , did it again show error.

fsilber
Starting Member

11 Posts

Posted - 2008-02-19 : 13:20:48

quote:
Originally posted by sodeep

After running sp_resetstaus 'DBname' , did it again show error.

Yes, I did sp_resetstatus so that it was no longer in suspect mode -- but resetstatus left it in emergency mode. The error message I got:

Server: Msg 3908, Level 16, State 1, Line 1
Could not run BEGIN TRANSACTION in database 'MEMTiltData' because the database is in bypass recovery mode.

was due to EMERGENCY MODE, not SUSPECT status.

Should I hack the system table to take it out of EMERGENCY MODE before running CHECKDB with REPAIR_ALLOW_DATA_LOSS? (Note that if I restart the server to make the removal of EMERGENCY MODE fully take effect, the attempt to restore the database will put it back into SUSPECT status).

By the way, I am running SQL Server 2000 Service Pack 3, if that matters.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-02-19 : 22:23:43

>> Which above sql message told me that?

You just posted it again: Could not run BEGIN TRANSACTION in database 'MEMTiltData' because the database is in bypass recovery mode.

When db is in emergency mode, dba can get READ access to the db for troubleshooting. The db itself is still in damage, changing status doesn't fix problem at all.

Since you have read access now, try script the db.

fsilber
Starting Member

11 Posts

Posted - 2008-02-20 : 15:43:34

quote:
Originally posted by rmiao

>> Which above sql message told me that?

You just posted it again: Could not run BEGIN TRANSACTION in database 'MEMTiltData' because the database is in bypass recovery mode.

When db is in emergency mode, dba can get READ access to the db for troubleshooting. The db itself is still in damage, changing status doesn't fix problem at all.

Since you have read access now, try script the db.

Do you have any URLs you can give me that teach how to script the database? We're talking hundreds of tables and hundreds of stored procedures.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-20 : 16:11:39

You can do that in Enterprise Manager and SSMS.

In SSMS, rightclick the database and choose Tasks -> Scripts

E 12°55'05.25"
N 56°04'39.16"

fsilber
Starting Member

11 Posts

Posted - 2008-02-20 : 18:40:08

I followed some DTS directions I saw elsewhere that let me copy the datatables, but it didn't get all the stored procedures. (Actually, I didn't think I asked for any stored procedures in that DTS process, so I'm not sure why I got any of them.) Because I had an old (no longer used) stored procedure that referenced a table that no longer existed, I had to uncheck the "get dependent objects" switch. So now I must pick up the rest of the stored procedures.

quote:
Originally posted by Peso

You can do that in Enterprise Manager and SSMS.

In SSMS, rightclick the database and choose Tasks -> Scripts

E 12°55'05.25"
N 56°04'39.16"

SSMS is new in SQL Server 2005; since we're using SS2000 it's not available.

Unfortunately, Enterprise Manager won't let me look in an Emergency Mode database. Is there an approved solution to this?

In desperation, I saved copies of my two database files after getting in emergency mode so I could minimize risk while playing around in EM. After bringing up the server with the database in Emergency Mode, I have found that if I:

change the database status in Master.sysdatabases to 0 (without restarting the server, which would make the database SUSPECT again), and then

refresh Enterprise Manager

I can then see the database objects in EM (but not select data). I was successful in scripting a few stored procedures that way, but a curious thing happened in that the database began dropping objects.

I worried that maybe someone was trying to use the database while it's mode was reset, so I disabled a web application that referenced the DB, brought the server down and replaced the two files I had saved before I began this experiment, and was able to see all the stored procedures again. Is this the approved hack for forcing EM to let me generate scripts from a database brought up in EMERGENCY MODE? Or is there some other way?

I know I can script stored procedures in Query Analyzer, but it only lets me get them one at a time, and this database has hundreds of them. I read that it's possible in EM to generate a single script that gets all the stored procedures at once -- is that true for the 2000 version of SQL Server?

If so, maybe I can try that hack again and then generate a single script for all stored procedures before weird things start happening again.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-02-20 : 22:34:03

Yes, don't let anyone else to access the db and you can script whole db in em.

fsilber
Starting Member

11 Posts

Posted - 2008-02-21 : 10:51:50

quote:
Originally posted by rmiao

Yes, don't let anyone else to access the db and you can script whole db in em.

OK, I have a new database that looks good. I'd like to rename the EMERGENCY MODE one and keep it around, just in case an object fell through the cracks. So I'd like to rename the EMERGENCY MODE database and change the new database to have the old name.

But when I called sp_renamedb, I got this error message:

Server: Msg 902, Level 16, State 1, Line 1
To change the NAME, the database must be in state in which a checkpoint can be executed.

If I take it out of emergency mode and restart, it will again be SUSPECT; won't I have the same problem? If I take it out of emergency mode and then try to rename it, will it work or will I risk something really horrible?

Or should I create a new database with the desired name, stop the server, copy the EMERGENCY MODE files to the new name (replacing the old ones), restart to find two copies of the EMERGENCY MODE database, and then drop the copy under the old name?

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-02-21 : 15:57:32

Not much you can do for that db, just give new db a new name.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources