Using undocumented DBCC WRITEPAGE to instigate SQL Server database corruption

By: Simon Liew   |   Updated: 2018-09-28   |   Comments (2)   |   Related: > Database Console Commands DBCCs

Problem

I have heard that the undocumented DBCC WRITEPAGE can be used to instigate database corruption. Is this command to corrupt database one-way and irrecoverable? Assuming I know the state prior to the corruption, can I use this to reverse the corruption?

Solution

DBCC WRITEPAGE is a very dangerous undocumented DBCC command. It is useful if you want to use the command to practice data corruption and recovery. Always make sure to only practice this in secluded R&D sandboxes.

In nutshell, you can recover from (accidental) corruption, but only if you know what you have corrupted and note that this is undocumented and unsupported by Microsoft. This tip will step through a demonstration to corrupt a record, and revert by putting back the original value.

SQL Server Corruption Demonstration

You can download a good backup of [CorruptionTest] database. This will allow you to step through this tip with the same code. The database backup can be restored on version SQL Server 2016 and higher (any edition).

Database [CorruptionTest] contains a single table [dbo].[mssqltips] populated with randomly generated data.

The query below outputs the top 10 rows from table [dbo].[mssqltips]. In this demonstration, we (randomly) pick the fifth row and column [randomValue] for our corruption demo.

SELECT TOP 10
   sys.fn_PhysLocFormatter(%%physloc%%) PageId,
   *
FROM [CorruptionTest].[dbo].[mssqltips]
GO

Corrupting a Row in a SQL Server Data Page

Using DBCC PAGE, we can inspect data page id 258 in detail. In the page dump output, we will look for Slot 1 and Column 3. Note the original value stored here is 37444641.

To over-write Slot 1 Column 3 in page id 258, we need to calculate its column offset which is 0x84f + 0x18 (2127 + 24) = 2151.

DBCC TRACEON (3604);
GO
DBCC PAGE ('CorruptionTest', 1, 258, 3);
GO

WARNING!! Using the undocumented command DBCC WRITEPAGE, will over-write [randomValue] with another number, say 95868367. The input parameters for DBCC WRITEPAGE are described in the table below.

Syntax:

DBCC WRITEPAGE (databasename, fileid, pageid, offset, length, data, directORbypassbufferpool)

The input parameter [offset] and [data] are in hexadecimal. We have already calculated the offset. For conversion to hexadecimal, we can execute the query below to convert 95868367 to hexadecimal, which returns 0x05B6D5CF.

SELECT CONVERT(VARBINARY(8), 95868367)
GO
----------------------------------------------
0x05B6D5CF

The input value for [data] needs to be byte-reverse, which converts it to 0xCFD5B605. We want to replace the whole content of Column 3, hence the value for [length] would be 4 (4 bytes for data type integer). Setting directORbypassbufferpool to 1 is akin to simulating a corruption on an I/O subsystem as the buffer pool would not be aware of this change and the page checksum is not recalculated.

USE master;
GO  
ALTER DATABASE [CorruptionTest] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
DBCC WRITEPAGE ('CorruptionTest', 1, 258, 2151, 4, 0xCFD5B605, 1)
GO
ALTER DATABASE [CorruptionTest] SET MULTI_USER;
GO

We'll now inspect the data page 258 again with DBCC PAGE command and look at the page dump output. As expected, the value in Slot 1 Column 3 has been overwritten to 95868367.

DBCC PAGE ('CorruptionTest', 1, 258, 3);
GO

When we execute the initial query which will access the corrupted page id 258, we will get the error below.

SELECT TOP 10
   sys.fn_PhysLocFormatter(%%physloc%%) PageId,
   *
FROM [CorruptionTest].[dbo].[mssqltips]
GO

Revert the SQL Server Corruption

To revert the corruption, we will again overwrite Slot 1 Column 3 in page 258 with its original value. The query below calculates the hexadecimal for integer 37444641.

SELECT CONVERT(VARBINARY(8), 37444641)
GO
----------------------------------------------
0x023B5C21

After byte-reversal, the hexadecimal would be 0x215C3B02. The input parameters to DBCC WRITEPAGE are the same except the input for parameter [data] would now be the original value for randomValue of 37444641.

USE master;
GO  
ALTER DATABASE [CorruptionTest] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
DBCC WRITEPAGE ('CorruptionTest', 1, 258, 2151, 4, 0x215C3B02, 1)
GO
ALTER DATABASE [CorruptionTest] SET MULTI_USER;
GO

Using DBCC PAGE, we inspect page id 258 again and we can confirm randomValue is back to its original value.

DBCC PAGE ('CorruptionTest', 1, 258, 3);
GO

Now if we re-execute the initial query, it will return the result successfully without error.

To doubly confirm, we'll perform a database integrity check and the output returns successful.

Summary

In a real-life situation, recovering from corruption in SQL Server is very complicated using DBCC WRITEPAGE, not to mention it is unsupported. In addition, it is very hard to distinguish a corrupted versus original value as shown in this tip since they both are integer and are legitimate data types in SQL Server.

If you have a good backup regime and happen to be on the Enterprise Edition, you can recover from corruption while the database is online and in a supported manner. My next tip will demonstrate how easy SQL Server has made it to perform an online page restore to fix page level corruption.

Next Steps

• Database Corruption Part 1 - Introduction to Database Corruption in SQL Server
• DBCC CHECKDB (Transact-SQL)
• How to use the SQL Server sys.fn_PhysLocFormatter undocumented function
• Using DBCC PAGE to Examine SQL Server Table and Index Data

About the author

Simon Liew is an independent SQL Server Consultant in Sydney, Australia. He is a Microsoft Certified Master for SQL Server 2008 and holds a Master’s Degree in Distributed Computing.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips