SQLTop*, lightweight, simple and easy to use.

When a performance issue occurs, SQLTop* will show quick and easy entire SQL Server Performance on real-time, including waits, performance counters and sessions.

Source : http://www.sqlpassion.at/archive/2015/10/13/how-to-perform-a-page-level-restore-in-sql-server/

How to perform a Page Level Restore in SQL Server

October 13, 2015 · Klaus Aschenbrenner · 9 Comments

(Be sure to checkout the FREE SQLpassion Performance Tuning Training Plan - you get a weekly email packed with all the essential knowledge you need to know about performance tuning on SQL Server.)

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.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26

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.

1 2 3 4 5 6 7

-- 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 commandDBCC WRITEPAGE. Yes, there is a DBCC WRITEPAGE available in SQL Server, but please don’t tell this to anyone…

1 2 3 4 5 6 7 8 9 10 11

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 toSingle-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 msdbto 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.

1 2 3

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

1 2 3 4 5

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

1 2 3 4 5 6

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

1 2 3 4 5

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

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