My database in SQL Server 2008 went into suspect mode, and I fixed it by changing to emergency mode then I used this query


And now my data letters in my tables changed from Arabic to question marks, I want to recover the encoding without dropping the tables or losing the data

2 Answers 2


You have already damaged the database by running REPAIR_ALLOW_DATA_LOSS. You need a professional to asses the loss in your database and see what is recoverable, and how. Contact product support.

Next time know that the most appropriate action is to restore now from a recent non-damaged backup.

  • I have a backup of the database, how do I re-attach the database without error 9004 and without data loss
    – ahmed mido
    Commented Aug 28, 2012 at 12:14
  • If you get error 9004 when you restore (not attach!) the backup then it means your backup is corrupted too. Commented Aug 28, 2012 at 12:15
  • +1 REPAIR_ALLOW_DATA_LOSS is a last resort.
    – Jon Seigel
    Commented Aug 29, 2012 at 17:10

Added to that, you need to change a couple of things in your maintenance strategy.

It's so important to check regulary that your database is in a correct state and that it doesn't contain any logical or physical inconsistencies.

Running DBCC CHECKDB on a regular basis is a must. Early detection is vital because corruption never just happens, you've got bigger underlying issues, so detecting it in a early stage might prevent you from a disaster. Secondly, detecting early helps avoiding the corruption being backupped. Leaving you with nothing to restore.

From SQL 2005 onwards Page verify Checksum was added. Databases created in sql 2005 and higher default to this setting. Databases created earlier and that were upgraded might still have the old verification strategy "Thorn page detection"set. Double check that.

Checksum gives you the option to detect corruption in any part of the page even inrow modifications AND as a big bonus, when you add the option WITH CHECKSUM to your backup statement, you're pages will be checksum tested while running the backup. Making sure that you don't have a corrupt database in your backup.

so a couple of things here:

  1. Check you current hardware, storage system for errors. The corruption didn't just happen. You still have to address the root cause.
  2. Run checkdb on all other databases that are stored on the same system

For the future:

  1. Check all your databases that they have page_verify checksum enabled. change acordingly : ALTER DATABASE DBname SET PAGE_VERIFY CHECKSUM;
  2. Start running DBCC CHECKDB on a regular basis. Daily if at all possible. If you run into performance issues, consider to still run daily with the option PHYSICAL_ONLY
  3. Add WITH CHECKSUM to all your backup commands to ensure you know that you only have clean backups.

Hopefully this will prevent you from future troubles. I wish you the best of luck with getting your current DB up and running again.

  • 1
    Changing the page verification database option only gets applied to pages that have been subsequently modified and written back to disk. Existing pages are not automatically changed to use the new setting. I completely agree with turning on page checksums; it's important to be aware of this behaviour on an upgraded database because it could potentially allow problems in that you might not expect based on the current setting.
    – Jon Seigel
    Commented Aug 29, 2012 at 17:09
  • That's so true, great addition Commented Aug 29, 2012 at 18:49

Your Answer

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