4

I want to corrupt a database page, and restore the same page.

How do I do that?

6

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 DBCC WRITEPAGE

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.

Enjoy!

1

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.

  • 2
    "Oh, did I run this in production?!?!?!" – Max Vernon Mar 28 '17 at 19:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.