Week 4 – Building the Corrupt Database

Posted on Posted in Corruption No Comments Tagged with , , , , , , ,
Download PDF

The following are the steps that I went through to create a more difficult Database Corruption Challenge for Week 4.
Here is how I built the database, nothing fancy, however I did add an extra FILEGROUP called [UserObjects].

CREATE DATABASE [CorruptionChallenge4]
 ON  PRIMARY
( NAME = N'CorruptionChallenge4',
  FILENAME = N'C:\SQL_DATA\CorruptionChallenge4.mdf',
  SIZE = 4288KB ,
  MAXSIZE = UNLIMITED,
  FILEGROWTH = 1024KB ),
FILEGROUP [UserObjects]  DEFAULT
( NAME = N'UserObjects',
  FILENAME = N'C:\SQL_DATA\CorruptionChallenge4_UserObjects.ndf' ,
  SIZE = 4096KB,
  MAXSIZE = UNLIMITED,
  FILEGROWTH = 1024KB )
LOG ON
( NAME = N'CorruptionChallenge4_log',
  FILENAME = N'C:\SQL_DATA\CorruptionChallenge4_log.ldf',
  SIZE = 1088KB,
  MAXSIZE = 2048GB,
  FILEGROWTH = 10%)
COLLATE SQL_Latin1_General_CP1_CS_AS;
GO

Notice, that to make things slightly more challenging, the database is a Case Sensitive Accent Sensitive database. I hope this didn’t cause too much trouble.
Next I created a couple of tables.

USE [CorruptionChallenge4]
GO
CREATE TABLE [dbo].[Customers]
(
	[id] [int] IDENTITY(1,1) NOT NULL,
	[FirstName] [varchar](30) NULL,
	[MiddleName] [varchar](30) NULL,
	[LastName] [varchar](30) NULL,
  CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED ([id] ASC)
)
ON [UserObjects];

CREATE TABLE [dbo].[Orders](
	[id] [int] IDENTITY(-2147483647,1) NOT NULL,
	[orderDate] [DATETIME] NOT NULL,
	[customerId] [int] NULL,
	[shippingType] varchar(100),
	[orderDetails] varchar(max)
  CONSTRAINT [PK_Revenue] PRIMARY KEY CLUSTERED ([id] ASC, [orderDate])
)
ON [UserObjects];

If you are wondering about why I started the int IDENTITY at -3147483647, then check out my blog post on wasting half of your IDENTITY values.
Next I turned on Change Data Capture, that I blogged about last week, perhaps the blog post was a clue of things to come.

EXECUTE sys.sp_cdc_enable_db;

EXECUTE sys.sp_cdc_enable_table
    @source_schema = N'dbo',
    @source_name = N'Customers',
    @role_name = NULL;

EXECUTE sys.sp_cdc_enable_table
    @source_schema = N'dbo',
    @source_name = N'Orders',
    @role_name = NULL;

Then I added people into the [customers] table using a Common Table Expression. Even though the CDC feature only works on 2008 and newer, this adding people script happens to be compatible with SQL Server 2005 based on how the CTE was writte;

