SQL Server Corrupt Index

[p***@gmail.com]

We had a corrupted index last night, I'm wondering if there is appropriate regular maintenance that we should do to prevent this again? I saw something about rebuilding indexes on a regular schedule? Is this true?

What could cause a corrupted index?
Thanks!!

[Erland Sommarskog]

If it is only a non-clustered index, you and address this by rebuilding the index. If it is a clustered index, you should restore from a clean backup.

Corruption is almost always caused by bad hardware. That is, errors in the I/O subsystem or bad memory sticks. (And of these the I/O subsystem is much more often at fault.)

Sometimes you can correlate the corruption to a certain event like a power failure. But if you cannot, you should consider the hardware suspect and run tests. Or simply move to new hardware. If it was only a non-clustered index, you can count yourself lucky, as it simple to recover from without data loss. But next time, disaster may strike right on the data pages in your most important table.

Having a good backup scheme with nightly full backup + regular transaction log backups during the day is a good safety measure. Also make sure to test the backups by retoring them to a server and run DBCC on them. This is not the least important when you seem to have bad hardware.

Finally, make sure that you're databases are set to PAGE_VERIFY CHECKSUM.

[d***@gmail.com]

Restore With Transact-SQL

-Click "New Query" in the Management Studio's main toolbar. This opens a large text area on the right side of the screen.

-Click in the text area and type a Create Database statement using the following Transact-SQL code as a guide:
CREATE DATABASE MyDatabase ON (FILENAME = 'c:\data files\my_data.mdf'), (FILENAME = ' c:\data files\my_data.ldf') FOR ATTACH;

-Click the "Execute" button in the Transact-SQL toolbar, located just under the Management Studio's main toolbar. The Execute button symbol is a right-pointing triangle. SQL Server Management Studio restores the database.

Helpful resources to explain another methods of restoration and gave more variants of sql data recovery...

https://social.technet.microsoft.com/Forums/en-US/cc4cfcf8-93ad-4d8e-98db-9f241543da89/my-database-mdf-file-got-corrupted?forum=ssdsgetstarted
http://www.filerepairforum.com/forum/microsoft/microsoft-aa/sql-server/1492-sql-database-corruption-on-sql-2014
https://www.repairtoolbox.com/sqlserverrepair.html SQL Server Repair Toolbox