In today’s blog posting I want to cover a very important topic that every DBA should know: how to perform a page level restore operation in SQL Server. Imagine that you have a corrupt page in SQL Server, and instead of restoring a complete database you only want to restore the page in question from the most recent database backup.

Let’s corrupt a page

In the first step I want to show you how to set up a scenario where one specific page in a table (or index) is corrupt. Of course, we have to perform some magic here because out-of-the-box SQL Server itself will not introduce any corruption (in that case you would have found a bug…). Let’s start by creating a new database and by populating a table within it with some records.

USE master
GO

CREATE DATABASE PageLevelRestores
GO

USE PageLevelRestores
GO

-- Create a table where every record fits onto 1 page of 8kb
CREATE TABLE Test
(
	Filler CHAR(8000)
)
GO

-- Insert 4 records
INSERT INTO Test VALUES (REPLICATE('A', 8000))
INSERT INTO Test VALUES (REPLICATE('B', 8000))
INSERT INTO Test VALUES (REPLICATE('C', 8000))
INSERT INTO Test VALUES (REPLICATE('D', 8000))
GO

-- Retrieve the selected records
SELECT * FROM Test
GO

In the next step I perform a full database backup. This means that this backup includes all the pages which belong to the table Test. This is very important, because in the next step we will corrupt one specific page of this table. To find out which pages belong to the table Test, I’m using the DBCC IND command that returns all pages for a specific table.

-- Perform a full database backup
BACKUP DATABASE PageLevelRestores TO DISK = N'g:\PageLevelRestores.bak'
GO

-- Retrieve the first data page for the specified table (columns PageFID and PagePID)
DBCC IND(PageLevelRestores, Test, -1)
GO

To corrupt an actual page, I’m using the undocumented command DBCC WRITEPAGE. Yes, there is a DBCC WRITEPAGE available in SQL Server, but please don’t tell this to anyone…

ALTER DATABASE PageLevelRestores SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

-- Let's corrupt page 301...
DBCC WRITEPAGE(PageLevelRestores, 1, 303, 0, 1, 0x41, 1)
DBCC WRITEPAGE(PageLevelRestores, 1, 303, 1, 1, 0x41, 1)
DBCC WRITEPAGE(PageLevelRestores, 1, 303, 2, 1, 0x41, 1)
GO

ALTER DATABASE PageLevelRestores SET MULTI_USER
GO

To use DBCC WRITEPAGE the database in question must be also set to Single-User mode as the code shows. I’m just simulating here some storage error, where the storage just writes some garbage to a page (yes, this can and even WILL happen sometimes in your career!) Now when you read from the table again, SQL Server will return an 824 I/O error, because the checksum validation for the corrupted page failed.

That is one good reason why you should never ever disable checksum validation in your databases (which is on by default since SQL Server 2005). Without checksum validation you can’t know immediately if a specific page got corrupted…

Msg 824, Level 24, State 2, Line 70
SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0xa0896eb3; actual: 0x00a96e93). It occurred during a read of page (1:303) in database ID 6 at offset 0x0000000025e000 in file ‘g:\MSSQL12.MSSQLSERVER\MSSQL\DATA\PageLevelRestores.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.

As soon as SQL Server detects a corrupted page during I/O access, the corrupted page is also logged in msdb.dbo.suspect_pages as you can see from the following picture.

It is also always a very good idea to monitor that specific table in msdb to get an idea of whether you have corrupt pages in your databases. And now to make things worse, the following script inserts one additional row into the table.

-- Now we have additional transaction that we don't want to loose...
INSERT INTO Test VALUES (REPLICATE('E', 8000))
GO

Let’s restore the corrupt page

You are now the DBA, and you want to restore this database into a good state without losing any data (like the record we have inserted in the last step). What do you do? As the first step you have to perform a so-called Tail-Log Backup: you are backing up the transactions that have occurred since the last transaction log backup.

-- Backup the transaction log
BACKUP LOG PageLevelRestores TO
	DISK = 'g:\PageLevelRestores_LOG1.bak'
	WITH INIT
GO

In our case no transaction log backup had been performed yet, so our backup will contain all the transactions that have been executed since the full backup. And now we can initiate the page level restore operation in SQL Server. You use the traditional RESTORE DATABASE T-SQL command here, but you only specify the page that you want to restore. Instead of restoring the whole database, we only restore the page in question. This would make a huge difference if you are dealing with quite a large database.

-- Restore full database backup
RESTORE DATABASE PageLevelRestores
	PAGE = '1:303'
	FROM DISK = 'g:\PageLevelRestores.bak'
	WITH NORECOVERY
GO

And now the tricky part begins: after the RESTORE DATABASE T-SQL command you have to perform another transaction log backup. This additional log backup is needed, because then you are guaranteed to have all the changes that you have performed on this page available for the restore. Without this additional log backup SQL Server is not able to bring your page online again.

-- Backup the tail of the log...
BACKUP LOG PageLevelRestores TO
	DISK = 'g:\PageLevelRestores_LOG_TAIL.bak'
	WITH INIT
GO

After performing this additional log backup you can finally restore all the log backups in the correct order and finally bring the database online.

-- Restore all available log backups in the correct order
RESTORE LOG PageLevelRestores FROM
	DISK = 'g:\PageLevelRestores_LOG1.bak'
	WITH NORECOVERY
GO

-- Finally restore the tail log backup
RESTORE LOG PageLevelRestores FROM
	DISK = 'g:\PageLevelRestores_LOG_TAIL.bak'
	WITH NORECOVERY
GO

-- Finally finish with the restore sequence
RESTORE DATABASE PageLevelRestores WITH RECOVERY
GO

When you now query the table again, you can see that the SELECT statement succeeds without any I/O errors, and that you haven’t lost any data in this table. Almost easy, isn’t it?

Summary

How to perform page level restore operation in SQL Server is something that every DBA should know. It is one of the most important things in your toolset – especially when you work with quite large databases. Instead of restoring the complete database, you just restore the page in question, and the whole recovery process will be finished quite fast.

And finally a question to you, my reader: have you ever already needed to perform a page level restore in SQL Server, and if yes – how easy/hard was it for you? Please feel free to leave a comment.

Thanks for your time,

-Klaus