Corruption Challenge 1 – how I corrupted the database

Since the corruption challenge completed yesterday, I have had several request asking how I created the corrupt database. So here is the script that I used to create the Database Corruption Challenge 1.

First the initial setup. Most of this I stole from a query training session that I did several weeks ago. All I really needed was a table with some data in it.


CREATE DATABASE [CorruptionChallenge1];
GO

USE [CorruptionChallenge1];

CREATE TABLE Revenue
(
[id] INTEGER IDENTITY,
[DepartmentID] INTEGER,
[Revenue] INTEGER,
[Year] INTEGER,
[Notes] VARCHAR(300)
);

INSERT INTO Revenue ([DepartmentID], [Revenue], [Year])
VALUES (1,10030,1998),(2,20000,1998),(3,40000,1998),
 (1,20000,1999),(2,600400,1999),(3,500400,1999),
 (1,40050,2000),(2,400300,2000),(3,604000,2000),
 (1,30000,2001),(2,30000,2001),(3,703000,2001),
 (1,90000,2002),(2,200200,2002),(3,80000,2002),
 (1,10300,2003),(2,1000,2003), (3,900300,2003),
 (1,10000,2004),(2,10000,2004),(3,100300,2004),
 (1,208000,2005),(2,200200,2005),(3,203000,2005),
 (1,40000,2006),(2,30000,2006),(3,300300,2006),
 (1,709000,2007),(2,40000,2007),(3,400300,2007),
 (1,50000,2008),(2,50000,2008),(3,500300,2008),
 (1,20000,2009),(2,600030,2009),(3,600300,2009),
 (1,300700,2010),(2,70000,2010),(3,700300,2010),
 (1,80000,2011),(2,80000,2011),(3,800200,2011),
 (1,100030,2012),(2,90000,2012),(3,900300,2012),
 (1,10000,2013),(2,90000,2013),(3,900100,2013),
 (1,100400,2014),(2,900300,2014),(3,903000,2014),
 (1,102000,2015),(2,902000,2015),(3,902000,2015);

UPDATE Revenue SET [Notes] = CAST(NEWID() as VARCHAR(300)) + 'This is some varchar data just to fil out some pages... data pages are only 8k, therefore the more we fill up in each page, the more pages this table will flow into, thus simulating a larger table for the corruption example';

CREATE CLUSTERED INDEX [clustId] ON [dbo].[Revenue]
(
 [id] ASC
);

CREATE NONCLUSTERED INDEX [ncDeptIdYear] ON [dbo].[Revenue]
(
 [DepartmentID] ASC,
 [Revenue] ASC
);

CREATE NONCLUSTERED INDEX [ncBadNameForAnIndex] ON [dbo].[Revenue]
(
 [Year] ASC
)
INCLUDE ( [Notes]) ;

-- first lets look at the REVENUE table
SELECT *
 FROM Revenue;

Setup1

Read more of this post

Newsletter Mailing List Added

With all the interest in the Database Corruption Challenge, and the hundreds of email message I have exchanged with people over the last 3 days relating to the Database Corruption Challenge, I have decided to set up a newsletter mailing list to use to notify people of the upcoming Database Corruption Challenges, and other interesting tidbits.

Sign up for the newsletter to get notified of upcoming Database Corruption Challenges. You will get notified ahead of time when the corruption challenge will be posted.

I would love to contact everyone individually, however the list of people interested in the Corruption Challenge is getting to big to easily manage.

Just click here to go to the newsletter signup form.

What if I don’t want to subscribe to the newsletter?

That’s no problem, I will be announcing the Database Corruption Challenge on Twitter after it is posted. Just follow me on Twitter  @sqlEmt, and watch for updates.

Will you spam me?

No. You will only receive information related to SQL Server topics, like the corruption challenge, like Database Health Monitor, my and company (Stedman Solutions), blog posts, things like that. If you end up getting too much email, it is easy to unsubscribe.

Will you sell my email address?

No your email address will be used by my, and my company Stedman Solutions, LLC. The only way that your email address would be sold would be if someone was to purchase my entire company Stedman Solutions, LLC. which is very unlikely at this point.

Just click here to go to the newsletter signup form.

 

Related Links:

A Weekend Full of Database Corruption

On Saturday morning, I announced the Database Corruption Challenge, and I had to abbreviate it as the DBCC, why not, acronym overloading isn’t always a bad thing.

There were 91 participants, 22 of which ended up with correct answers with no corruption and no data loss.

I created a database, with 3 bytes of corruption in one of the leaf node pages of a clustered index, however all I stated was that the database was corrupt, and it could be fixed with no data loss.

The number of different ways that people solved the problem was amazing. There were many attempts that did remove the corruption, and did recover all the rows, however many judged success as having the same number of rows as before, as a success. There were so many successful solutions that I think I will post one a day for the rest of the week, with the details explained. One solution even involved editing the database file on disk with a hex editor. I don’t have time to post all the good solutions right now.

 

The original corrupt database had been created on SQL Server 2014, but many people contacted me stating that they didn’t have SQL Server 2014 on their home computer, so I ended up creating a SQL Server 2008 version of the problem, but some were so driven to solve the challenge that they ended up installing SQL Server 2014 just to work the problem.

house1

