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.
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:
- Check you current hardware, storage system for errors. The corruption didn't just happen. You still have to address the root cause.
- Run checkdb on all other databases that are stored on the same system
For the future:
- Check all your databases that they have page_verify checksum enabled. change acordingly :
ALTER DATABASE DBname SET PAGE_VERIFY CHECKSUM;
- 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
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.