Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

restoring corrupted Database due to hard disk crash Expand / Collapse
Author
Message
Posted Wednesday, December 22, 2010 12:16 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 04, 2012 8:37 AM
Points: 4, 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
Post #1038086
Posted Wednesday, December 22, 2010 1:07 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 7:46 AM
Points: 41,528, Visits: 34,444
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 2008, MVP
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

Post #1038098
Posted Wednesday, December 22, 2010 1:47 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 04, 2012 8:37 AM
Points: 4, 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
Post #1038112
Posted Wednesday, December 22, 2010 1:57 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, March 27, 2014 2:51 AM
Points: 462, Visits: 517
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

Post #1038113
Posted Wednesday, December 22, 2010 2:00 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 7:46 AM
Points: 41,528, Visits: 34,444
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 2008, MVP
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

Post #1038114
Posted Wednesday, December 22, 2010 2:37 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 04, 2012 8:37 AM
Points: 4, 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 option.
Post #1038126
Posted Wednesday, December 22, 2010 7:14 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 7:46 AM
Points: 41,528, Visits: 34,444
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 2008, MVP
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

Post #1038257
Posted Thursday, January 06, 2011 12:08 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, February 07, 2014 2:04 PM
Points: 14, Visits: 257
I would try a third party tool
Post #1043960
Posted Thursday, January 06, 2011 11:00 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, April 01, 2014 9:00 PM
Points: 114, Visits: 447
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
Post #1044149
Posted Thursday, January 06, 2011 11:07 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, April 01, 2014 9:00 PM
Points: 114, Visits: 447
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
Post #1044151
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse