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!