Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am working on a sql database and it got corrupted due to power failure while I am doing some data entry on the system.

I set my database to emergency mode so that I could get some data out of it before dumping the database but I keep getting the error:

Could not continue with NOLOCK due to data movement

I set the database to offline mode and now when i try to turn it online.It is giving me this error

Set offline failed for Database 'xyz'.

The log scan number (1224:41:1) passed to log scan in database 'xyz' is not valid. This error may indicate data corruption or that the log file (.ldf) does not match the data file (.mdf). If this error occurred during replication, re-create the publication. Otherwise, restore from backup if the problem results in a failure during startup. An error occurred during recovery, preventing the database 'xyz' (database ID 5) from restarting. Diagnose the recovery errors and fix them, or restore from a known good backup. If errors are not corrected or expected, contact Technical Support. Database 'xyz' cannot be opened. It has been marked SUSPECT by recovery. See the SQL Server errorlog for more information. ALTER DATABASE statement failed. (Microsoft SQL Server, Error: 9003)

share|improve this question
2  
Tried running DBCC CHECKDB(database_name)? Are you able to backup and restore the database? –  VDMT Dec 28 '11 at 22:36
    
Are you getting this error when trying to extract the data or is this showing up in the log? If during extraction, what method are you using and can you post the SQL it uses to extract? –  Jason Cumberland Dec 29 '11 at 16:48
    
I just execute the select statement in descending order and try to get top 1000 rows –  MSingh Dec 29 '11 at 18:41
add comment

2 Answers 2

The database is corrupt. Setting it to offline sealed your fate.

If it's mission-critical data that you need to recover, contact Microsoft Support. It's going to cost $500, but it's worth a shot if you need that data.

Otherwise, restore from backup.

share|improve this answer
add comment
up vote -3 down vote accepted

I change the mode of the database to emergency, then restore it with 'RESTORE_WITH_DATA_LOSS', it works only very very few records has been lost, moreover rather than contacting Microsoft guys for that, i've tried another tool like Phoenix data recovery, they also worked and costs only $129.

share|improve this answer
2  
This answer should not be accepted as it is bad general advice. Let me summarize what you said: "We lost data." And that is, for most people, not what they should allow to happen. I suggest you accept Brent's answer. –  usr Jul 11 '12 at 16:03
    
@usr Thanks for your concern. I am agree with you that i shoudl accept Brent's answer but Cost is a very big factor for some of our clients. This database belongs to their offline system and they are more than happy to re-enter little data than pay $500. Thanks anyways –  MSingh Jul 11 '12 at 16:33
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.