Prashanth Jayaram

How to perform a page level restore in SQL Server

May 18, 2018 by




In this series we will discuss the following topics:

  1. Introduction to page-level restore
  2. Different ways the data gets corrupted
  3. How to deal with corruption
  5. And more…


This entire illustration is based on the ProdSQLShackDemo database. Let’s walk through the following SQL to get a better understanding of the page-level restoration concepts. Here are the steps involved:

  1. Create the database, ProdSQLShackDemo
  2. Switch the database context to ProdSQLShackDemo
  3. Change the recovery mode of the ProdSQLShackDemo to FULL
  4. Create the table, SQLShackAuthor, and load some test data
  5. Backup the database, ProdSQLShackDemo

Let’s get straight to it….

Corruption is often very arbitrary; there are many things that can be the cause. Let’s use a tool to identify the corruption, in this demo. There are two tools that can help us with this:

  2. XVI32 by Christian Mass, a simple Hex editor freeware tool. You just need to download, extract and run the XVI32.exe

To understand the internals of how the data is organized, we can use the undocumented DBCC IND and DBCC PAGE database consistency checker commands.


This Database Consistency Checker command lists all the data and index pages. The command returns the information about the actual pages that are being used in the database.

This command requires three arguments:

  1. Database name (ProdSQLShackDemo in our case)
  2. Table name (SQLShackAuthor in our case)
  3. Index ID
    1. -1 list everything (indexes +Index Allocation Map, or IAMs)
    2. -2 list only IAMs

We can see that this only returns two pages. For the demo, I will be focusing on the page ID 153. I’m going to deliberately corrupt page 153 with two bits of information. The table SQLShackAuthor contains a value, Prashanth Jayaram, which we will meddle with, to corrupt the page in this demo.

Let’s enable the trace flag 3604 by running the following DBCC command. This option is used to redirect the output to the console.

Execute the DBCC PAGE command to examine the internal page details of the data and indexes.

To turn off the trace flag

To corrupt the page, we need to identify the starting offset of the page. Multiplying the page ID (153) with a number of bytes per page (8192) gives us the offset value.

Make the database offline. This option enables XVI32.exe tool to edit the mdf file.

To use XVI32

  1. Open XVI32.exe
  2. Browse to the path of the mdf file and select it
  3. Go to Search or Press Ctrl+G and then type offset 1253376

  4. Locate the value Prashanth on the screen and replace the character ‘s’ with ‘a’ and ‘h’ with ‘a’

  5. You can see that value has been changed from ‘Prashanth’ to ‘Praaanth’. Save and close the file.

  6. Bring the database online.

  7. Run the DBCC PAGE command to view the modified value.

By default, checksums are enabled on the database. Therefore, we can see the consistency error by executing a select statement or even by running the DBCC CHECKDB command.

The I/O error message is shown below

The expected and actual fields in the error message clearly define the expected values and the reason for the logical inconsistency, as shown below.

Msg 824, Level 24, State 2, Line 2
SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0xd8b40584; actual: 0xd8b0858d).
It occurred during a read of page (1:153) in database ID 26 at offset 0x00000000132000 in file ‘f:\PowerSQL\ProdSQLShackDemo.mdf’.

Another way to check the results is to run the following:

The table results option allows an administrator to programmatically investigate the results of CHECKDB using SQL queries.

We can also query the msdb.dbo.suspect_pages table to get the details on the corrupted pages.

Next, let us change the recovery of the database in the database property page to NONE and then execute the select statement to view the modified data

We can see the modified value in the following output

Performing a restore

In this section, I will take you through the steps to restore the page. Let’s revert the corrupting changes we previously made.

Before starting the restoration process, validate the consistency of every page by enabling the CHECKSUM along with PAGE_VERIFY database option. This allows the SQL Server database engine to calculate the checksum over the contents of the whole page.

With SQL Enterprise license, one could perform a page-level restore while the database is online. The process is nothing but actually swapping the corrupt data page(s) with a copy of data pages from the backup. On other editions of the SQL Server, the recovery would be an offline process, meaning, the database has to be offline during the restore. Let’s now perform the restore.

  • Browse Object-Explorer, right-click the database ProdSQLShackDemo
  • Go to tasks, select Restore and click Page….

  • In the restore page window, click the button at the top, Check Database Pages. This would identify the pages that it suspects are inconsistent, by executing the DBCC command, DBCC CHECKDB PHYSICAL_ONLY.

  • Specify the backup files to pull the data from by taking a look at the files in the backup sets section. Press the Verify button. You’ll see at the top that the backup media was verified successfully.

  • The database will go through the process of restoring the clean pages and a message on whether the process was successful or not would be displayed. we can see that the database restored successfully. Go ahead and press the OK button.

Now, let’s ensure that the data is correct by issuing the following SQL statement. You can even run the DBCC checkdb command to verify the data integrity.


In this article, we looked at page-level restore, by corrupting the data and then performing a recovery. The intention was to prove that restoration of data into a corrupted table is possible—simple, even—provided the backup is good.

We could take advantage of options such as checksums and torn_page_detection.

We also saw that only explicitly specified pages were restored and as such,this task would not pose any sort of resource overhead. The selected pages are replaced by the copy of the corresponding page from the specified data backup.

Table of contents

Database Backup and Restore process in SQL Server – series intro
Understanding the SQL Server Data Management Life Cycle
Understanding SQL Server database recovery models
Understanding SQL Server Backup Types
Backup and Restore (or Recovery) strategies for SQL Server database
Discussing Backup and Restore Automation using SQLCMD and SQL Server agent
Understanding Database snapshots vs Database backups in SQL Server
Smart database backup in SQL Server 2017
How to perform a Page Level Restore in SQL Server
Backup Linux SQL databases Using PowerShell and Windows Task Scheduler
SQL Server Database backup and restore operations using the Cloud
Tail-Log Backup and Restore in SQL Server
SQL Server Database Backup and Restore reports
Database Filegroup(s) and Piecemeal restores in SQL Server
In-Memory Optimized database backup and restore in SQL Server
Understanding Backup and Restore operations in SQL Server Docker Containers


Prashanth Jayaram
Backup and restore

About Prashanth Jayaram
