June 17, 2010 at 8:11 am
I have just taken over responsiblity of several new database projects and the first task given was to recover some SS 2000 databases that are corrupted. THere are no backups (which is another story all together)....I only have the mdf and ldf.
I have attempted the following without success:
1) Tried to attach the mdf - received Error 9004: An error occurred while processing the log for database
2) Tried using SP_ATTACH_SINGLE_FILE_DB to have the log file rebuilt but was not successfull either.
Any other suggestions for recovering the databases if I don't have the full backup?
Dave Coats
June 17, 2010 at 9:49 am
Mentioned in the other other posting you added to - if no SQL backups check for a software backup of the file system or a Windows Shadow copy backup. If there is you could attach the mdf.
June 17, 2010 at 10:01 am
Yes...that's how we actually got the mdf and ldf. The server went down and they were able to retrieve the files from the server backups, we just didn't have any database backups. So then I proceeded to stand up a new instance of SQL Server 2000 and attach the databases. However, I have not been able to attach or recover them due to corruption.
Dave Coats
June 17, 2010 at 11:22 am
You may be able to hack the DB back into the server. See this blog post. It's for 2005/2008, but the procedure's the same on 2000 up until the point where you set the DB to emergency.
See if you can follow it and get the DB back into the server. If you can, post back and I'll help you from there.
http://sqlinthewild.co.za/index.php/2009/06/09/deleting-the-transaction-log/
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
June 17, 2010 at 1:57 pm
Ok...I think I'm not so lucky. I've followed your blog post and have gotten the database into an Emergency State on the server. When I try to view the tables, views, etc under Ent Mgr it shows no items.
I then go to Query Analyzer object browser and try to view tables from there and receive this error:
"I/O error (bad page ID) detected during read at offset"
From internet research, it appears to be a bad corruption.
Any ideas? Thanks for your help so far.
Dave Coats
June 17, 2010 at 2:00 pm
What did you do to get it into emergency mode? the ALTER DATABASE described on the blog is only for SQL 2005+, it won't work on SQL 2000.
Basically, what are the precise steps that you've done up until now?
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
June 18, 2010 at 5:51 am
I was not able to attach the database at all. So I created a blank database with the same name, stopped the service, replaced the blank mdf with my corrupted mdf, restarted the service and it came up in "Suspect\Emergency". Then I updated the sysdatabases status field in master to be 32768. Restarted the service and it was then in "Emergency" mode.
At this point I can run select * from sysobjects and it returns 32 objects and then gives this message:
"Server: msg 823, Level 24, State 2, Line 1
I/O error (bad page ID) detected during read at offset..."
Should I continue with "Step 2: Emergency Mode Repair" from your blog? Thanks again for your help!!
Dave Coats
June 18, 2010 at 6:12 am
Before you try a repair, let's see how bad it is.
DBCC CHECKDB (<Database Name>) WITH NO_INFOMSGS, ALL_ERRORMSGS
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
June 18, 2010 at 6:36 am
Returns following message....guess its bad then????
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (1:219) with latch type SH. sysobjects failed.
Dave Coats
June 18, 2010 at 7:17 am
DBCC CHECKDB REPAIR_ALLOW_DATA_LOSS
June 18, 2010 at 7:20 am
Not repairable. Sorry, DB's a loss.
You may be able to extract some data with it in emergency mode. Some objects will likely fail to script, some will fail to export. It's the best that you're going to be able to do 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
June 18, 2010 at 7:23 am
sparky-407434 (6/18/2010)
DBCC CHECKDB REPAIR_ALLOW_DATA_LOSS
No point. If checkDB can't complete a check (in this case because the system tables are damaged) there's no way it's going to be able to repair it.
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
June 18, 2010 at 7:29 am
Gail,
Ok...I was afraid that was the case. Thanks for your help with this...I really appreciate it. I have 3 other dbs that I need to attempt to recover, if I can get any of them to do a CHECKDB, I'll repost. Thanks again!
Dave Coats
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply