This Post Tells You How To Corrupt a SQL Server Database with a Hex Editor in Gruesome Detail
And that’s all this post tells you. Not how to fix anything, just how to break it. If you aren’t familiar with corruption, corruption is bad. It is no fun at all on any data, or any server, that you care about.
Where You (Possibly) Want To Do This
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. When things go badly, you want to be prepared. 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. Big Disclaimer: Do not run this in production. Or anywhere near production, or anything important. Ever. Only use this at home, in a dark room, alone, when not connected to your workplace, or anything you’ve ever cared about. If you corrupt the wrong pages in a user database, you may not be able to bring it back online. If you corrupt a system database, you may be reinstalling SQL Server.
Everything I’m doing here I learned from Paul Randal’s blog posts. 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. If you’d like to go straight to the source:
Common bad advice around disaster recovery ← Search for “the best way to create a test corrupt database”
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
Creating, detaching, re-attaching, and fixing a suspect database ← This shows how to use the hex editor to corrupt the log file. That’s different than what I’m doing in this post, but you should test that also. This post is where I got the recommendation to use the XVI32 Hex editor.
You can also download Sample corrupt databases to play with from SQLSkills if you prefer not to run with scissors.
And that’s all this post tells you. Not how to fix anything, just how to break it. If you aren’t familiar with corruption, corruption is bad. It is no fun at all on any data, or any server, that you care about.
Where You (Possibly) Want To Do This
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. When things go badly, you want to be prepared. 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. Big Disclaimer: Do not run this in production. Or anywhere near production, or anything important. Ever. Only use this at home, in a dark room, alone, when not connected to your workplace, or anything you’ve ever cared about. If you corrupt the wrong pages in a user database, you may not be able to bring it back online. If you corrupt a system database, you may be reinstalling SQL Server.
References, and Thanks to Paul Randal
Everything I’m doing here I learned from Paul Randal’s blog posts. 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. If you’d like to go straight to the source:
Common bad advice around disaster recovery ← Search for “the best way to create a test corrupt database”
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
Creating, detaching, re-attaching, and fixing a suspect database ← This shows how to use the hex editor to corrupt the log file. That’s different than what I’m doing in this post, but you should test that also. This post is where I got the recommendation to use the XVI32 Hex editor.
You can also download Sample corrupt databases to play with from SQLSkills if you prefer not to run with scissors.
First, Get Your Hex Editor
Download XVI32 by Christian Maas. No installer is necessary: download the zip file, then unzip all files to a directory and run XVI32.exe
Download XVI32 by Christian Maas. No installer is necessary: download the zip file, then unzip all files to a directory and run XVI32.exe
Create a Database to Corrupt
For our adventure, our database is named CorruptMe. We’ll create a single table, insert some data, and create a clustered index and nonclustered index on it. (Note: Data generation technique found on Stack Overflow, attributed to Itzik Ben-Gan.) Read more: SQL Server Pedia
For our adventure, our database is named CorruptMe. We’ll create a single table, insert some data, and create a clustered index and nonclustered index on it. (Note: Data generation technique found on Stack Overflow, attributed to Itzik Ben-Gan.) Read more: SQL Server Pedia
Posts
0 comments:
Post a Comment