SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


restoring corrupted Database due to hard disk crash


restoring corrupted Database due to hard disk crash

Author
Message
mlsrinivas
mlsrinivas
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 15
Hi,

We had system crash because of hard disk failure. I have my database (Sql Server 2000) on that system. Unfortunately we had not taken back up of the database for the last 3 months.

Now we got the data from that hard drive recovered, but I could not attach the database in enterprise manager.

I tried various tools and options but of no use. I am giving below the process I followed which had lead close to recovery (I could see that data but huge number of records missing). I am not sure what I did wrong in this process, please help me to correct this.

Version: Sql Server 2000
- Created a database (mdf and ldf) with the same name of the main database (I am not sure if I need create the mdf with the same size)
- Stopped Sql Server service
- Replaced the newly created mdf file with the corrupted one.
- Retaining/replacing the ldf file (I tried both).
- Started Sql Server
- Database is shown in 'Suspect mode' in enterprise manager
- executed the following sequence of commands in Query Analyzer

use master
go
sp_configure "allow", 1
go
reconfigure with override
go

--Set the database to emergency mode
update sysdatabases set status = 32768 where name = 'TransAidDB'
go
select Name, Status from Sysdatabases where name = 'TransAidDB'
-- Got the status of database as mergency mode (32768)

exec sp_dboption 'TransAidDB','Single User', 'true'
or
Alter Database TransAidDB SET single_user

DBCC CHECKDB('TransAidDB', REPAIR_ALLOW_DATA_LOSS)
(It took time and displayed output with list of tables and number of records in that. I could execute this step only once, all other trials miserably failed with different error messages )

Alter Database TransAidDB SET online

exec sp_dboption 'TransAidDB','Single User', 'false'

use
TransAidDB
Go
select * from doctordetails


NOTE: This worked only once, most of the other times I got the error 'database is not in single user mode' even though I executed that command succesfully.

Please guide to recover this data.

Thanks a ton for your time.

Srinivas
GilaMonster
GilaMonster
SSC Guru
SSC Guru (154K reputation)SSC Guru (154K reputation)SSC Guru (154K reputation)SSC Guru (154K reputation)SSC Guru (154K reputation)SSC Guru (154K reputation)SSC Guru (154K reputation)SSC Guru (154K reputation)

Group: General Forum Members
Points: 154207 Visits: 45898
mlsrinivas (12/22/2010)
I tried various tools and options but of no use. I am giving below the process I followed which had lead close to recovery (I could see that data but huge number of records missing). I am not sure what I did wrong in this process, please help me to correct this.


You ran CheckDB with REPAIR_ALLOW_DATA_LOSS and you're surprised that there's data missing afterwards?

If the repair deallocated data and you have no good backup, that data is gone and not recoverable. It was likely gone and unrecoverable before you ran the repair because of the corruption.

The way you recover from severe corruption without data loss is by restoring a backup. No backup - not many options left.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


mlsrinivas
mlsrinivas
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 15
I tried with DBCC CHECKDB('TransAidDB', REPAIR_REBUILD), but during this trial I am getting an error that database is to be opened in single user access, which I already did using with both of these options.

exec sp_dboption 'TransAidDB','Single User', 'true'

Alter Database TransAidDB SET single_user

I am not able to proceed beyond this step.

Thanks

Srinivas
Marco V
Marco V
Say Hey Kid
Say Hey Kid (712 reputation)Say Hey Kid (712 reputation)Say Hey Kid (712 reputation)Say Hey Kid (712 reputation)Say Hey Kid (712 reputation)Say Hey Kid (712 reputation)Say Hey Kid (712 reputation)Say Hey Kid (712 reputation)

Group: General Forum Members
Points: 712 Visits: 569
mlsrinivas (12/22/2010)
I tried with DBCC CHECKDB('TransAidDB', REPAIR_REBUILD), but during this trial I am getting an error that database is to be opened in single user access, which I already did using with both of these options.

exec sp_dboption 'TransAidDB','Single User', 'true'

Alter Database TransAidDB SET single_user

I am not able to proceed beyond this step.

Thanks

Srinivas


Not sure if its in single user mode or not but after the allow data loss repair i dont know what you want to accomplish wioth running another checkdb repair_rebuild. Afaik you already passed point no return. either accept the dataloss or go back to a backup
GilaMonster
GilaMonster
SSC Guru
SSC Guru (154K reputation)SSC Guru (154K reputation)SSC Guru (154K reputation)SSC Guru (154K reputation)SSC Guru (154K reputation)SSC Guru (154K reputation)SSC Guru (154K reputation)SSC Guru (154K reputation)

Group: General Forum Members
Points: 154207 Visits: 45898
mlsrinivas (12/22/2010)
I tried with DBCC CHECKDB('TransAidDB', REPAIR_REBUILD), but during this trial I am getting an error that database is to be opened in single user access, which I already did using with both of these options.


In emergency mode the only repair that's available is repair allow data loss, which will discard damaged data.

At this point I'd say be happy that you got anything back, consider this a harsh lesson and fix up your maintenance (integrity checks and backups) going forward.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


mlsrinivas
mlsrinivas
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 15
I want to re-run the process with your guidance with the mdf and ldf files I got from the data recovery team. I still have the copy of the mdf and ldf files (corrupted) that were recovered from the damaged hard disk.

I learnt the lesson very hard way. Actually now the system is running on the back up data only, that is about 3 months old. I also looking for an option if there is any to update the old database with the new transactions from this damaged ldf file.

I am open any option, I don't have any other optionSad.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (154K reputation)SSC Guru (154K reputation)SSC Guru (154K reputation)SSC Guru (154K reputation)SSC Guru (154K reputation)SSC Guru (154K reputation)SSC Guru (154K reputation)SSC Guru (154K reputation)

Group: General Forum Members
Points: 154207 Visits: 45898
mlsrinivas (12/22/2010)
I want to re-run the process with your guidance with the mdf and ldf files I got from the data recovery team. I still have the copy of the mdf and ldf files (corrupted) that were recovered from the damaged hard disk.


Honestly, I wouldn't bother. What you did is the last resort for recovering damaged databases, if it required that, there is no fixing without the data loss.

Actually now the system is running on the back up data only, that is about 3 months old. I also looking for an option if there is any to update the old database with the new transactions from this damaged ldf file.


There are log reader tools you can buy, they're around $1000 per licence. Whether they can recover from just a log file that's not attached, I don't know. Pull one of the demos down and see.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


guthey.aditya
guthey.aditya
SSC Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
Points: 30 Visits: 269
I would try a third party tool
Greg Milner
Greg Milner
Old Hand
Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)

Group: General Forum Members
Points: 369 Visits: 494
Hi,

I had the same trouble a few years ago and Recovery for SQL Server helped me.

If you don't mind spending a little money (it wasn't much) it's worth it.

http://www.officerecovery.com/mssql/

Try out the trial for a preview of how it will work.

Good luck!



G. Milner
Greg Milner
Greg Milner
Old Hand
Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)

Group: General Forum Members
Points: 369 Visits: 494
Ouch! Recovery for SQL Server has gone up in price. It used to be only $159.

Sorry about that. Still, if you need it, you need it.



G. Milner
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search