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)

  • 2
    Tried running DBCC CHECKDB(database_name)? Are you able to backup and restore the database?
    – VDMT
    Commented Dec 28, 2011 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? Commented Dec 29, 2011 at 16:48
  • I just execute the select statement in descending order and try to get top 1000 rows
    – MSingh
    Commented Dec 29, 2011 at 18:41

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.


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.

  • 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
    Commented Jul 11, 2012 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
    Commented Jul 11, 2012 at 16:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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