SQL Server

How to Deliberately Corrupt a SQL Server Database Using DBCC WRITEPAGE

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

SQL Server has a range of features that are used to detect, repair and recover from data corruption in a data file. The problem many DBA’s have is that their first exposure to these features is often when the corruption is happening for real.

Ideally we would like to be able to give these scenarios a run through in a more leisurely a relaxed environment! In this post I’m going to show you how to deliberately corrupt a test database so that you can get an understanding of how SQL Server will behave and so that you can run through your recovery process before it happens for real.

There are several ways to corrupt an SQL Server data file, including using a hex editor or low level file utility to open the file and slot in some jibberish. My preferred approach is to to use the undocumented DBCC WRITEPAGE command. Bear in mind – this command is undocumented for a reason – it is extremely dangerous. If you use it on a production server you are either:

1. A (Senior) Microsoft PSS specialist trying to perform an emergency data repair
2. An idiot

Microsoft PSS are the only people who can run this command without it implicitly invalidating your ability to get technical support.

How to Corrupt a Data Page

DBCC WRITEPAGE does exactly what it says on the tin. It allows you to directly write bytes to a given data file page via SQL Server whilst the database is still attached. Used correctly it can help us create a number of different corruption scenarios that will in turn allow us to practice repair and recovery in ways that would be difficult to otherwise simulate.

Step 1 – Create a sample database


USE master

GO

IF DATABASEPROPERTY('OhOh', 'Version') > 0
	DROP DATABASE OhOh;

GO

CREATE DATABASE OhOh

GO

USE OhOh

GO

CREATE TABLE tblWhoops(
	ID INT IDENTITY(1,1) ,
	Name VARCHAR(200) DEFAULT 'Data'
)

CREATE CLUSTERED INDEX ix_ID ON tblWhoops(ID)

GO

INSERT INTO tblWhoops DEFAULT VALUES
INSERT INTO tblWhoops DEFAULT VALUES
INSERT INTO tblWhoops DEFAULT VALUES
INSERT INTO tblWhoops DEFAULT VALUES
INSERT INTO tblWhoops DEFAULT VALUES

GO

Step 2 – Identify a Page to Corrupt

There are a couple of ways to identify a specific page to corrupt. One of the simpler ways is to use the DBCC IND command (again undocumented):

-- Output the page summary for the clustered index on tblWhoops (index id = 1)
DBCC IND (OhOh, 'tblWhoops', 1)
GO

The parameter list for DBCC IND is as follows:
DBCC IND ( {dbname}, {table_name},{index_id} )

The “magic number” 1 parameter indicates the index id you want to analyse. As long as you have a clustered index defined on your table, 1 will be fine.

This command will generate a description of the pages that make up our clustered index (and by extension, our table).

In this case we want the first page where the PageType is 1 (a Data page) and so that gives me a PageId of 93:

DBCC IND Index Page Summary

Step 3 – Corrupt the Page

Now all that remains for us to do is to use the DBCC WRITEPAGE command to bork our target page:

ALTER DATABASE OhOh SET SINGLE_USER WITH ROLLBACK IMMEDIATE

DBCC WRITEPAGE('OhOh', 1, 93, 60, 1, 0x00, 1)

With the command signature DBCC WRITEPAGE ({dbname | dbid}, fileid, pageid, offset, length, data [, directORbufferpool])

The final (optional) parameter directOrBufferpool is particularly interesting. If you pass 0, or don’t provide the value the data you provide will be saved to the buffer pool before being saved to disk.

This means that when the data page is then written out to disk it will be saved with a correct checksum (because this occurs whenever the buffer pool is flushed) *

If on the other hand you provide 1 as the directORbufferpool parameter – the data value will be written directly to disk without any opportunity to create a valid checksum for the page. This approach is much more akin to having a hex editor and doing a direct edit on the data file.

Because in our example we have provided 1 as our directOrBufferpool value, our change was made directly to the file on disk which now means the checksum on the page is almost certainly wrong. We can check this by doing a straight select of the table. This will cause the data page to be read into memory again, which will in turn cause the checksum to be verified:

SELECT * FROM tblWhoops

This will immediately fail and give us something along the lines of:

Msg 824, Level 24, State 2, Line 6
SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0x529d3500; actual: 0x529d352b). It occurred during a read of page (1:93) in database ID 7 at offset 0x000000000ba000 in file ‘C:\Program Files (x86)\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\OhOh.mdf’. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

Thats it – we’re now dealing with a corrupt database and a level 24 error!

From here you can play through a few different scenarios. Things you might want to look into:
– See how DBCC CHECKDB plays with the corruption.
– See if you can do a point in time recovery using transaction logs
– Automatic Page Repair via a database mirror
– Individual page recovery using RESTORE DATABASE <dbname> PAGE syntax

Good luck and have fun!

* NOTE: Checksums are enabled by default for databases created under SQL Server 2005 and later.

Advertisements

8 thoughts on “How to Deliberately Corrupt a SQL Server Database Using DBCC WRITEPAGE

  1. I followed up ur blog, now that particular table is not showing any results or data on select query. On select query, it shows error:
    Msg 5243, Level 22, State 8, Line 2
    An inconsistency was detected during an internal operation. Please contact technical support.
    Help please, how to resolve the issue ?

    1. Hey – I’m not sure what it is you want to resolve. The blog article teaches you how to deliberately corrupt a test database so that you can understand how SQL Server will behave with on disk corruption.

      How you resolve the issue is a very big topic and depends on numerous factors including your backup strategy and how quickly you require the data back. As I mentioned at the end of the post – you can now explore tools such as DBCC and the RESTORE command to attempt page level repairs.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s