Post reply

How to corrupt table?

  • March 28, 2011 at 10:40 pm

    #233925

    Hi All,

    Hope that someone would be able to help me.

    As a part of DR strategy demonstration need to corrupt a table (just a table) in SQL Server 2008.

    Have a tool to corrupt a data file, but need to do it for a particular table only.

    Any advices?

    Many thanks.

    MB

    I am aware that there are many other scenarios/cases - need help just regarding this one.

    (not an exam, test or anything similar, just a bit of a strange request)

  • March 28, 2011 at 11:52 pm

    #1304680

    Take the database offline, open the mdf file in a hex editor. Search for a string you know to be in that table, overwrite with 0000000000. Save. Close hex editor, bring database online.

    NB: Never do this to a production database, never do this on a production server

    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

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

  • March 29, 2011 at 4:14 am

    #1304744

    @Gila,

    What it exactly happens inside sql server when your solution is implemented

  • March 29, 2011 at 4:21 am

    #1304747

    Nothing much. In what sense?

    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

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

  • March 29, 2011 at 5:41 am

    #1304769

    Thank you, it was exactly what I needed 🙂

    Have one more question (and will risk to sound stupid):

    I heard/read a few times so far that some people are suggesting, in case of a corrupted table, to BCP data out, recreate the table and BCP in.

    I believe that a good backup/restore and DR strategy is the only way to protect from damage caused by corruption. Full stop.

    However, I am quite curious to hear if anyone came across this (export/import of corrupted table)? How could export/import be possible if table is corrupted (not to say how it would help) at all?

    (If data is damaged - no sense; if table structure is damaged -how could it be exported in the first place?)

    Thanks

    MB

  • March 29, 2011 at 5:47 am

    #1304773

    ModestyBlaise84 (3/29/2011)

    I believe that a good backup/restore and DR strategy is the only way to protect from damage caused by corruption. Full stop.

    You would be correct there.

    However, I am quite curious to hear if anyone came across this (export/import of corrupted table)? How could export/import be possible if table is corrupted (not to say how it would help) at all?

    It's not. The export will fail as soon as it hits the bad page.

    Where export/import is indicated is when the corruption is irreparable (allocation pages, critical system tables, metadata) and there is no backup. It's not a full export/import, it's a case of save what you can and recreate the DB from what would extract and accept the loss of the rest.

    It's the last resort when nothing else will work and you want something back, even if not everything.

    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

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

  • March 30, 2011 at 4:29 pm

    #1305742

    Thank you

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply