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.
1 Comment