;WITH Fnames (Name) AS
(
 SELECT 'John' UNION SELECT 'Mary' UNION SELECT 'Noah' UNION SELECT 'Liam' UNION SELECT 'Jakob' UNION SELECT 'Mason' UNION SELECT 'William' UNION SELECT 'Ethan' UNION SELECT 'Michael' UNION SELECT 'Alexander' UNION SELECT 'Sophia' UNION SELECT 'Emma' UNION SELECT 'Olivia' UNION SELECT 'Isabella' UNION SELECT 'Ava' UNION SELECT 'Mia' UNION SELECT 'Emily' UNION SELECT 'Mia' UNION SELECT 'Madison' UNION SELECT 'Chloe' UNION SELECT 'Aiden' UNION SELECT 'James' UNION SELECT 'Benjamin'
),
Mnames (Initial) AS
(
	SELECT 'A'	UNION SELECT 'B'	UNION SELECT 'C'	UNION SELECT 'D'	UNION SELECT 'E'	UNION SELECT 'F'	UNION SELECT 'G'	UNION SELECT 'H'	UNION SELECT 'I'	UNION SELECT 'J'	UNION SELECT 'K'	UNION SELECT 'L'	UNION SELECT 'M'	UNION SELECT 'N'	UNION SELECT 'O'	UNION SELECT 'P'	UNION SELECT 'Q'	UNION SELECT 'R'	UNION SELECT 'S'	UNION SELECT 'T'	UNION SELECT 'U'	UNION SELECT 'V'	UNION SELECT 'W'	UNION SELECT 'X'	UNION SELECT 'Y'	UNION SELECT 'Z'
),
Lnames (Name) AS
(
  SELECT 'SMITH'  UNION SELECT 'JOHNSON'  UNION SELECT 'WILLIAMS'  UNION SELECT 'BROWN'  UNION SELECT 'JONES'  UNION SELECT 'MILLER'  UNION SELECT 'DAVIS'  UNION SELECT 'GARCIA'  UNION SELECT 'RODRIGUEZ'  UNION SELECT 'WILSON'  UNION SELECT 'MARTINEZ'  UNION SELECT 'ANDERSON'  UNION SELECT 'TAYLOR'  UNION SELECT 'THOMAS'  UNION SELECT 'HERNANDEZ'  UNION SELECT 'MOORE'  UNION SELECT 'MARTIN'  UNION SELECT 'JACKSON'  UNION SELECT 'THOMPSON'  UNION SELECT 'WHITE'  UNION SELECT 'LOPEZ'  UNION SELECT 'LEE'  UNION SELECT 'GONZALEZ'  UNION SELECT 'HARRIS'  UNION SELECT 'CLARK'  UNION SELECT 'LEWIS'  UNION SELECT 'ROBINSON'  UNION SELECT 'WALKER'  UNION SELECT 'PEREZ'  UNION SELECT 'HALL'  UNION SELECT 'YOUNG'  UNION SELECT 'ALLEN'  UNION SELECT 'SANCHEZ'  UNION SELECT 'WRIGHT'  UNION SELECT 'KING'  UNION SELECT 'SCOTT'  UNION SELECT 'GREEN'  UNION SELECT 'BAKER'  UNION SELECT 'ADAMS'  UNION SELECT 'NELSON'  UNION SELECT 'HILL'  UNION SELECT 'RAMIREZ'  UNION SELECT 'CAMPBELL'  UNION SELECT 'MITCHELL'  UNION SELECT 'ROBERTS'  UNION SELECT 'CARTER'  UNION SELECT 'PHILLIPS'  UNION SELECT 'EVANS'  UNION SELECT 'TURNER'  UNION SELECT 'TORRES'  UNION SELECT 'PARKER'  UNION SELECT 'COLLINS'  UNION SELECT 'EDWARDS'  UNION SELECT 'STEWART'  UNION SELECT 'FLORES'  UNION SELECT 'MORRIS'  UNION SELECT 'NGUYEN'  UNION SELECT 'MURPHY'  UNION SELECT 'RIVERA'  UNION SELECT 'COOK'  UNION SELECT 'ROGERS'  UNION SELECT 'MORGAN'  UNION SELECT 'PETERSON'  UNION SELECT 'COOPER'  UNION SELECT 'REED'  UNION SELECT 'BAILEY'  UNION SELECT 'BELL'  UNION SELECT 'GOMEZ'  UNION SELECT 'KELLY'  UNION SELECT 'HOWARD'  UNION SELECT 'WARD'  UNION SELECT 'COX'  UNION SELECT 'DIAZ'  UNION SELECT 'RICHARDSON'  UNION SELECT 'WOOD'  UNION SELECT 'WATSON'  UNION SELECT 'BROOKS'  UNION SELECT 'BENNETT'  UNION SELECT 'GRAY'  UNION SELECT 'JAMES'  UNION SELECT 'REYES'  UNION SELECT 'CRUZ'  UNION SELECT 'HUGHES'  UNION SELECT 'PRICE'  UNION SELECT 'MYERS'  UNION SELECT 'LONG'  UNION SELECT 'FOSTER'  UNION SELECT 'SANDERS'  UNION SELECT 'ROSS'  UNION SELECT 'MORALES'  UNION SELECT 'POWELL'  UNION SELECT 'SULLIVAN'  UNION SELECT 'RUSSELL'  UNION SELECT 'ORTIZ'  UNION SELECT 'JENKINS'  UNION SELECT 'GUTIERREZ'  UNION SELECT 'PERRY'  UNION SELECT 'BUTLER'  UNION SELECT 'BARNES'  UNION SELECT 'FISHER'  UNION SELECT 'HENDERSON'  UNION SELECT 'COLEMAN'  UNION SELECT 'SIMMONS'  UNION SELECT 'PATTERSON'  UNION SELECT 'JORDAN'  UNION SELECT 'REYNOLDS'  UNION SELECT 'HAMILTON'  UNION SELECT 'GRAHAM'  UNION SELECT 'KIM'  UNION SELECT 'GONZALES'  UNION SELECT 'ALEXANDER'  UNION SELECT 'RAMOS'  UNION SELECT 'WALLACE'  UNION SELECT 'GRIFFIN'  UNION SELECT 'WEST'  UNION SELECT 'COLE'  UNION SELECT 'HAYES'  UNION SELECT 'CHAVEZ'  UNION SELECT 'GIBSON'  UNION SELECT 'BRYANT'  UNION SELECT 'ELLIS'  UNION SELECT 'STEVENS'  UNION SELECT 'MURRAY'  UNION SELECT 'FORD'  UNION SELECT 'MARSHALL'  UNION SELECT 'OWENS'  UNION SELECT 'MCDONALD'  UNION SELECT 'HARRISON'  UNION SELECT 'RUIZ'  UNION SELECT 'KENNEDY'  UNION SELECT 'WELLS'  UNION SELECT 'ALVAREZ'  UNION SELECT 'WOODS'  UNION SELECT 'MENDOZA'  UNION SELECT 'CASTILLO'  UNION SELECT 'OLSON'  UNION SELECT 'WEBB'  UNION SELECT 'WASHINGTON'  UNION SELECT 'TUCKER'  UNION SELECT 'FREEMAN'  UNION SELECT 'BURNS'  UNION SELECT 'HENRY'  UNION SELECT 'VASQUEZ'  UNION SELECT 'SNYDER'  UNION SELECT 'SIMPSON'  UNION SELECT 'CRAWFORD'  UNION SELECT 'JIMENEZ'  UNION SELECT 'PORTER'  UNION SELECT 'MASON'  UNION SELECT 'SHAW'  UNION SELECT 'GORDON'  UNION SELECT 'WAGNER'  UNION SELECT 'HUNTER'  UNION SELECT 'ROMERO'  UNION SELECT 'HICKS'  UNION SELECT 'DIXON'  UNION SELECT 'HUNT'  UNION SELECT 'PALMER'  UNION SELECT 'ROBERTSON'  UNION SELECT 'BLACK'  UNION SELECT 'HOLMES'  UNION SELECT 'STONE'  UNION SELECT 'MEYER'  UNION SELECT 'BOYD'  UNION SELECT 'MILLS'  UNION SELECT 'WARREN'  UNION SELECT 'FOX'  UNION SELECT 'ROSE'  UNION SELECT 'RICE'  UNION SELECT 'MORENO'  UNION SELECT 'SCHMIDT'  UNION SELECT 'PATEL'  UNION SELECT 'FERGUSON'  UNION SELECT 'NICHOLS'  UNION SELECT 'HERRERA'  UNION SELECT 'MEDINA'  UNION SELECT 'RYAN'  UNION SELECT 'FERNANDEZ'  UNION SELECT 'WEAVER'  UNION SELECT 'DANIELS'  UNION SELECT 'STEPHENS'  UNION SELECT 'GARDNER'  UNION SELECT 'PAYNE'  UNION SELECT 'KELLEY'  UNION SELECT 'DUNN'  UNION SELECT 'PIERCE'  UNION SELECT 'ARNOLD'  UNION SELECT 'TRAN'  UNION SELECT 'SPENCER'  UNION SELECT 'PETERS'  UNION SELECT 'HAWKINS'  UNION SELECT 'GRANT'  UNION SELECT 'HANSEN'  UNION SELECT 'CASTRO'  UNION SELECT 'HOFFMAN'  UNION SELECT 'HART'  UNION SELECT 'ELLIOTT'  UNION SELECT 'CUNNINGHAM'  UNION SELECT 'KNIGHT'  UNION SELECT 'BRADLEY'  UNION SELECT 'CARROLL'  UNION SELECT 'HUDSON'  UNION SELECT 'DUNCAN'  UNION SELECT 'ARMSTRONG'  UNION SELECT 'BERRY'  UNION SELECT 'ANDREWS'  UNION SELECT 'JOHNSTON'  UNION SELECT 'RAY'  UNION SELECT 'LANE'  UNION SELECT 'RILEY'  UNION SELECT 'CARPENTER'  UNION SELECT 'PERKINS'  UNION SELECT 'AGUILAR'  UNION SELECT 'SILVA'  UNION SELECT 'RICHARDS'  UNION SELECT 'WILLIS'  UNION SELECT 'MATTHEWS'  UNION SELECT 'CHAPMAN'  UNION SELECT 'LAWRENCE'  UNION SELECT 'GARZA'  UNION SELECT 'VARGAS'  UNION SELECT 'WATKINS'  UNION SELECT 'WHEELER'  UNION SELECT 'LARSON'  UNION SELECT 'CARLSON'  UNION SELECT 'HARPER'  UNION SELECT 'GEORGE'  UNION SELECT 'GREENE'  UNION SELECT 'BURKE'  UNION SELECT 'GUZMAN'  UNION SELECT 'MORRISON'  UNION SELECT 'MUNOZ'  UNION SELECT 'JACOBS'  UNION SELECT 'OBRIEN'  UNION SELECT 'LAWSON'  UNION SELECT 'FRANKLIN'  UNION SELECT 'LYNCH'  UNION SELECT 'BISHOP'  UNION SELECT 'CARR'  UNION SELECT 'SALAZAR'  UNION SELECT 'AUSTIN'  UNION SELECT 'MENDEZ'  UNION SELECT 'GILBERT'  UNION SELECT 'JENSEN'  UNION SELECT 'WILLIAMSON'  UNION SELECT 'MONTGOMERY'  UNION SELECT 'HARVEY'  UNION SELECT 'OLIVER'  UNION SELECT 'HOWELL'  UNION SELECT 'DEAN'  UNION SELECT 'HANSON'  UNION SELECT 'WEBER'  UNION SELECT 'GARRETT'  UNION SELECT 'SIMS'  UNION SELECT 'BURTON'  UNION SELECT 'FULLER'  UNION SELECT 'SOTO'  UNION SELECT 'MCCOY'  UNION SELECT 'WELCH'  UNION SELECT 'CHEN'  UNION SELECT 'SCHULTZ'  UNION SELECT 'WALTERS'  UNION SELECT 'REID'  UNION SELECT 'FIELDS'  UNION SELECT 'WALSH'  UNION SELECT 'LITTLE'  UNION SELECT 'FOWLER'  UNION SELECT 'BOWMAN'  UNION SELECT 'DAVIDSON'  UNION SELECT 'MAY'  UNION SELECT 'DAY'  UNION SELECT 'SCHNEIDER'  UNION SELECT 'NEWMAN'  UNION SELECT 'BREWER'  UNION SELECT 'LUCAS'  UNION SELECT 'HOLLAND'  UNION SELECT 'WONG'  UNION SELECT 'BANKS'  UNION SELECT 'SANTOS'  UNION SELECT 'CURTIS'  UNION SELECT 'PEARSON'  UNION SELECT 'DELGADO'  UNION SELECT 'VALDEZ'  UNION SELECT 'PENA'  UNION SELECT 'RIOS'  UNION SELECT 'DOUGLAS'  UNION SELECT 'SANDOVAL'  UNION SELECT 'BARRETT'  UNION SELECT 'HOPKINS'  UNION SELECT 'KELLER'  UNION SELECT 'GUERRERO'  UNION SELECT 'STANLEY'  UNION SELECT 'BATES'  UNION SELECT 'ALVARADO'  UNION SELECT 'BECK'  UNION SELECT 'ORTEGA'  UNION SELECT 'WADE'  UNION SELECT 'ESTRADA'  UNION SELECT 'CONTRERAS'  UNION SELECT 'BARNETT'  UNION SELECT 'CALDWELL'  UNION SELECT 'SANTIAGO'  UNION SELECT 'LAMBERT'  UNION SELECT 'POWERS'  UNION SELECT 'CHAMBERS'  UNION SELECT 'NUNEZ'  UNION SELECT 'CRAIG'  UNION SELECT 'LEONARD'  UNION SELECT 'LOWE'  UNION SELECT 'RHODES'  UNION SELECT 'BYRD'  UNION SELECT 'GREGORY'  UNION SELECT 'SHELTON'  UNION SELECT 'FRAZIER'  UNION SELECT 'BECKER'  UNION SELECT 'MALDONADO'  UNION SELECT 'FLEMING'  UNION SELECT 'VEGA'  UNION SELECT 'SUTTON'  UNION SELECT 'COHEN'  UNION SELECT 'JENNINGS'  UNION SELECT 'PARKS'  UNION SELECT 'MCDANIEL'  UNION SELECT 'WATTS'  UNION SELECT 'BARKER'  UNION SELECT 'NORRIS'  UNION SELECT 'VAUGHN'  UNION SELECT 'VAZQUEZ'  UNION SELECT 'HOLT'  UNION SELECT 'SCHWARTZ'  UNION SELECT 'STEELE'  UNION SELECT 'BENSON'  UNION SELECT 'NEAL'  UNION SELECT 'DOMINGUEZ'  UNION SELECT 'HORTON'  UNION SELECT 'TERRY'  UNION SELECT 'WOLFE'  UNION SELECT 'HALE'  UNION SELECT 'LYONS'  UNION SELECT 'GRAVES'  UNION SELECT 'HAYNES'  UNION SELECT 'MILES'  UNION SELECT 'PARK'  UNION SELECT 'WARNER'  UNION SELECT 'PADILLA'  UNION SELECT 'BUSH'  UNION SELECT 'THORNTON'  UNION SELECT 'MCCARTHY'  UNION SELECT 'MANN'  UNION SELECT 'ZIMMERMAN'  UNION SELECT 'ERICKSON'  UNION SELECT 'FLETCHER'  UNION SELECT 'MCKINNEY'  UNION SELECT 'PAGE'  UNION SELECT 'DAWSON'  UNION SELECT 'JOSEPH'  UNION SELECT 'MARQUEZ'  UNION SELECT 'REEVES'  UNION SELECT 'KLEIN'  UNION SELECT 'ESPINOZA'  UNION SELECT 'BALDWIN'  UNION SELECT 'MORAN'  UNION SELECT 'LOVE'  UNION SELECT 'ROBBINS'  UNION SELECT 'HIGGINS'  UNION SELECT 'BALL'  UNION SELECT 'CORTEZ'  UNION SELECT 'LE'  UNION SELECT 'GRIFFITH'  UNION SELECT 'BOWEN'  UNION SELECT 'SHARP'  UNION SELECT 'CUMMINGS'  UNION SELECT 'RAMSEY'  UNION SELECT 'HARDY'  UNION SELECT 'SWANSON'  UNION SELECT 'BARBER'  UNION SELECT 'ACOSTA'  UNION SELECT 'LUNA'  UNION SELECT 'CHANDLER'  UNION SELECT 'BLAIR'  UNION SELECT 'DANIEL'  UNION SELECT 'CROSS'  UNION SELECT 'SIMON'  UNION SELECT 'DENNIS'  UNION SELECT 'OCONNOR'  UNION SELECT 'QUINN'  UNION SELECT 'GROSS'  UNION SELECT 'NAVARRO'  UNION SELECT 'MOSS'  UNION SELECT 'FITZGERALD'  UNION SELECT 'DOYLE'  UNION SELECT 'MCLAUGHLIN'  UNION SELECT 'ROJAS'  UNION SELECT 'RODGERS'  UNION SELECT 'STEVENSON'  UNION SELECT 'SINGH'  UNION SELECT 'YANG'  UNION SELECT 'FIGUEROA'  UNION SELECT 'HARMON'  UNION SELECT 'NEWTON'  UNION SELECT 'PAUL'  UNION SELECT 'MANNING'  UNION SELECT 'GARNER'  UNION SELECT 'MCGEE'  UNION SELECT 'REESE'  UNION SELECT 'FRANCIS'  UNION SELECT 'BURGESS'  UNION SELECT 'ADKINS'  UNION SELECT 'GOODMAN'  UNION SELECT 'CURRY'  UNION SELECT 'BRADY'  UNION SELECT 'CHRISTENSEN'  UNION SELECT 'POTTER'  UNION SELECT 'WALTON'  UNION SELECT 'GOODWIN'  UNION SELECT 'MULLINS'  UNION SELECT 'MOLINA'  UNION SELECT 'WEBSTER'  UNION SELECT 'FISCHER'  UNION SELECT 'CAMPOS'  UNION SELECT 'AVILA'  UNION SELECT 'SHERMAN'  UNION SELECT 'TODD'  UNION SELECT 'CHANG'  UNION SELECT 'BLAKE'  UNION SELECT 'MALONE'  UNION SELECT 'WOLF'  UNION SELECT 'HODGES'  UNION SELECT 'JUAREZ'  UNION SELECT 'GILL'  UNION SELECT 'FARMER'  UNION SELECT 'HINES'  UNION SELECT 'GALLAGHER'  UNION SELECT 'DURAN'  UNION SELECT 'HUBBARD'  UNION SELECT 'CANNON'  UNION SELECT 'MIRANDA'  UNION SELECT 'WANG'  UNION SELECT 'SAUNDERS'  UNION SELECT 'TATE'  UNION SELECT 'MACK'  UNION SELECT 'HAMMOND'  UNION SELECT 'CARRILLO'  UNION SELECT 'TOWNSEND'  UNION SELECT 'WISE'  UNION SELECT 'INGRAM'  UNION SELECT 'BARTON'  UNION SELECT 'MEJIA'  UNION SELECT 'AYALA'  UNION SELECT 'SCHROEDER'  UNION SELECT 'HAMPTON'  UNION SELECT 'ROWE'  UNION SELECT 'PARSONS'  UNION SELECT 'FRANK'  UNION SELECT 'WATERS'  UNION SELECT 'STRICKLAND'  UNION SELECT 'OSBORNE'  UNION SELECT 'MAXWELL'  UNION SELECT 'CHAN'  UNION SELECT 'DELEON'  UNION SELECT 'NORMAN'  UNION SELECT 'HARRINGTON'  UNION SELECT 'CASEY'  UNION SELECT 'PATTON'  UNION SELECT 'LOGAN'  UNION SELECT 'BOWERS'  UNION SELECT 'MUELLER'  UNION SELECT 'GLOVER'  UNION SELECT 'FLOYD'  UNION SELECT 'HARTMAN'  UNION SELECT 'BUCHANAN'  UNION SELECT 'COBB'  UNION SELECT 'FRENCH'  UNION SELECT 'KRAMER'  UNION SELECT 'MCCORMICK'  UNION SELECT 'CLARKE'  UNION SELECT 'TYLER'  UNION SELECT 'GIBBS'  UNION SELECT 'MOODY'  UNION SELECT 'CONNER'  UNION SELECT 'SPARKS'  UNION SELECT 'MCGUIRE'  UNION SELECT 'LEON'  UNION SELECT 'BAUER'  UNION SELECT 'NORTON'  UNION SELECT 'POPE'  UNION SELECT 'FLYNN'  UNION SELECT 'HOGAN'  UNION SELECT 'ROBLES'  UNION SELECT 'SALINAS'  UNION SELECT 'YATES'  UNION SELECT 'LINDSEY'  UNION SELECT 'LLOYD'  UNION SELECT 'MARSH'  UNION SELECT 'MCBRIDE'  UNION SELECT 'OWEN'  UNION SELECT 'SOLIS'  UNION SELECT 'PHAM'  UNION SELECT 'LANG'  UNION SELECT 'PRATT'  UNION SELECT 'LARA'  UNION SELECT 'BROCK'  UNION SELECT 'BALLARD'  UNION SELECT 'TRUJILLO'  UNION SELECT 'SHAFFER'  UNION SELECT 'DRAKE'  UNION SELECT 'ROMAN'  UNION SELECT 'AGUIRRE'  UNION SELECT 'MORTON'  UNION SELECT 'STOKES'  UNION SELECT 'LAMB'  UNION SELECT 'PACHECO'  UNION SELECT 'PATRICK'  UNION SELECT 'COCHRAN'  UNION SELECT 'SHEPHERD'  UNION SELECT 'CAIN'  UNION SELECT 'BURNETT'  UNION SELECT 'HESS'  UNION SELECT 'LI'  UNION SELECT 'CERVANTES'  UNION SELECT 'OLSEN'  UNION SELECT 'BRIGGS'  UNION SELECT 'OCHOA'  UNION SELECT 'CABRERA'  UNION SELECT 'VELASQUEZ'  UNION SELECT 'MONTOYA'  UNION SELECT 'ROTH'  UNION SELECT 'MEYERS'  UNION SELECT 'CARDENAS'  UNION SELECT 'FUENTES'  UNION SELECT 'WEISS'  UNION SELECT 'HOOVER'  UNION SELECT 'WILKINS'  UNION SELECT 'NICHOLSON'  UNION SELECT 'UNDERWOOD'  UNION SELECT 'SHORT'  UNION SELECT 'CARSON'  UNION SELECT 'MORROW'  UNION SELECT 'COLON'  UNION SELECT 'HOLLOWAY'  UNION SELECT 'SUMMERS'  UNION SELECT 'BRYAN'  UNION SELECT 'PETERSEN'  UNION SELECT 'MCKENZIE'  UNION SELECT 'SERRANO'  UNION SELECT 'WILCOX'  UNION SELECT 'CAREY'  UNION SELECT 'CLAYTON'  UNION SELECT 'POOLE'  UNION SELECT 'CALDERON'  UNION SELECT 'GALLEGOS'  UNION SELECT 'GREER'  UNION SELECT 'RIVAS'  UNION SELECT 'GUERRA'  UNION SELECT 'DECKER'  UNION SELECT 'COLLIER'  UNION SELECT 'WALL'  UNION SELECT 'WHITAKER'  UNION SELECT 'BASS'  UNION SELECT 'FLOWERS'  UNION SELECT 'DAVENPORT'  UNION SELECT 'CONLEY'  UNION SELECT 'HOUSTON'  UNION SELECT 'HUFF'  UNION SELECT 'COPELAND'  UNION SELECT 'HOOD'  UNION SELECT 'MONROE'  UNION SELECT 'MASSEY'  UNION SELECT 'ROBERSON'  UNION SELECT 'COMBS'  UNION SELECT 'FRANCO'  UNION SELECT 'LARSEN'  UNION SELECT 'PITTMAN'  UNION SELECT 'RANDALL'  UNION SELECT 'SKINNER'  UNION SELECT 'WILKINSON'  UNION SELECT 'KIRBY'  UNION SELECT 'CAMERON'  UNION SELECT 'BRIDGES'  UNION SELECT 'ANTHONY'  UNION SELECT 'RICHARD'  UNION SELECT 'KIRK'  UNION SELECT 'BRUCE'  UNION SELECT 'SINGLETON'  UNION SELECT 'MATHIS'  UNION SELECT 'BRADFORD'  UNION SELECT 'BOONE'  UNION SELECT 'ABBOTT'  UNION SELECT 'CHARLES'  UNION SELECT 'ALLISON'  UNION SELECT 'SWEENEY'  UNION SELECT 'ATKINSON'  UNION SELECT 'HORN'  UNION SELECT 'JEFFERSON'  UNION SELECT 'ROSALES'  UNION SELECT 'YORK'  UNION SELECT 'CHRISTIAN'  UNION SELECT 'PHELPS'  UNION SELECT 'FARRELL'  UNION SELECT 'CASTANEDA'  UNION SELECT 'NASH'  UNION SELECT 'DICKERSON'  UNION SELECT 'BOND'  UNION SELECT 'WYATT'  UNION SELECT 'FOLEY'  UNION SELECT 'CHASE'  UNION SELECT 'GATES'  UNION SELECT 'VINCENT'  UNION SELECT 'MATHEWS'  UNION SELECT 'HODGE'  UNION SELECT 'GARRISON'  UNION SELECT 'TREVINO'  UNION SELECT 'VILLARREAL'  UNION SELECT 'HEATH'  UNION SELECT 'DALTON'  UNION SELECT 'VALENCIA'  UNION SELECT 'CALLAHAN'  UNION SELECT 'HENSLEY'  UNION SELECT 'ATKINS'  UNION SELECT 'HUFFMAN'  UNION SELECT 'ROY'  UNION SELECT 'BOYER'  UNION SELECT 'SHIELDS'  UNION SELECT 'LIN'  UNION SELECT 'HANCOCK'  UNION SELECT 'GRIMES'  UNION SELECT 'GLENN'  UNION SELECT 'CLINE'  UNION SELECT 'DELACRUZ'  UNION SELECT 'CAMACHO'  UNION SELECT 'DILLON'  UNION SELECT 'PARRISH'  UNION SELECT 'ONEILL'  UNION SELECT 'MELTON'  UNION SELECT 'BOOTH'  UNION SELECT 'KANE'  UNION SELECT 'BERG'  UNION SELECT 'HARRELL'  UNION SELECT 'PITTS'  UNION SELECT 'SAVAGE'  UNION SELECT 'WIGGINS'  UNION SELECT 'BRENNAN'  UNION SELECT 'SALAS'  UNION SELECT 'MARKS'  UNION SELECT 'RUSSO'  UNION SELECT 'SAWYER'  UNION SELECT 'BAXTER'  UNION SELECT 'GOLDEN'  UNION SELECT 'HUTCHINSON'  UNION SELECT 'LIU'  UNION SELECT 'WALTER'  UNION SELECT 'MCDOWELL'  UNION SELECT 'WILEY'  UNION SELECT 'RICH'  UNION SELECT 'HUMPHREY'  UNION SELECT 'JOHNS'  UNION SELECT 'KOCH'  UNION SELECT 'SUAREZ'  UNION SELECT 'HOBBS'  UNION SELECT 'BEARD'  UNION SELECT 'GILMORE'  UNION SELECT 'IBARRA'  UNION SELECT 'KEITH'  UNION SELECT 'MACIAS'  UNION SELECT 'KHAN'  UNION SELECT 'ANDRADE'  UNION SELECT 'WARE'  UNION SELECT 'STEPHENSON'  UNION SELECT 'HENSON'  UNION SELECT 'WILKERSON'  UNION SELECT 'DYER'  UNION SELECT 'MCCLURE'  UNION SELECT 'BLACKWELL'  UNION SELECT 'MERCADO'  UNION SELECT 'TANNER'  UNION SELECT 'EATON'  UNION SELECT 'CLAY'  UNION SELECT 'BARRON'  UNION SELECT 'BEASLEY'  UNION SELECT 'ONEAL'  UNION SELECT 'PRESTON'  UNION SELECT 'SMALL'  UNION SELECT 'WU'  UNION SELECT 'ZAMORA'  UNION SELECT 'MACDONALD'  UNION SELECT 'VANCE'  UNION SELECT 'SNOW'  UNION SELECT 'MCCLAIN'  UNION SELECT 'STAFFORD'  UNION SELECT 'OROZCO'  UNION SELECT 'BARRY'  UNION SELECT 'ENGLISH'  UNION SELECT 'SHANNON'  UNION SELECT 'KLINE'  UNION SELECT 'JACOBSON'  UNION SELECT 'WOODARD'  UNION SELECT 'HUANG'  UNION SELECT 'KEMP'  UNION SELECT 'MOSLEY'  UNION SELECT 'PRINCE'  UNION SELECT 'MERRITT'  UNION SELECT 'HURST'  UNION SELECT 'VILLANUEVA'  UNION SELECT 'ROACH'  UNION SELECT 'NOLAN'  UNION SELECT 'LAM'  UNION SELECT 'YODER'  UNION SELECT 'MCCULLOUGH'  UNION SELECT 'LESTER'  UNION SELECT 'SANTANA'  UNION SELECT 'VALENZUELA'  UNION SELECT 'WINTERS'  UNION SELECT 'BARRERA'  UNION SELECT 'LEACH'  UNION SELECT 'ORR'  UNION SELECT 'BERGER'  UNION SELECT 'MCKEE'  UNION SELECT 'STRONG'  UNION SELECT 'CONWAY'  UNION SELECT 'STEIN'  UNION SELECT 'WHITEHEAD'  UNION SELECT 'BULLOCK'  UNION SELECT 'ESCOBAR'  UNION SELECT 'KNOX'  UNION SELECT 'MEADOWS'  UNION SELECT 'SOLOMON'  UNION SELECT 'VELEZ'  UNION SELECT 'ODONNELL'  UNION SELECT 'KERR'  UNION SELECT 'STOUT'  UNION SELECT 'BLANKENSHIP'  UNION SELECT 'BROWNING'  UNION SELECT 'KENT'  UNION SELECT 'LOZANO'  UNION SELECT 'BARTLETT'  UNION SELECT 'PRUITT'  UNION SELECT 'BUCK'  UNION SELECT 'BARR'  UNION SELECT 'GAINES'  UNION SELECT 'DURHAM'  UNION SELECT 'GENTRY'  UNION SELECT 'MCINTYRE'  UNION SELECT 'SLOAN'  UNION SELECT 'MELENDEZ'  UNION SELECT 'ROCHA'  UNION SELECT 'HERMAN'  UNION SELECT 'SEXTON'  UNION SELECT 'MOON'  UNION SELECT 'HENDRICKS'  UNION SELECT 'RANGEL'  UNION SELECT 'STARK'  UNION SELECT 'LOWERY'  UNION SELECT 'HARDIN'  UNION SELECT 'HULL'  UNION SELECT 'SELLERS'  UNION SELECT 'ELLISON'  UNION SELECT 'CALHOUN'  UNION SELECT 'GILLESPIE'  UNION SELECT 'MORA'  UNION SELECT 'KNAPP'  UNION SELECT 'MCCALL'  UNION SELECT 'MORSE'  UNION SELECT 'DORSEY'  UNION SELECT 'WEEKS'  UNION SELECT 'NIELSEN'  UNION SELECT 'LIVINGSTON'  UNION SELECT 'LEBLANC'  UNION SELECT 'MCLEAN'  UNION SELECT 'BRADSHAW'  UNION SELECT 'GLASS'  UNION SELECT 'MIDDLETON'  UNION SELECT 'BUCKLEY'  UNION SELECT 'SCHAEFER'  UNION SELECT 'FROST'  UNION SELECT 'HOWE'  UNION SELECT 'HOUSE'  UNION SELECT 'MCINTOSH'  UNION SELECT 'HO'  UNION SELECT 'PENNINGTON'  UNION SELECT 'REILLY'  UNION SELECT 'HEBERT'  UNION SELECT 'MCFARLAND'  UNION SELECT 'HICKMAN'  UNION SELECT 'NOBLE'  UNION SELECT 'SPEARS'  UNION SELECT 'CONRAD'  UNION SELECT 'ARIAS'  UNION SELECT 'GALVAN'  UNION SELECT 'VELAZQUEZ'  UNION SELECT 'HUYNH'  UNION SELECT 'FREDERICK'  UNION SELECT 'RANDOLPH'  UNION SELECT 'CANTU'  UNION SELECT 'FITZPATRICK'  UNION SELECT 'MAHONEY'  UNION SELECT 'PECK'  UNION SELECT 'VILLA'  UNION SELECT 'MICHAEL'  UNION SELECT 'DONOVAN'  UNION SELECT 'MCCONNELL'  UNION SELECT 'WALLS'  UNION SELECT 'BOYLE'  UNION SELECT 'MAYER'  UNION SELECT 'ZUNIGA'  UNION SELECT 'GILES'  UNION SELECT 'PINEDA'  UNION SELECT 'PACE'  UNION SELECT 'HURLEY'  UNION SELECT 'MAYS'  UNION SELECT 'MCMILLAN'  UNION SELECT 'CROSBY'  UNION SELECT 'AYERS'  UNION SELECT 'CASE'  UNION SELECT 'BENTLEY'  UNION SELECT 'SHEPARD'  UNION SELECT 'EVERETT'  UNION SELECT 'PUGH'  UNION SELECT 'DAVID'  UNION SELECT 'MCMAHON'  UNION SELECT 'DUNLAP'  UNION SELECT 'BENDER'  UNION SELECT 'HAHN'  UNION SELECT 'HARDING'  UNION SELECT 'ACEVEDO'  UNION SELECT 'RAYMOND'  UNION SELECT 'BLACKBURN'  UNION SELECT 'DUFFY'  UNION SELECT 'LANDRY'  UNION SELECT 'DOUGHERTY'  UNION SELECT 'BAUTISTA'  UNION SELECT 'SHAH'  UNION SELECT 'POTTS'  UNION SELECT 'ARROYO'  UNION SELECT 'VALENTINE'  UNION SELECT 'MEZA'  UNION SELECT 'GOULD'  UNION SELECT 'VAUGHAN'  UNION SELECT 'FRY'  UNION SELECT 'RUSH'  UNION SELECT 'AVERY'  UNION SELECT 'HERRING'  UNION SELECT 'DODSON'  UNION SELECT 'CLEMENTS'  UNION SELECT 'SAMPSON'  UNION SELECT 'TAPIA'  UNION SELECT 'BEAN'  UNION SELECT 'LYNN'  UNION SELECT 'CRANE'  UNION SELECT 'FARLEY'  UNION SELECT 'CISNEROS'  UNION SELECT 'BENTON'  UNION SELECT 'ASHLEY'  UNION SELECT 'MCKAY'  UNION SELECT 'FINLEY'  UNION SELECT 'BEST'  UNION SELECT 'BLEVINS'  UNION SELECT 'FRIEDMAN'  UNION SELECT 'MOSES'  UNION SELECT 'SOSA'  UNION SELECT 'BLANCHARD'  UNION SELECT 'HUBER'  UNION SELECT 'FRYE'  UNION SELECT 'KRUEGER'  UNION SELECT 'BERNARD'  UNION SELECT 'ROSARIO'  UNION SELECT 'RUBIO'  UNION SELECT 'MULLEN'  UNION SELECT 'BENJAMIN'  UNION SELECT 'HALEY'  UNION SELECT 'CHUNG'  UNION SELECT 'MOYER'  UNION SELECT 'CHOI'  UNION SELECT 'HORNE'  UNION SELECT 'YU'  UNION SELECT 'WOODWARD'  UNION SELECT 'ALI'  UNION SELECT 'NIXON'  UNION SELECT 'HAYDEN'  UNION SELECT 'RIVERS'  UNION SELECT 'ESTES'  UNION SELECT 'MCCARTY'  UNION SELECT 'RICHMOND'  UNION SELECT 'STUART'  UNION SELECT 'MAYNARD'  UNION SELECT 'BRANDT'  UNION SELECT 'OCONNELL'  UNION SELECT 'HANNA'  UNION SELECT 'SANFORD'  UNION SELECT 'SHEPPARD'  UNION SELECT 'CHURCH'  UNION SELECT 'BURCH'  UNION SELECT 'LEVY'  UNION SELECT 'RASMUSSEN'  UNION SELECT 'COFFEY'  UNION SELECT 'PONCE'
)
INSERT INTO [dbo].[Customers] (FirstName, MiddleName, LastName)
SELECT F.Name AS FirstName, M.Initial as MiddleName, L.Name AS LastName
 FROM Fnames AS F
 CROSS JOIN Mnames as M
 CROSS JOIN Lnames AS L;

Next I performed some updates and deletes so that the CDC tables would not just contain INSERT data.

UPDATE [dbo].[Customers]
   SET FirstName = 'Angela'
 WHERE FirstName = 'Mary';

DELETE FROM [dbo].[Customers]
 WHERE id < 1000
   AND id % 5 = 2;

UPDATE [dbo].[Customers]
   SET FirstName = 'Steve',
       MiddleName = 'M',
	   LastName = 'Stedman'
 WHERE id = 510900;

I added the foreign key to make the challenge more realistic.

ALTER TABLE [dbo].[Orders]
    WITH CHECK ADD CONSTRAINT [FK_Orders_People]
FOREIGN KEY([customerId])
    REFERENCES [dbo].[Customers] ([id]);
GO

ALTER TABLE [dbo].[Orders]
    CHECK CONSTRAINT [FK_Orders_People];
GO

I then filled the [Orders] table with some data so that the foreign key constraints may come into play.

SET NOCOUNT ON;

DECLARE @iLoop as INTEGER;
SET @iLoop = cast(rand() * 1000 as INTEGER) + 10;

WHILE @iLoop > 0
BEGIN
	INSERT INTO [dbo].[Orders] ([orderDate],
                                    [customerId],
                                    [shippingType],
                                    [orderDetails])
	SELECT DATEADD(month,
                       - (cast(rand() * 50 as INTEGER)),
                       GETDATE()),
		   id,
		   CASE id % 3
		     WHEN 1 THEN 'Next Day'
			 WHEN 2 THEN '2 Day'
			 ELSE 'Ground'
			END as Shipping,
		   CAST(NEWID() as varchar(max)) +
                        ' this is the order details...'
	FROM Customers
	WHERE id = cast(rand() * 600000 as INTEGER);

	SET @iLoop = @iLoop - 1;
END

GO 3

If you are not familiar with the GO statement followed by a number, see my blog post that explains it.
I then added some indexes to be sort of a decoy, so that you might think this was like challenge #1 where you can just pull the corrupt data from the non-clustered indexes.

CREATE NONCLUSTERED INDEX [ncCustomerLastname]
ON [dbo].[Customers]
(
	[LastName] ASC
);

CREATE NONCLUSTERED INDEX [ncCustomerFirstname]
ON [dbo].[Customers]
(
	[FirstName] ASC
);

CREATE NONCLUSTERED INDEX [ncOrdersCustIdShippingType]
ON [dbo].[Orders]
(
	[customerId] ASC,
	[shippingType] ASC
);

Next to make it more interesting I added database triggers to prevent creating or dropping tables. If you recognize the Little Britain (tv show) reference to Computer says no, I hope everyone got a chuckle out of this.

CREATE TRIGGER noNewTables
ON DATABASE for CREATE_TABLE
AS
BEGIN
  PRINT 'Hey that is forbidden. Not allowed. Computer says no.';
  ROLLBACK;
END
GO

CREATE TRIGGER noDropTables
ON DATABASE for DROP_TABLE
AS
BEGIN
  PRINT 'Hey that is forbidden. Not allowed. Computer says no.';
  ROLLBACK;