Nice work John. Thanks for playing along, I hope you enjoyed the experience.  The next tweet from Mike Fal followed the post from John Morehouse.

nerd2

 

I hope that John took this comment from Mike Fal as a great compliments, however it was soon corrected by AJ Mendo that it is more geek than nerd.

aj1

Either way, geeks or nerds, it was a good learning experience for those involved, and for me having to evaluate each solution to determine if it was indeed a valid solution with no data loss. Personally I ended up restoring the corrupt database and testing different solutions over 50 times this weekend.

Read more of this post

SQL Server 2008 Downloads for the Database Corruption Challenge (DBCC) Week 1

After introducing the Database Corruption Challenge (DBCC) – Week 1 Challenge yesterday, I received several requests for a SQL Server 2008 version of the challenge. Here is is, and for future Database Corruption Challenges, I will include a SQL Server 2008 version of the challenge.  The 2008 backup file should restore just fine on SQL 2008, SQL Server 2008R2, SQL Server 2012, and SQL 2014. However if you are using SQL Server 2014 you can download the SQL Server 2014 specific backup file instead.

CorruptionChallenge1_2008.zip

 

For more details see the Database Corruption Challenge (DBCC) – Week 1 Challenge.

 

To see the winning solution and list of winners visit the “Weekend of Corruption” blog post.

Introducing the DataBase Corruption Challenge (DBCC) – Week 1 Challenge

corruption

Welcome to the DataBase Corruption Challenge, this is an about weekly blog challenge where I will post a corrupt SQL Server database with some details on what happened to it.

If at this point you are already a bit irked by my use of capitalization in the DataBase Corruption Challenge, and the acronym of DBCC that I have used to describe it, then you are already ahead of many people reading about this challenge. Welcome to the challenge.
The challenge will be to download the corrupt database and attempt to recover it. If you can recover it, please send me the steps to recover it, along with some proof that the database has been recovered. The goal each week will be the following:

Read more of this post

Introducing Stedman Solutions, LLC.

As of today, March 31st 2015, I am off on a new adventure in my life. As of today I am 100% focused on my new business. Here are the details.

StedmanSolutionsHeader2

FOR IMMEDIATE RELEASE:

Stedman Solutions, LLC. To Offer SQL Server Consulting Services.

Bellingham, WA, USA – March 31, 2015 – Today Steve Stedman, Founder/Owner of Stedman Solutions, LLC is proud to announce a new consulting service focused on SQL Server Services, Products, and Training and more. Stedman solutions is based in the Bellingham Washington area and offers onsite services to Northwest Washington, and remote services to the world.

Steve Stedman is the founder and owner of Stedman Solutions, LLC; he has been using SQL since 1990, having 25 years of SQL and database experience. This experience combined with experience on the latest and greatest SQL Server technologies comes together to offer clients services, products and training with a specific focus on SQL Server technologies.

Services: Offering consulting, mentoring and custom solutions for all your SQL Server needs. These consulting services include performance tuning, server upgrades, data migrations, high availability, disaster recovery, business intelligence, reports and more.

  • Performance Tuning: Do you have a SQL Server that is running slow? Are you considering adding more hardware to improve the speed? Performance tuning can tune your existing server, to run faster without always requiring more hardware. Stedman Solutions can help you get the most out of your SQL Server.
  • Server Upgrades: Need help moving from that old unsupported version of SQL Server, to a newer version? Do you need help just confirming if your upgrade plan will work? Would you like to have someone available if something goes wrong? Would you prefer to just hand off the upgrade to someone else? Would you like training to prepare you for this upgrade, and many upgrades in the future? Stedman Solutions can help in all of these environments.
  • Data Migrations: Do you have data in one database that needs to be replicated or moved to another database? Do you want to move your MySQL Data into a SQL Server? For these and many more data migration solutions, Stedman Solutions is ready to help.
  • High Availability: Does your SQL Servers need to run 24×7 with minimal down time? Will your entire workforce be blocked if the SQL Server needs to be restarted? Stedman Solutions can help you improve your uptime, system reliability, and availability.
  • Disaster Recovery: What would happen if your SQL Server was destroyed by natural or human caused disaster? Do you know what risks you may be currently exposed to? Stedman Solutions can help you analyze your risks for disaster, and build a recovery plan, and help train your team to support that plan.
  • Reporting and Business Intelligence: Do you need a SSRS Report created? Do you need help with a BI dashboard? How about building a data warehouse? Stedman Solutions has experience in these areas and can with your Business Intelligence needs.

Products: Database Health Monitor – An application for DBAs and database developers currently in a free Beta release to help analyze your SQL Server to find performance bottlenecks and to find solutions for those performance issues.  Visit http://DatabaseHealth.com for more details.

Training: In addition to specific onsite and web broadcast trainings, Stedman Solutions offers training as part of every consulting engagement. Rather than just providing solutions, every client will learn so that they can do more on their own in the future.

Stedman Solutions, LLC is currently taking on new clients in all of the above mentioned areas. For free 30 minute consultation to see if we can help you, please visit http://StedmanSolutions.com or contact Steve Stedman.

 

Contact:

Steve Stedman

http://StedmanSolutions.com

PO Box 3175, Ferndale, WA 98248

 

###