Corrupting a database page

To corrupt a database page for the purpose of testing DBCC and page restores etc, this is the process I use which I find easiest…

1. Download a copy of AdventureWorksLT2008 sample database… http://msftdbprodsamples.codeplex.com/releases/view/93587

2. Attach the database to your SQL Server instance (run SSMS as administrator)

EXEC sp_attach_db AdventureWorksLT2008
, 'D:\MSSQL\Data\AdventureWorksLT2008_Data.mdf'
, 'D:\MSSQL\Data\AdventureWorksLT2008_Log.ldf'

3. Take a backup of the database so that you have something to restore (unless you have another copy of the files)

BACKUP DATABASE AdventureWorksLT2008
TO DISK = 'D:\MSSQL\Backup\bkup_AdventureWorksLT2008.bak'
WITH init

4. Find a page in the database to corrupt

To do this run the following command:

DBCC IND (AdventureWorksLT2008, 'SalesLT.Customer', 4 )

This will return all the database pages used by the SalesLT.Customer table and 4 is the index id. See another post on using DBCC IND here… http://www.sqldba.co.nz/blog/?p=62

For this example, I chose page 636.

5. Put the database into a state where you can perform page restores i.e. Full recovery (only necessary if you want to do this)

ALTER DATABASE AdventureWorksLT2008 SET COMPATIBILITY_LEVEL = 100
ALTER DATABASE AdventureWorksLT2008 SET RECOVERY FULL
ALTER AUTHORIZATION ON DATABASE::AdventureWorksLT2008 TO sa

6. Download a HEX editor for the purpose of corrupting a page. I use XVI32 which is here…

http://www.chmaas.handshake.de/delphi/freeware/xvi32/xvi32.htm

This is a stand-alone application that doesn’t get installed – just run the .exe

7. Take AdventureworksLT2008 database offline so you can edit the mdf

ALTER DATABASE AdventureWorksLT2008 SET OFFLINE

8. Run XVI32.exe

9. Open the AdventureWorksLT2008 main data file (.mdf)

10. Go to the page that you want to corrupt. Do this by select Address on the menu and Goto. The location that you want to go to is the page number (which is 636) multiplied by 8192 which equals 5210112

11. Corrupt the page by entering 00 in the byte.

12. Save the change to the .mdf

13. Bring the database back online

ALTER DATABASE AdventureWorksLT2008 SET ONLINE

14. Run CheckDB to highlight the corruption

DBCC CHECKDB (AdventureworksLT2008) WITH NO_INFOMSGS, ALL_ERRORMSGS

15. Note the output which will have page 636 in the error message or lookup the corruption in the suspect_pages table…

SELECT * FROM msdb..suspect_pages

From here you can perform a page restore or do what ever you like.

This entry was posted in Corruption. Bookmark the permalink.