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…
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.