June 26, 2013 at 2:08 am
Hi experts, whats the best / easiest way for me to actually corrupt a page in my database (on local test machine) in order for me to then identify it using CHECKDB and practice a page restore scenario?
Thanks
June 26, 2013 at 3:03 am
Hex editor works.
Pick a page you want to corrupt, identify an allocated page using DBCC IND for a table and cacluate the offset as (page Number)*8192 bytes
Take the DB offline, open the data file in a hex editor (HxD is good), go to the offset calculated, write garbage over a few bytes, save, exit
Bring the DB back online.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 26, 2013 at 6:41 am
Thanks guys...two different approaches. I'll try and have a go at each and post back my success rate.
Again, thanks 🙂
July 2, 2013 at 8:39 am
Gail that worked a charm. I followed the steps you outlined:
Identify Page (using DBCC IND)
Took DB offline
Opened MDF in HxD and navigated to page(offset)
Added some garbage text and save
Brought the DB back online and ran DBCC CHECKDB (this clearly reported an issue)
Steps to remedy:
Back up tail of the log
restore DB page (the one I trashed) with norecovery
restore all logs and the final tail backup
DBCC CHECKDB reports no issue and the table contains the data I expected.
That was great exercise to run on my test DB and feel it's added a little something to my skillset!! I'll play about with different options (repair_allow_data_loss) etc just to see what happens.
Again, thanks Gail
July 2, 2013 at 8:50 am
Excellent!
<Shameless self-promotion> There's a chapter on page restores in SQL Server MVP Deep Dives 2 </Shameless self-promotion>
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply