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.

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

DBCC CHECKDB (Test, REPAIR_ALLOW_DATA_LOSS);

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

share|improve this question
add comment

migrated from stackoverflow.com Aug 28 '12 at 19:46

This question came from our site for professional and enthusiast programmers.

2 Answers

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.

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

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.

share|improve this answer
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 Aug 29 '12 at 17:09
    
That's so true, great addition –  Edward Dortland Aug 29 '12 at 18:49
add comment

Your Answer

 
discard

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