December 22, 2010 at 12:16 am
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
December 22, 2010 at 1:07 am
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
December 22, 2010 at 1:47 am
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
December 22, 2010 at 1:57 am
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
December 22, 2010 at 2:00 am
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
December 22, 2010 at 2:37 am
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:(.
December 22, 2010 at 7:14 am
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
January 6, 2011 at 12:08 pm
I would try a third party tool
January 6, 2011 at 11:00 pm
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
January 6, 2011 at 11:07 pm
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
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply