Corruption demo databases and scripts

I originally blogged a series of corruption demos and associated databases back in 2008, for use with SQL Server 2005 and 2008. Since then the releases have changed which databases and corruptions work and I’ve had to rework some of the databases for you. This is an update that takes into account SQL Server 2008R2 and SQL Server 2012 and sets out everything clearly.

I’m sure a bunch of you have never seen these – so something new for you to play with!

The databases zips are:

  • example corrupt databases (36-MB zip) containing:
    • DemoDataPurity
      • 192-MB SQL Server 2005 database with a single 2570 (data purity) error
      • Works on 2005 and will upgrade and work on 2008, 2008R2, and 2012
    • DemoFatalCorruption1
      • 1-MB SQL Server 2005 database with a corrupt system table (that allows CHECKDB to complete)
      • Only works on 2005
    • DemoFatalCorruption2
      • 1-MB SQL Server 2005 database with a corrupt system table (that terminates CHECKDB)
      • Only works on 2005
    • DemoNCIndex
      • 192-MB SQL Server 2005 database with a bunch of nonclustered index corruptions
      • Works on 2005 and will upgrade and work on 2008, 2008R2, and 2012
    • DemoCorruptMetadata
      • 1-MB SQL Server *2000* database with corrupt syscolumns table
      • Upgrades and works on 2005, 2008, and 2008R2
  • 2008 fatal corruptions databases (232KB zip) containing:
    • DemoFatalCorruption1
      • 1-MB SQL Server 2008 database with a corrupt system table (that allows CHECKDB to complete)
    • DemoFatalCorruption2
      • 1-MB SQL Server 2008 database with a corrupt system table (that terminates CHECKDB)
  • 2008R2 fatal corruptions databases (260KB zip) containing:
    • DemoFatalCorruption1
      • 1-MB SQL Server 2008R2 database with a corrupt system table (that allows CHECKDB to complete)
    • DemoFatalCorruption2
      • 1-MB SQL Server 2008R2 database with a corrupt system table (that terminates CHECKDB)
  • 2008R2 corrupt metadata database (260KB zip) containing:
    • DemoCorruptMetadata
      • 1-MB SQL Server 2008R2 database that was upgraded from a 2000 database with a corruption syscolumns database
      • Works on 2008R2 and upgrades and works on 2012

Note that I have not created DemoFatalCorruption databases yet for 2012.

The scripts zip contains the following directories:

  • 1 – Fatal Errors
    • This makes use of the DemoFatalCorruption1 and DemoFatalCorruption2 databases. The FatalErrors.sql script has the steps to follow and see this blog post for a complete walk-through.
  • 2 – NC Indexes
    • This makes use of the DemoNCIndex database. The NCIndexCorruption.sql script has the steps to follow and see this blog post for a complete walk-through.
  • 3 – Data Purity
    • This makes use of the DemoDataPurity database. The DataPurityCorruption.sql script has the steps to follow. This has a database that generates a 2570 error and then walks you through fixing it.
  • 4 – Metadata
    • This makes use of the DemoCorruptMetadata database. The CorruptMetadata.sql script has the steps to follow and see this blog post for a complete walk-through. If you’re on 2012, you’ll need the 2008R2 corrupt metadata backup to restore from.
  • 5 – Restore or Repair
    • There’s a setup script (01CreateRestoreOrRepair.sql) and two demo scripts – FixUsingPageRestore.sql and FixUsingRepair.sql. There are script to use for 2005-2008R2 and for 2012. This creates a database with corruption and then shows fixing it using a page restore and then using repair and salvaging data from an older backup.
  • 6 – Suspect Database
    • This demo doesn’t come with a corrupt database – you create your own one. The SuspectDatabase.sql script has the steps to follow and see this blog post for a complete walk-through.

I use a whole bunch more demos and scripts when I’m teaching about corruption, but these are the ones I’ve always made completely public.

Enjoy!

25 thoughts on “Corruption demo databases and scripts

  1. Hi Paul –

    I have a DB that uses file stream, I get below error when tried to REPAIR even with allow data lose. Any idea how to fix it?

    Msg 2576, Level 16, State 1, Line 1
    The Index Allocation Map (IAM) page (0:0) is pointed to by the previous pointer of IAM page (1:204) in object ID 0, index ID -1, partition ID 0, alloc unit ID 72057594054901760 (type Unknown), but it was not detected in the scan.
    CHECKDB found 1 allocation errors and 0 consistency errors not associated with any single object.

  2. The repair data loss stated it corrected 1 allocation problem, but when I reran the DBCC cehckdb, it still gave the same error. I created a new database, copied theobjects using generate script task and ran checkdb, this time I did not get an error. The only issue I noticed is some of the external files were not copied, so I guess I have to use detach/attach to move the files. Please advise.

    Thank you so much for your reply.

  3. Yes, I looked into your demo DBs, and could not find any. I had to create a new database, alter it to point to the files from the one which was giving the error.
    Thanks a lot for your great blog.

  4. Dear Paul,

    I was able to repair your DemoFatalCorruption1 for SQL 2008. Output stating gave me: repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (DemoFatalCorruption1).

    Most of the errors refer to NonClustered indexes on system tables – is it possible to rebuild them?

    Except of one: Object ID 0, index ID -1, partition ID 0, alloc unit ID 0 (type Unknown): Page (1:54) could not be processed. See other errors for details.

    How should I interpret this? What could I lose repairing it with allow data loss?

    Many thanks.

  5. I am working on the “DemoRestoreorrepair” to try DBCC repair.
    and in the script “FixUsingRepair.sql” you say we can extract the missing rows from the corrupt database backup. But I can’t archeive that.
    INSERT INTO DemoRestoreOrRepair.dbo.sales (
    salesID, customerID, salesDate, salesAmount)
    SELECT * FROM DemoRestoreOrRepairCopy.dbo.sales AS copy
    WHERE copy.salesID > X AND copy.salesID < Y;
    GO still has problem.

  6. I know what’s happening. There should be an backup DemoRestoreOrRepair.bak.
    Are there download from your website about for the bak?

  7. Hi paul,

    I’ve encoutered a corruption on a production database. Fortunately only one data page was corrupted:

    Msg 8928, Level 16, State 1, Line 1
    Object ID 1333579789, index ID 0, partition ID 72057594041335808, alloc unit ID 72057594057654272 (type In-row data): Page (1:6196) could not be processed. See other errors for details.

    I’ve restored the last backup (simple mode recovery) and found out the data that were into the corrupted page by using DBCC pages (actually there was only four rows into the page : m_SlotCnt = 4).
    I’ve found the primary key of the records into the page, then identified the missing rows by a SELECT of the primary key values into the restored and clean database.
    Now I’ve the records concerned by the corruption, I’ve launched a CHECKDB REPAIR_ALLOW_DATA_LOSS on the corrupted database as I was thinking that the corrupted page would be dropped.
    Here is the result :
    Repair: The page (1:6196) has been deallocated from object ID 1333579789, index ID 0, partition ID 72057594041335808, alloc unit ID 72057594057654272 (type In-row data).

    A second checkDB give me no error any more, the database is now clean.

    But it seems that actually the corrupted page has not been dropped. And if I try a DBCC PAGE on the corrupted page, I still have the error :
    Msg 2514, Level 16, State 5, Line 2
    A DBCC PAGE error has occurred: Invalid page type – dump style 3 not possible.

    But DBCC CHECKDB is now OK.
    So I wonder what exactly does ALLOW_REPAIR_ALLOW_DATA_LOSS, and how to control his action ?
    Is there a problem having such corrupted page into the database ? How to drop it ?

    Many thank’s for your advices…

    O.

  8. Hi Paul

    With regards to the RestoreOrRepair scenario, when choosing the repair option. What would happen if more than 1 page was corrupt. ie when finding the range of missing data, how would that part of the script work?

  9. Hi Paul,
    I have few queries it is not related with corrupt demo databases but some points I have Googled for suspect database, it is in that regards.

    As I know the database should be restored from backup and logs reapplied. That’s the first and generally recommended approach any time a DB is suspect, whether it’s from log or data file corruption.

    But if I have no backups then we follow below steps ( after Googling I found these steps on most of the websites ):

    1.EXEC sp_resetstatus ‘yourDBname’;
    2.ALTER DATABASE yourDBname SET EMERGENCY
    3.DBCC checkdb(’yourDBname’)
    4.ALTER DATABASE yourDBname SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    5.DBCC CheckDB (’yourDBname’, REPAIR_ALLOW_DATA_LOSS)
    6.ALTER DATABASE yourDBname SET MULTI_USER

    Now my question is,
    1. If after executing DBCC CHECKDB at 3rd step, it shows inconsistency.Then I will execute SET SINGLE USER WITH ROLLBACK & if after executing DBCC CHECKDB at 3rd step, no errors found. Then what will be the steps?

    2. When we execute DBCC CHECKDB (‘ DBNAME ‘, REPAIR_ALLOW_DATA_LOSS) how can we recover lost data during process.As far as I know some data will loss and if it will happen then business will get suffered.

    3. Why run DBCC CHECKDB() WITH REPAIR_ALLOW_DATA_LOSS if there are no consistency errors in the database? as per steps which I Googled.

    I am stucked in these questions….
    I am very grateful to you if you can clear my doubt…

    1. Step number 1 is useless from SQL Server 2000 onward.

      1) Then you have transient corruption problems with your I/O subsystem.
      2) You mostly can’t – that’s the point of having backups so you don’t lose data by having to run repair. You may be able to get some stuff back – see my Advanced Corruption Recovery Techniques course on Pluralsight.
      3) You can’t trust everything you read online. Steps 4-6 are unnecessary if no corruption is found.

  10. Are you by any chance working on versions of corrupt databases for CHECKDB for SQL 2016 & 2017? The 2008R2 examples won’t restore on SQL 2017 but will on SQL 2016 though running CHECKDB against them on 2016 doesn’t cause CHECKDB to fail but it does produce an informational message even with NO_INFOMSGS.

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.