END
GO

Then I decided to take a look at how many pages were included in the [Customers] table that I was planning on corrupting.

DBCC TRACEON(3604) with no_infomsgs;
DBCC IND(CorruptionChallenge4, Customers, 1) with no_infomsgs;

Next I created a table variable, inserted the output from DBCC IND into that table, I then iterated through all of the pages that were of type 1, which indicates a data page. Inside of the cursor, I used DBCC WritePage to corrupt 4 bytes in every single data page in the [Customers] table. My plan here was to prevent people from just using DBCC WritePage to fix a single spot of corruption. I wanted the whole table to be bad.

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.

DECLARE @dbccIndTable as TABLE
(
	PageFID INTEGER, PagePID INTEGER,
	IAMFID BIGINT, IAMPID BIGINT,
	ObjectID BIGINT, IndexID BIGINT,
	PartitionNumber BIGINT,	PartitionID BIGINT,
	iam_chain_type VARCHAR(max),
        PageType BIGINT, IndexLevel BIGINT,
	NextPageFID BIGINT,	NextPagePID BIGINT,
	PrevPageFID BIGINT,	PrevPagePID BIGINT
);

insert @dbccIndTable(
	PageFID, PagePID, IAMFID, IAMPID, ObjectID, IndexID,
	PartitionNumber, PartitionID,
        iam_chain_type, PageType, IndexLevel,
	NextPageFID, NextPagePID, PrevPageFID, PrevPagePID)
EXEC('DBCC IND(CorruptionChallenge4,Customers, 1) with no_infomsgs;')

SELECT * FROM @dbccIndTable;

DECLARE @pagePID as INT;
DECLARE @pageFID as INT;
DECLARE @corruptorCursor as CURSOR;

SET @corruptorCursor = CURSOR FOR
SELECT PagePID, PageFID
  FROM @dbccIndTable
 WHERE PageType = 1;

OPEN @corruptorCursor;
FETCH NEXT FROM @corruptorCursor INTO @pagePID, @pageFID;

WHILE @@FETCH_STATUS = 0
BEGIN
	PRINT cast(@pagePID as VARCHAR (50));
	DECLARE @pageOffset AS INTEGER = 4;
	DBCC WritePage(CorruptionChallenge4, @pageFID,
                     @pagePID, @pageOffset,
                     4, 0x00000000);
	FETCH NEXT FROM @corruptorCursor INTO @pagePID, @pageFID;
END

CLOSE @corruptorCursor;
DEALLOCATE @corruptorCursor;

I then confirmed that the database was indeed corrupt, and that the data could not be selected from the [Customers] table.

DBCC CheckTable(Customers);
SELECT * FROM Customers;

Finally I backed up the database, and saved that backup to be used in the challenge.

BACKUP DATABASE CorruptionChallenge4
TO DISK = 'C:\DBBackups\CorruptionChallenge4_Corrupt.bak'
   WITH COMPRESSION, FORMAT,
      NAME = 'Full Backup of CorruptionChalenge4';

That’s it, that I had a perfectly corrupt database, I started on the adventure of removing the corruption myself. I hope you enjoyed the database.

Related Links:

 

 

More from Stedman Solutions:

SteveStedman5
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!

Related Posts

Leave a Reply

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

*