May 13, 2010 at 3:26 am
I asked about the upgrade message. Is the SQL instance that you're trying to recover this with IDENTICAL to the version that this DB was attached to before the SAN failure?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 13, 2010 at 3:28 am
on the same server we have 2 instances of the sql server. not to muck up the production file (which is in suspect mode) i copied the mdf and ldf file to the test instance and working from there. they both are on the same server and same version
Microsoft SQL Server 2005 - 9.00.3042.00 (X64) Feb 10 2007 00:59:02 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)
Microsoft SQL Server 2005 - 9.00.3042.00 (X64) Feb 10 2007 00:59:02 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)
May 13, 2010 at 3:52 am
yes they are identical
May 13, 2010 at 5:16 am
Ok, so the DB is attached but suspect. Please run the following.
ALTER DATABASE <database name> SET EMERGENCY
Once you've done that, you should be able to access the DB, though it will be restricted to single user. Please confirm once you've run that, that you can access the database and you can run a simple query (just query one of the system views, like sys.objects)
If you get any errors, post the complete messages here.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 13, 2010 at 9:31 am
oh my. This is just a comment from someone just watching, but Sunil, you are in a bad spot. Usually something of this magnitude cannot be fixed - it isn't recoverable and restoring a backup is the best solution. Since you don't have one you would be really stuck. But I will say that Gail is not one of your every-day, run-of-the-mill SQL users. You are in the hands of one of the best SQL surgeons I have ever seen, and if you follow Gail's instructions precisely you have the best shot of getting your database (or at least part of it) back. I hope it goes well.
Chad
May 13, 2010 at 2:05 pm
Thanks Gail
I get this error when i run set to emergency
Msg 602, Level 21, State 33, Line 1
Could not find an entry for table or index with partition ID 327680 in database 17. This error can occur if a stored procedure references a dropped table, or metadata is corrupted. Drop and re-create the stored procedure, or execute DBCC CHECKDB.
May 13, 2010 at 4:52 pm
hi Gail
Please let me know when u online
May 13, 2010 at 5:39 pm
Hi Gail
I have tried all the steps you had requested in the live system but still get the same error when trying to set mode to emergency
Msg 602, Level 21, State 33, Line 1
Could not find an entry for table or index with partition ID 327680 in database 22. This error can occur if a stored procedure references a dropped table, or metadata is corrupted. Drop and re-create the stored procedure, or execute DBCC CHECKDB.
May 13, 2010 at 9:26 pm
Hi Sunil,
I'd suggest that Gail is otherwise busy right now, so give her some time to get back to you.
While you wait, you should start banging some heads together to get backups in place. This event should scare the necessary authorities at your company into taking action on this. At the very least, you need to get backups happening onto other disks (ie: NOT the same disks/SAN as those the db files live on.) Use lots of 1.44MB floppies if you must.
Steve.
May 13, 2010 at 9:39 pm
Thanks Steve
We have been doing regular backups and have manage to get back all DB's online except for this one. Its a newly created DB (7 months) and somehow we forget to include this in the backup job. We are certainly going to tighten up on this area.
May 13, 2010 at 11:01 pm
Hi Gail
I am going out of office for now for (2 hours).
May 14, 2010 at 1:51 am
Hi Gail
I am in office now
May 14, 2010 at 2:23 am
Sunil Padarath (5/13/2010)
Thanks GailI get this error when i run set to emergency
Msg 602, Level 21, State 33, Line 1
Could not find an entry for table or index with partition ID 327680 in database 17. This error can occur if a stored procedure references a dropped table, or metadata is corrupted. Drop and re-create the stored procedure, or execute DBCC CHECKDB.
Game over.
Emergency mode is the absolute last resort. If it fails, there's nothing more that can be done. This database is toast, it's not recoverable, it's not coming back.
I hope this is not critical for your business.
There are 3rd party tools that are available that claim to be able to extract data from damaged database files. Personally, I've not had any success doing so when I tried them on databases too damaged to repair. It may be worth trying out a demo, see if anything at all can be extracted. Personally I suspect the chances are slim-to-none at best here.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 14, 2010 at 2:24 am
Fal (5/13/2010)
I'd suggest that Gail is otherwise busy right now, so give her some time to get back to you.
Sleeping actually. I'm in one of the european time zones, so those mails came in at 10pm, 1am and 1:30 am.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 14, 2010 at 2:26 am
Thank you Gail.
Viewing 15 posts - 16 through 30 (of 35 total)
You must be logged in to reply to this topic. Login to reply