Corrupting a Database For Fun

When you’re not expecting it, database corruption is bad for your database server (and probably your own personal health). Database corruption can be fun though when you’re the one doing the corrupting. Let’s take a look at corrupting a database and see what we can learn.

It’s Just One of Those Days

First, let’s create a test database and test table:

CREATE DATABASE Ohio;
GO

USE Ohio;
GO

CREATE TABLE SportsTeam (
	Team_ID INT Identity(1, 1)
	,Team_Name VARCHAR(100) NOT NULL
	,Team_City VARCHAR(100) NOT NULL
	);
GO

And we’ll enter a handful of rows for test data:

INSERT INTO SportsTeam
VALUES ('Cavaliers','Cleveland')
,('Blue Jackets','Columbus')
,('Browns','Cleveland')
,('Muskies','New Concord')
,('Bobcats','Athens')

For our example, we’re going to add two indexes. One index will be a clustered index that we’ll leave alone and the other will be a non-clustered index that we’re going to corrupt:

CREATE CLUSTERED INDEX SportsTeam_Team_ID ON SportsTeam (Team_ID);
GO

CREATE INDEX SportsTeam_Team_Name ON SportsTeam (Team_Name);
GO

To knowingly corrupt our non-clustered index, we’re going to need to track down some more information. We’ll use DBCC IND to pass in our database name, table name, and ‘2’ for our non-clustered index.

DBCC IND ('Ohio', 'SportsTeam', 2);

To know where we’ll need to go in the MDF file, let’s take note of the PagePID of the index page which is where PageType is 2. In this case, that will be 360.

Getting Your Hands Dirty

To corrupt the database, we’re going to use a hex editor to edit the database MDF file. If we Google hex editor, HxD is one of the first to come up. We’ll use that to corrupt our database but if you have another preferred hex editor then feel free to go with that.

Let’s take our Ohio database offline so that we can access it with our hex editor.

USE master;
GO

ALTER DATABASE Ohio SET OFFLINE;
GO

Let’s open up HxD and click File > Open. Navigate to the location of our data files and open our Ohio.mdf.

We can now take our PagePID of 360 and multiply it by 8192 which is the number of bytes per page. This will take us to where we want to corrupt our index.

We’ll click Search > Go to. We’ll then search for the dec value of 2949120 (360 * 8192) and we’ll see some familiar text.

We’ll make a small change. Instead of Blue Jackets, what about…Teal Jackets? We can click into the decoded text and type our change:

After making that change, we’ll save our file and we can exit out of our hex editor.

Verifying Corruption

Back in SQL Management Studio, we’ll bring our database back online.

ALTER DATABASE Ohio SET ONLINE;
GO

USE Ohio;
GO

The database successfully came back online. No problems yet. We can even run this statement:

SELECT * FROM SportsTeam;
GO

And not see any problems because the clustered index is used. But what if we run a statement that uses the index that now has Teal Jackets?

SELECT Team_Name FROM SportsTeam;
GO

Now we see our problem. Running DBCC CHECKDB now will also tell us about the problem.

DBCC CHECKDB('Ohio');

Let’s do some testing with the SportsTeam_Team_Name index? What if we disable the index?

ALTER INDEX SportsTeam_Team_Name ON SportsTeam
DISABLE;

If we run the SELECT statement that was breaking earlier, does it work now?

It does because it’s using the clustered index. What if we run DBCC CHECKDB again?

That works as well. Finally, let’s go ahead and rebuild that disabled index:

ALTER INDEX SportsTeam_Team_Name ON SportsTeam
REBUILD; 

If we run our query that was failing earlier:

That now completes using the SportsTeam_Team_Name index and CHECKDB will find no issues.

NOTE: It’s fun messing around with a hex editor to see what happens to the database but make sure you never do this type of experiment on a production server. Not a good idea even on a non-prod server, especially if you start messing with system databases.

On a machine where you don’t mind reinstalling SQL Server if it goes beyond repair? Go for it!

Thanks for reading!