Post reply

How do I corrupt a page in a database?

  • March 14, 2009 at 9:19 am

    #127036

    Im studying for my 70-432 exams, and so that i dont become a paper dba, im going through all the examples, so at least i can say i have some hands on experience.

    Im just finished the backup/restore chapter and want to do some examples and tests on that.

    One of the topics is a page restore. The thing is though, my test db on the laptop is in perfect health.

    I want to corrupt a page so that i can see what error is presented, and can then restore it.

    Can anyone describe how i would damage an individual page for the purposes of this test?

  • March 14, 2009 at 9:29 am

    #959723

    Disclaimer: Do not do this to a production database. Do not do this to any database that you care about.

    Right, now that's out of the way.

    Detach the database, open the mdf file in a hex editor. Go a good way into the file to avoid the system tables and then write zeros over a small section of the file. Reattach the database to SQL and (if it attaches successfully) run checkDB to see the results of your work.

    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 14, 2009 at 9:38 am

    #959729

    http://www.sqlskills.com/BLOGS/PAUL/post/Conference-corruption-demo-scripts-and-example-corrupt-databases.aspx

    http://www.sqlskills.com/BLOGS/PAUL/post/TechEd-Demo-Creating-detaching-re-attaching-and-fixing-a-suspect-database.aspx

  • March 14, 2009 at 10:54 am

    #959746

    great stuff. thanks guys.

  • March 14, 2009 at 2:41 pm

    #959795

    If you don't have a hex editor, Tony Rogerson blogged about another way to do it.

    http://sqlblogcasts.com/blogs/tonyrogerson/archive/2007/03/10/how-to-create-a-corrupt-database-using-bulk-insert-update-and-bcp-sql-server-as-a-hex-editor.aspx


    Check Your SQL Servers Quickly and Easily
    www.sqlcopilot.com

  • March 15, 2009 at 4:55 am

    #959884

    So I detached my db, and using a hex editor i zeroed out a few entries.

    when i try to atach the db i get this error:

    Msg 824, Level 24, State 6, Line 1

    SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0x7a661c45; actual: 0x7a581c45). It occurred during a read of page (0:0) in database ID 0 at offset 0000000000000000 in file 'C:\DatabaseFiles2\SecondryDF3.ndf'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

    So, SQLServer wants me to do a DBCC Checkdb on an unatached database. does this mean there's a way to attach a corrupd database, or in this suituation are backups your only option.

  • March 15, 2009 at 5:18 am

    #959885

    It means that you corrupted a page that gets checked during the attach. That's why I said "if it attaches successfully". Restore a backup, detach and write over in a different place. I've found that scribbling over a few bytes at an offset of a couple MB into the mdf is 'safe' (the database attaches)

    If the error message is correct, the header page of a secondary data file is damaged (offset 0). Damaged header pages are irreparable by any means and the only fix is a full DB (or file/filegroup) restore.

    As for the error, that's a generic logical IO error and is returned whenever SQL detects a logical IO error, regardless of the situation.

    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 15, 2009 at 9:32 am

    #959908

    If you don’t have a backup of this database, you can modify the files’ names, then create a new database with the same name and files’ structure as the old database (including the original database’s files’ names). Then stop the server, delete the new files and change the name of the original files to the original name. When you’ll start the service, the database will be suspect. I have to admit that I never did it my self, I only read about it, and I don’t see a reason that it shouldn’t work.

    Adi

    --------------------------------------------------------------
    To know how to ask questions and increase the chances of getting asnwers:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

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

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