| Author |
Topic |
|
HOz
Starting Member
6 Posts |
 Posted - 2006-10-03 : 09:20:00
|
| Hi Guys,I'm trying to create a deliberate DB corruption (QA reasons) on one of my DBs (never mind which one, even the System ones will be ok), on SQL 2005 Ent. edition.Since I'm kinda new in the area, I'd appreciate any help you have to offer (query, etc...)Thanks in advanced, Oz |
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2006-10-03 : 09:49:19
|
| Backup first.Detach database.Download a hex editorRandomly hex edit some of the database fileRe-attach database.-------Moo. :) |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-10-03 : 10:05:39
|
Might not re-attach, depending where you happen to hex-edit it (but then if you take a safe-copy first you could always copy that back and hex-edit it somewhere else)If you can stop/start the SQL Service you could 1) Backup the database  2) Stop the SQL service3) Hex-edit the MDF file4) Restart the SQL serverwhich might be more tolerant of the corruption (there-again if its in a critical part of the file SQL Server will just mark the database as Suspect during its startup checks - but at least it will be "attached" and available for DBCC CHECKDB type tests of some sort.If you are wanting to try various disaster recovery scenarios I reckon you should be planning to:Make some updates to database"Break" a record (particularly one that you have just updated)Check that you get an error trying to retrieve that recordAttempt recovery by: 1) Make database inaccessible to applications (single user / DBO only / Read only / etc.)2) Make TLog final backup2a) DBCC CHECKDB to prove its detectably broken3) Restore Full backup + All Tlog backups (including one at Step-2)4) DBCC CHECKDBI guess it would be good to try damaging the LDF file too so that you can see what the scenario is when you can NOT make the final TLog backup - i.e. are you confident that you known the final TLog backup is hosed?next up I would try corrupting an Index block (not sure how you find one in your hex-editor ...), and then checking that you can Drop / Recreate the index and away you do with no data loss.Hex-editing a backup file and experiencing a restore that works but gives you a DBCC CHECKDB that fails would be a useful experience too.Final step is to post your findings here please  Kristen |
|
|
|
HOz
Starting Member
6 Posts |
Posted - 2006-10-03 : 10:36:59
|
| Hi mr mist, kirsten.Thanks for all your suggestions but I'm afraid some of them won't work for me.1. Attach / Detach actions are not optional.2. Stop / Start the SQL Service is not optional also.I need to corrupt the DB during work (Kirsten was right saying its a DR scenario testing).One last q.What do you mean by saying "Break a record"?do you want me to delete the last record I just updated? a short clarification will also be appreciated.Sorry for the basic questions...again, I'm kinda new in the area.Thanks a lot!Oz |
|
|
|
paulrandal
Yak with Vast SQL Skills
899 Posts |
Posted - 2006-10-03 : 10:51:41
|
| Are you on SQL Server 2000? In that case you could just go and delete some random records from the sysindexes table (making sure that this is a test database etc etc) which will break the database in such a way that only a restore will fix it.On SQL Server 2005 its much harder to corrupt a database online - there are tools we have internally but I'm not going to discuss them. What you could do is partition a database onto several drives and then pull out one of the drives - I've done that several times as a demo during DAT413 at TechEds in China and Hong Kong over the last few weeks (using USB keys as drives).ThanksPaul RandalLead Program Manager, Microsoft SQL Server Core Storage Engine (Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.) |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-10-03 : 11:01:52
|
| "1. Attach / Detach actions are not optional."I'm curious why that would be. And your point (2) also really. If the whole server cannot be taken offline for DR testing, if necessary, I'd be very worried about why not - it suggests that something "Live" is going on on that server! particularly as you were saying that you would be happy to corrupt a system database - maybe I'm missing something here?"What do you mean by saying "Break a record"?"I meant to hex-edit a specific, and known, data record - rather than its index entry or anything else. So that you could trial various get-out-of-jail methods for reinstating it. Hex-editing at a random address will probably break the file, but may not help you test out specific DR questions (such as "We know the corruption is in a table that only changes once a month, so we could just record that table from last nights backup and merge it into the current [DBCC cleaned]database")"... then pull out one of the drives"More fun with an 8 inch nail, isn't it Paul?!Kristen |
|
|
|
HOz
Starting Member
6 Posts |
Posted - 2006-10-03 : 11:23:06
|
| Hi Kristen,I think you are missing something here, but it seems to be my fault.What I meant by saying the Atch/Dtch and Stop/Start are not an option is because I want to simulate a DB corruption during a live work. basicly, I want to see if my application (Its a Sync & Replication app)can identify the corruption and stop the scenario on time without replicating the corruption.It doens't matter if it's a System DB or a user DB. a corruption is still a corruption and I need to catch it ASAP.I also know that stopping and starting the server will lead me to the same result, but I covered those isssues before.Paul, I liked your idea (It's a bit extreme :)) but i'm still looking for more suggestions (isn't there a certain query for that matter?)Thank you very much, for all your advises...Oz |
|
|
|
HOz
Starting Member
6 Posts |
Posted - 2006-10-03 : 11:27:23
|
| One more thing, just to calm everyone here...I'm doing all those tests on my Testing enviroment, not on my production one... |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-10-03 : 11:54:53
|
| "I want to see if my application (Its a Sync & Replication app)can identify the corruption and stop the scenario on time without replicating the corruption"That sounds pretty cool!Is there any way to hex-edit the MDF file whilst its in use by SQL Server?What is the MDF on? If its RAID5 you might get some joy by pulling a drive during a write (as Paul suggested) - I've had database corruption when a RAID5 drive went down because the controller card did not finish the write-cycle to the other drives in the RAID. RAID10 is probably not going to exhibit this behaviour - so I'm led to believe anyway!Kristen |
|
|
|
HOz
Starting Member
6 Posts |
Posted - 2006-10-04 : 03:37:22
|
quote:
Is there any way to hex-edit the MDF file whilst its in use by SQL Server?
Thats a good question...Unfortunatly I don't have the answer.Anyway, my MDF file (and all the other files for that matter) are on a simple HD drive (It's not RAID or anything) so unplugging the drive would be a bit difficult.Any other ideas?Thanks again...Oz |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-10-04 : 03:55:06
|
| Corrupt SQL Server's memory's cache of some objects?Provide a means of providing "fake" inputs to your detection program?Or adapt it so you can "stall" it whilst you take the DB offline, mangle it, and put it back online.Can't think of anything real-time though - I imagine that SQL Server has an exclusive lock on the MDB tables.Kristen |
|
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2006-10-04 : 04:07:08
|
| Unfortunately there is no "corrupt database" command that is easily available to use. Fun though that would be.Your best bet is likely to be a hot-remove-drive, or just push the power switch on the box in the middle of a complicated operation. That's fairly disaster-like.-------Moo. :) |
|
|
|
HOz
Starting Member
6 Posts |
Posted - 2006-10-04 : 04:16:00
|
| I had a bad feeling about that (I knew it would be difficult to do).Anyway, Thanks a lot Kristen & Paul & mr mist. I appreciate all the efforts you made.I think I'll adopt this forum with many questions I have about SQL. It's nice to get feedbacks and answers in minutes!Way to go!about the current problem, I'll go with the USB drive, and just unplug it. Lets hope it works...Oz |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-10-04 : 06:02:16
|
| I think the only thing you can achieve with unplugging is to create a torn page - only one of N blocks in a page gets written. SQL may just roll this back though as the transaction will be incomplete - whereas in a real disk failure the system would carry on without realising that a byte-level corruption had occurred.Worth a try though! Get it writing heavily when you pull the disk ...Kristen |
|
|
|
|
Create a deliberate DB corruption