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.

We will then detach the database to free the file locks held by the SQL Server service.

USE master
GO
CREATE DATABASE IsItSafe
GO
USE IsItSafe
GO
CREATE TABLE SomeNames(i BIGINT IDENTITY PRIMARY KEY CLUSTERED, PersonName CHAR(100), SPACEWASTER1 NCHAR(400) DEFAULT(REPLICATE(N'N',400)))
INSERT INTO dbo.SomeNames (PersonName) VALUES ('Martha	Ramirez'), ('Eric	Edwards'), ('Lillian	Long'), ('Jonathan	Washington'), ('Jessica	Harris')
, ('Jeremy	Howard'), ('Harold	Taylor'), ('Brian	Parker'), ('Albert	Foster'), ('Doris	Sanchez'), ('Walter	Diaz'), ('Ryan	Bryant'), ('Ralph	Powell')
, ('James	Turner'), ('Denise	Rodriguez'), ('Cheryl	Smith'), ('Sean	Jackson'), ('Peter	Brown'), ('Benjamin	Robinson'), ('Judy	Watson'), ('Harry	Wilson')
, ('Tina	Phillips'), ('Tammy	Butler'), ('Robert	Thompson'), ('Charles	Bell'), ('Gerald	Anderson'), ('Edward	Gonzalez'), ('Amanda	Allen')
, ('Gregory	Hall'), ('Christopher	Adams'), ('Rebecca	Sanders'), ('Diane	Stewart'), ('David	Clark'), ('Keith	Gray'), ('Helen	Williams'), ('Joan	Lee')
, ('Jeffrey	Brooks'), ('Sarah	King'), ('Steven	Nelson'), ('Paul	Cooper'), ('Martin	Collins'), ('Scott	Carter'), ('Marie	Martin'), ('Jacqueline	Ross')
, ('Jose	Perez'), ('Johnny	Cook'), ('Paula	Kelly'), ('Mary	Murphy'), ('Carolyn	Young'), ('Kathryn	Torres')

GO
USE master
GO
exec sp_detach_db 'IsItSafe'

Step 3. For our purposes I’m going to use a hex editor to mess with the file. There are other, faster methods to corrupt a database, but this one works for the illustrations I want to show. I am using a freeware hex editor tool called HxD to do my changes, but any hex editor should be fine.

Open IsItSafe.mdf in the hex editor program and find your way down to the name Eric Edwards.  It will probably be about half way down.  HxD has a find function which will quickly jump to this section.

Overwrite the name Edwards with Johnson. It is important to change the value of each letter without changing the length of the file. While that would also be corruption, it would not allow the rest of the demos to work as designed.


Save the file and close the hex editor.

Step 4. Reattach the database.  I’m doing this on my laptop so the files are on C.  Adjust this script to match your file locations.

CREATE DATABASE [IsItSafe] ON 
  ( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\IsItSafe.mdf' )
, ( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\IsItSafe_log.ldf' ) 
FOR ATTACH 
GO

You now have a database that has been corrupted. Please return to the source post so that you can see how to detect/correct this fault.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.