THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Linchi Shea

Checking out SQL Server via empirical data points

How to Corrupt a SQL Server 2005 Database

How can you corrupt an online SQL Server 2005 database? Okay, why would you want to do that? Well, let's say because you want to test out some DBCC commands.

If you take a SQL Server 2005 database offline, you can easily corrupt it by opening it with a different program and messing up the file content. But then it's unlikely you can bring it online for your DBCC check. I don't know about you. But I've found it darn hard to corrupt an online SQL Server 2005 database intentionally. Yeah, if you are a device driver programmer, it's probably no big deal. But how many SQL Server DBAs/developers know how to write a low-level driver? There must be a few out there, but I don't personally know any.

Now, if you have a Windows failover cluster with a shared disk--a LUN presetned from a SAN, there is an easy way. Well, it doesn't have to be a cluster or a SAN, but that's where we often find the configuration. Anyway, here are the steps:

  • Evict one node (say node B) from the cluster.
  • But don't change anything with respect to the shared drive. Now, the LUN is visible to both node A and node B.
  • Create a small database on the SQL instance from node A, and place the files on the shared drive. Open the database and create a test table just to make sure that the database works fine.
  • At this time, the database files are not visible to node B. You need to reboot node B for the files to become visible to node B. I have not found any command/utility that can make the files visible to node B without rebooting.
  • Now, use Notepad or Wordpad to open up one of the database files on node B and mess it up. Close the file to save your changes.

At this point, you probably think that if you go back to node A and do a DBCC CHECKDB on the database, you would find it corrupt. Not really! The database is technically corrupt. But the corruption may not be visible to node A yet. In fact, in my tests I've found it difficult to force the changes made on node B to become visible to node A. I tried to reboot node B and run the sysinternals' Sync.exe tool to flush changes to disk. Neither was able to get DBCC CHECKDB to report any corruption on node A. Only when I took the database offline and then tried to bring it online did SQL Server report corruption and refused to bring the database online.

But I want to discover the database corruption while it's online. The easiest way to accomplish that is as follows (there may be other easier method, but this is what I found most reliable so far):

  • Make sure there is a running SQL Server 2005 instance on node B.
  • Run sp_attach_db to bring the database online on node B. At this point, the same database is read/write accessible on both node A and node B without being protected by the cluster service--a recipe for disaster. But that's okay since we actually want to corrupt the database.
  • Now, make some changes (e.g. creating a table and inserting some data) to the database on node B and flush the changes to disk with a checkpoint.
  • Go back to node A and run DBCC CHECKDB, you'll get allocation integrity errors.

And the database is corrupt while online. Mission accomplished!

If you know an easier way to force the changes made on node B visible on node A, I'm all ears.

 

Published Wednesday, August 29, 2007 11:35 PM by Linchi Shea
Filed under: , ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

JB said:

Hi

Is there a way to simulate the corruption of  tempdb.

I am providing various scenerios for my company of DR and one of them is tempdb. Is there a way to corrupt tempdb, i know dropping and recreating will create new mdf\ldf so recovery is easy, but simulating the corruption is hard part.

June 2, 2009 4:57 PM
 

Subhash Pant said:

Reminds me of the blog post from @PaulRandall where he says never to turn on/off the hdd or something like that to corrupt the DB.

March 4, 2011 3:53 PM

Leave a Comment

(required) 
(required) 
Submit

About Linchi Shea

Checking out SQL Server via empirical data points

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement