Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Page Restore Expand / Collapse
Author
Message
Posted Wednesday, June 26, 2013 2:08 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 9:47 AM
Points: 357, Visits: 914
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
Post #1467499
Posted Wednesday, June 26, 2013 3:03 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:02 AM
Points: 39,866, Visits: 36,207
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1467521
Posted Wednesday, June 26, 2013 3:28 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 9:50 AM
Points: 5,367, Visits: 9,913
Tony Rogerson also covers this in detail in his blog.

John
Post #1467530
Posted Wednesday, June 26, 2013 6:41 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 9:47 AM
Points: 357, Visits: 914
Thanks guys...two different approaches. I'll try and have a go at each and post back my success rate.

Again, thanks
Post #1467629
Posted Tuesday, July 2, 2013 8:39 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 9:47 AM
Points: 357, Visits: 914
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
Post #1469573
Posted Tuesday, July 2, 2013 8:50 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:02 AM
Points: 39,866, Visits: 36,207
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1469577
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse