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 only takes a minute to sign up.
Sign up to join this community
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
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.
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:
For the future:
ALTER DATABASE DBname SET PAGE_VERIFY CHECKSUM;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_ONLYWITH 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.
