I was asked how I caused the corruption in Database Corruption Challenge #9. Here is how I did it.
To cause the corruption, I used the undocumented DBCC WritePage, however the same thing could have been accomplished by detaching the database, opening it with a hex editor, and then re-attaching the db.
DBCC WritePage
WARNING: DBCC WritePage is a dangerous command, that should never be used on any production database. It may invalidate your ability to get support from Microsoft on issues that arise with that database going forward. It is not my intention to encourage anyone to use DBCC WritePage ever. This is just what I used to create a corrupt database, and since creating corrupt databases is not part of the role of most DBAs, you should not use DBCC WritePage. Consider yourself warned.
First I used DBCC IND to find the pages that were part of the index, then I used those page (189, 226, 227, and 230) with DBCC WritePage to stomp on some memory to simulate corruption.
-- Find the pages to corruption in the non-clustered index on [Record1100].
DBCC TRACEON(3604) with no_infomsgs;
DBCC IND(CorruptionChallenge9,'[OPEN_NFIRS].[Record1100]', 2) WITH NO_INFOMSGS;
-- Index #2 is the nonClustered index
-- DBCC TraceOn(2588) - Will enable DBCC HELP for undocumented DBCC commands
DBCC TRACEON(2588);
DBCC HELP('WritePage');
DBCC Page(CorruptionChallenge9, 3, 189, 2) WITH NO_INFOMSGS;
--dbcc writepage ({'dbname' | dbid}, fileid, pageid, offset, length, data [, directORbufferpool])
DBCC WritePage(CorruptionChallenge9, 3, 189, 216, 4, 0x00000000);
DBCC WritePage(CorruptionChallenge9, 3, 226, 216, 4, 0x00000000);
DBCC WritePage(CorruptionChallenge9, 3, 227, 216, 4, 0x00000000);
DBCC WritePage(CorruptionChallenge9, 3, 230, 216, 4, 0x00000000);
DBCC CheckDB(CorruptionChallenge9) WITH NO_INFOMSGS;
Then something similar for [Record1000] clustered index.
-- corrupt the clustered index on [Record1000].
DBCC TRACEON(3604) with no_infomsgs;
DBCC IND(CorruptionChallenge9,'[OPEN_NFIRS].[Record1000]', 0) with no_infomsgs;
--000000000C9FC000: 01010400 00820001 3a000000 03000c00 †........:.......
DBCC Page(CorruptionChallenge9, 3, 22, 2) WITH NO_INFOMSGS;
--dbcc writepage ({'dbname' | dbid}, fileid, pageid, offset, length, data [, directORbufferpool])
DBCC WritePage(CorruptionChallenge9, 3, 22, 0, 1, 0x10);
DBCC CheckTable('[OPEN_NFIRS].[Record1000]')
DBCC CheckDB(CorruptionChallenge9) WITH NO_INFOMSGS;
And again something similar for [Record1005].
-- corrupt the clustered index on [Record1005].
DBCC TRACEON(3604) with no_infomsgs;
DBCC IND(CorruptionChallenge9,'[OPEN_NFIRS].[Record1005]', 0) with no_infomsgs;
DBCC Page(CorruptionChallenge9, 3, 88, 2) WITH NO_INFOMSGS;
--dbcc writepage ({'dbname' | dbid}, fileid, pageid, offset, length, data [, directORbufferpool])
DBCC WritePage(CorruptionChallenge9, 3, 88, 1, 2, 0x0303);
This was all done on SQL Server 2005 so that people using the corruption challenge could restore on SQL Server 2005 or newer. When you restore to a newer version of SQL Server, the database gets upgraded, and some of the page numbers may be different than what I used to create it.
Once the corruption was introduced, I then created a full database backup and tested it on other (newer) version of SQL Server to confirm that it would work for everyone.
-Steve Stedman
More from Stedman Solutions:
Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!