Well over the last few weeks I have be busy working on a workshop on how to Backup and Restore your database. I cannot tell you how much respect I now have for people who write the classes from square one. There is a ton of time here. As part of my workshop I described some methods to get around restoring a database. Things we covered were moving data from a warm stand by database such in the case of T-log Shipping and such. What I really found to be of great interest was the section that I did on DBCC CheckDB with the allow data loss options.
Well the only way to show how this works is to corrupt a database. I have never done that, well I never did it on purpose. I remember back in the 6.5 days of SQL Server corrupting them all the time, however never intentionally. How did I do it? Well it really was not as hard as I thought it was, I found a blog entry that helped me identify page numbers that a index was on. The blog that I was looking at referred to Kalen Delany’s books on how he was able to find those page numbers. But once I had the page numbers it was not difficult at all. I downloaded a Hex Editor Did a search for the page number and started to replace the values in the opened .mdf file. Once I completed that I restarted my SQL Server did a DBCC and the database was Corrupt.
This worked as a great tool for allowing me to demo how to get around such a problem. And a problem that I have seen before, I cannot tell you how many corrupt indexes I have seen and a DBA’s that I was working with would want to restore the database.
If you are interested in attending the Workshop on a Deep Dive in SQL Server 911 Backup’s and Restores. You can register here. The workshop lasts 4 hours and 37 minutes without breaks. It takes place this Friday on June 5th. I will be in the chat room to answer as many questions as I can.