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)
| 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
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
