![]() You want to search for this as a decimal. Go to the page you want to corrupt by using Address ? GoTo (or Ctrl + G), then paste in your Offset Value. If you got an error that you don’t have permissions to view the file, make sure you do have permissions and that you ran XVI32.exe with elevated privileges.) (If you didn’t set the database offline, you’ll get an error that it’s in use. Open the database file by using File ? Open, and then the data file name you copied to the clipboard. It’s the Moment We’ve Been Waiting For: Trash That Pageĭepending on your operating system, you may want to run this with elevated privileges / right click and “run as administrator”. Of course if you wanted the clustered index, you could use index Id 1. I wanted to specifically corrupt a page in the nonclustered index on the DeadBirdies table. Now we can take a look at the pages our table and nonclustered index got created on. GO Find the pages for the nonclustered index with DBCC IND Create a nonclustered index on BirdNameĬREATE NONCLUSTERED INDEX ncBirds ON dbo.DeadBirdies(BirdName) WHERE NUMBER < = 500000 -Cluster on BirdId.ĬREATE UNIQUE CLUSTERED INDEX cxBirdsBirdId ON dbo.DeadBirdies(BirdId) INSERT dbo.DeadBirdies (birdId, birdName, rowCreatedDate) Tally AS ( SELECT ROW_NUMBER() OVER( ORDER BY C) AS NUMBER FROM Pass5) Pass5 AS ( SELECT 1 AS C FROM Pass4 AS A, Pass4 AS B), Pass4 AS ( SELECT 1 AS C FROM Pass3 AS A, Pass3 AS B), Pass3 AS ( SELECT 1 AS C FROM Pass2 AS A, Pass2 AS B), Pass2 AS ( SELECT 1 AS C FROM Pass1 AS A, Pass1 AS B), Pass1 AS ( SELECT 1 AS C FROM Pass0 AS A, Pass0 AS B), Pass0 AS ( SELECT 1 AS C UNION ALL SELECT 1), I'll talk about other settings in a later post.ĪLTER DATABASE CorruptMe SET PAGE_VERIFY CHECKSUM GO -Make sure we're using CHECKSUM as our page verify option IF db_id( 'CorruptMe') IS NOT NULL BEGIN ALTER DATABASE CorruptMe SET SINGLE_USER WITH ROLLBACK IMMEDIATE DROP DATABASE CorruptMe ( Note: Data generation technique found on Stack Overflow, attributed to Itzik Ben-Gan.) We’ll create a single table, insert some data, and create a clustered index and nonclustered index on it. ![]() No installer is necessary: download the zip file, then unzip all files to a directory and run XVI32.exe Create a database to corruptįor our adventure, our database is named CorruptMe. Steps to corrupt your database First, get your hex editorĭownload XVI32 by Christian Maas. You can also download Sample corrupt databases to play with from SQLSkills if you prefer not to run with scissors.This post is where I got the recommendation to use the XVI32 Hex editor. That’s different than what I’m doing in this post, but you should test that also. Creating, detaching, re-attaching, and fixing a suspect database ? This shows how to use the hex editor to corrupt the log file.Inside the Storage Engine: Using DBCC PAGE and DBCC IND to find out if page splits ever roll back ? I use this as my reference for output from DBCC IND and DBCC PAGE.Common bad advice around disaster recovery ? Search for “the best way to create a test corrupt database”.If you’d like to go straight to the source: It just took me a little bit to understand how to use the hex editor and make sure I was doing it properly, so I thought I’d put down the steps I used here in detail. References, and thanks to Paul RandalĮverything I’m doing here I learned from Paul Randal’s blog posts. If you corrupt a system database, you may be reinstalling SQL Server. If you corrupt the wrong pages in a user database, you may not be able to bring it back online. Only use this at home, in a dark room, alone, when not connected to your workplace, or anything you’ve ever cared about. Or anywhere near production, or anything important. This post gives you the tools in a simple, step by step fashion, to create different types of corruption so that you can practice resolving them.īig Disclaimer: Do not run this in production. When things go badly, you want to be prepared. You only want to do this on a test database, in a land far far away from your customers, for the purpose of practicing dealing with corruption. It is no fun at all on any data, or any server, that you care about. If you aren’t familiar with corruption, corruption is bad. Not how to fix anything, just how to break it. This Post Tells You How To Corrupt a SQL Server Database with a Hex Editor in Gruesome DetailĪnd that’s all this post tells you.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |