Using undocumented DBCC WRITEPAGE to instigate SQL Server database corruption


By:   |   Updated: 2018-09-28   |   Comments (2)   |   Related: More > 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
sql server physical locatoin formatter

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
dbcc page output

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)
Parameter Description
[databasename] Name of the database
[fileid] File identifier of the database data file
[pageid] Page identifier number
[offset] Starting position of the data to change
[length] Number of bytes to write to the page
[data] Data to be written in hexadecimal format. The hexadecimal here correlate to the [length] of bytes that you want to overwrite, and need to be byte-reverse
[directORbypassbufferpool] 0 – data modification in buffer pool. This will trigger page checksum to recalculate 1 – Write data to disk without going to buffer pool. Page checksum is not triggered because buffer pool is not aware of this change.

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
dbcc page output

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
sql server physical locatoin formatter error

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
dbcc page output

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

sql server physical locatoin formatter

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

dbcc checkdb output

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


Last Updated: 2018-09-28


get scripts

next tip button



About the author
MSSQLTips author Simon Liew 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.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





Wednesday, October 03, 2018 - 9:10:23 AM - Simon Liew Back To Top

 Hi Lukse,

Yes, this action is logged in the SQL Server error log. Anyone who has executed this command will have its user name and the page modified written to the error log.


Wednesday, October 03, 2018 - 4:51:40 AM - Lukse Back To Top

 

Are these actions logged anywhere? Is it possible to see who executed the statements?



download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools