I want to corrupt a database page, and restore the same page.
How do I do that?
I love this question!
To demo, I'm going to use a local copy of the Fitness database from the Stack Overflow data dump.
First, make sure your database is in FULL recovery, and take a full and log backup.
BACKUP DATABASE [FITNESS] TO DISK = N'D:\Backup\FITNESS.bak' WITH FORMAT, INIT, STATS = 1, COMPRESSION, CHECKSUM GO BACKUP LOG [FITNESS] TO DISK = N'D:\Backup\FITNESS.trn' WITH FORMAT, INIT, STATS = 1, COMPRESSION, CHECKSUM GO
Next, head into your database, and grab a page to corrupt. You should be able to use this query on 2012 plus. It creates a simple dynamic block with
USE FITNESS SELECT TOP 1 N'DBCC WRITEPAGE (' + CAST([dddpa].[database_id] AS NVARCHAR(10)) + N', ' + CAST([dddpa].[allocated_page_file_id] AS NVARCHAR(10)) + N', ' + CAST([dddpa].[allocated_page_page_id] AS NVARCHAR(10)) + N', ' + N'0' + N', ' + N'4' + N', ' + N'0x0000008A' + N', 1) WITH NO_INFOMSGS' AS [cmd] FROM [sys].[dm_db_database_page_allocations](DB_ID(), NULL, NULL, NULL, 'DETAILED') AS [dddpa] WHERE [dddpa].[allocated_page_page_id] > 250;
After that, you'll need to set your database to
SINGLE_USER and corrupt the page
ALTER DATABASE FITNESS SET SINGLE_USER WITH ROLLBACK IMMEDIATE DBCC WRITEPAGE ( INSERT YOUR DBCC WRITEPAGE COMMAND HERE ) ALTER DATABASE FITNESS SET MULTI_USER WITH ROLLBACK IMMEDIATE
Remember to take note of which page you're overwriting. For me, it was page 280. Now I can run a restore sequence like this:
USE master RESTORE DATABASE FITNESS PAGE='1:280' FROM DISK = N'D:\Backup\FITNESS.bak' WITH NORECOVERY; RESTORE LOG FITNESS FROM DISK = N'D:\Backup\FITNESS.trn' WITH NORECOVERY; BACKUP LOG [FITNESS] TO DISK = N'D:\Backup\FITNESS_1.trn' WITH FORMAT, INIT, STATS = 1, COMPRESSION, CHECKSUM, NORECOVERY RESTORE LOG FITNESS FROM DISK = N'D:\Backup\FITNESS_1.trn' WITH NORECOVERY; RESTORE DATABASE FITNESS WITH RECOVERY;
If you want more practice, I highly recommend Steve Stedman's database corruption challenge.
Make sure you have a good backup of your current testing database. This will be required when you need to restore back to a good version. Following the instructions in How to Deliberately Corrupt a SQL Server Database Using DBCC WRITEPAGE
There is a bold disclaimer at the very top of that post
Warning: Do not carry out any of the instructions in this post anywhere near a production server or even a development server with valuable databases on it. Following these instructions incorrectly could severely damage either a single database or an entire SQL Server instance
After you have completed your testing, restore the testing database from the backup you created.