Eric Blinn has started a new series on database corruption. In part one, he shows us how to corrupt a database (probably by letting it listen to Alice Cooper and Frank Zappa):
I’m going to start a series of posts regarding corruption detection, prevention, and correction. In order to detect corruption we must first have a corrupt database. This post is going to show how to purposely corrupt a database. I’ll refer back to this post often as it will be the basis of most of the future posts in this series which will pick up where this leaves off.
Step 1. Create a new database. You didn’t think I was going to ask you to corrupt a real database did you? We will create one table with some names in it. The table is abnormally wide on purpose. The goal is to get fewer rows on any one page of data so that we can get several pages of data without needing many hundreds of rows.
Part two explains the concept of page verification:
Page verification is a process in SQL Server where the engine writes extra data to the header of the page while writing it to disk. This extra data can be used to verify that the data later read from that page is what was expected. There are 3 options for the setting. They are NONE, CHECKSUM, and TORN_PAGE_DETECTION. The torn page option is deprecated. It should not be used and will not be covered in this series.
When set to CHECKSUM SQL Server will quickly determine a checksum for the page of data and write it to the header of the data page. Any time from that point forward when the page is read from disk SQL Server will perform the same checksum calculation and compare that to to the stored value in the header of the page. If the value matches that would indicate there is probably not corruption. If the values do not match that would indicate there is almost certainly some kind of corruption.
Corruption is one of the scariest things that can happen to a database; knowing how to respond to an incident of corruption is critical even as the incidence of corruption is quite low in practice (unless you’re running on awful hardware).