It’s time to try something new! I was recently challenged to show something in database page with a hex editor for another blog post. I thought, “Hex what? Are we casting spells?”

Let’s start with, “What is a hex editor?” It’s a program that lets you view the binary of a computer file – the 0’s and 1’s that it’s made of. There are many available.

I started with my coworker Kendra Little’s (blog | twitter) blog, Corrupting Databases for Dummies- Hex Editor Edition. I downloaded the hex editor XVI32, as recommended. She gives instructions on how to install the software, create a database, create some objects, and then open the hex editor to break a page.

Note: this should not be done in, near, or even in the same state as a production database.

Beyond the basics of her blog post, here are the steps I went through to view a database page, break it, and fix it.

Breaking the Page

I issue DBCC IND to view a list of pages that belong to a table or index. The syntax for the command is:

T-SQL
1

DBCC IND ('DBName', 'TableName', <indexnumber>);
DBCC IND ('DBName', 'TableName', <indexnumber>);

I run the command:

T-SQL
1

DBCC IND ('CorruptMe', 'DeadBirdies', 2);
DBCC IND ('CorruptMe', 'DeadBirdies', 2);

Make note of a file and page number to work with. I issue a DBCC PAGE command to view page 1116. The syntax for the command is:

T-SQL
1

DBCC PAGE( {'dbname' | dbid}, filenum, pagenum [, printopt={0|1|2|3} ]);
DBCC PAGE( {'dbname' | dbid}, filenum, pagenum [, printopt={0|1|2|3} ]);

I run the command:

T-SQL
1

DBCC PAGE('CorruptMe', 1, 1116, 3);
DBCC PAGE('CorruptMe', 1, 1116, 3);

Following the steps in Kendra’s blog, I take the database offline, copy the physical file location, and figure out my page offset. I’ll need that information going forward.

The Hex Editor

Next, I open XVI32 and open the file. Well, that’s kind of fun-looking!

 

Quite frankly, I have no idea what the heck I’m looking at.

To find the page I had selected, I go to Address > Go To and enter my offset value. I’m now seeing the page on the right side. I can tell this because I can see the word “Tweetie” – the data I inserted in the table.

 

I click on the first T and type X. Apparently I’ve just changed the data in my data file. Oops! I’ve corrupted a data file!

I save the file and go back to SSMS. I bring the database online. That works without a problem. I run a query to view data in that table, which should access the nonclustered index I mangled.

T-SQL
1
2
3
4

USE CorruptMe;
GO
SELECT birdName
FROM dbo.DeadBirdies;
USE CorruptMe;
GO
SELECT birdName
FROM dbo.DeadBirdies;

I get a terrible error: “SQL Server detected a logical consistency-based I/O error: incorrect checksum”.

What happens if I run a DBCC CHECKDB? More errors!

Yes, I broke a database. On purpose.

But this is good! It gives me the opportunity to play with a hex editor, see what corruption errors look like, and fix them.

Fix them, you say? Yes, fix them!

Fixing the Corrupted File

This is an easy fix. A corrupted nonclustered index can be fixed by dropping and rebuilding.

First, I go to SSMS and expand the database, table, and indexes. I right-click the index and select Script Index As > Drop and Script Index As > Create To > New Query Editor window.

I run the query to drop the index:

T-SQL
1

DROP INDEX dbo.DeadBirdies.ncBirds;
DROP INDEX dbo.DeadBirdies.ncBirds;

When I run the query again, data is returned! This time, there is no error.

Now, I rebuild the nonclustered index using the Create statement I scripted, and I can still run the query without errors.

T-SQL
1

CREATE NONCLUSTERED INDEX ncBirds ON dbo.DeadBirdies(BirdName);
CREATE NONCLUSTERED INDEX ncBirds ON dbo.DeadBirdies(BirdName);

What I Learned

Someday, I may need to manually edit a database (or other) file. This is one of those tasks it’s nice to know I’ve done in practice before I ever need to actually do it – I know what to look for and what to do. I won’t need to panic at a critical moment.

Magical! Where is @SQLUnicorn when I need him?

Related Posts