In this article in our stairway series on SQL Server backup and restore, we shall discuss the importance of understanding the internals of the page restoration for a database administrator. Most of the time, performing a page-level restore suffices for database availability. A good backup strategy is a key to recovery or restoration, and SQL Server provides us with an option to fix database pages at a granular level. Performing a page level restore in SQL Server is a clever option wherein the restoration of the entire VLDB database backup is not required.

Identifying the corruption and finding a solution is the key to successful restoration or recovery of the database. If the number of corrupted pages seems too large, and if the administrator feels that restoring the entire database from the most recent backup is a viable option, only in that case should the option be full restoration.

Page restore is a technique used to replace corrupted pages of information in a database with uncorrupted data from a backup. Page-level restore can be performed via SSMS or T-SQL. The intent of the restore is to fix one or more corrupted pages from the available backups. To identify corrupted pages, look for pages marked as “suspect” in the table, msdb syspect_pages.

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
4. Restore a page from a full backup using SSMS and T-SQL
5. And more…

Let’s explore the ways to corrupt and restore a page using a demo.

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:

1. DBCC WRITEPAGE
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.

DBCC IND

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.

   1 2 3

   USE master; GO ALTER DATABASE ProdSQLShackDemo SET 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’.
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.

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.

Summary

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

References

• More undocumented fun: DBCC IND, DBCC PAGE, and off-row columns
• Restore Pages (SQL Servers)
• Freeware Hex Editor XVI32

• Author
• Recent Posts

Prashanth Jayaram

I’m a Database technologist having 11+ years of rich, hands-on experience on Database technologies. I am Microsoft Certified Professional and backed with a Degree in Master of Computer Application.

My specialty lies in designing & implementing High availability solutions and cross-platform DB Migration. The technologies currently working on are SQL Server, PowerShell, Oracle and MongoDB.

View all posts by Prashanth Jayaram

Latest posts by Prashanth Jayaram (see all)

• Stairway to SQL essentials - April 7, 2021
• A quick overview of database audit in SQL - January 28, 2021
• How to set up Azure Data Sync between Azure SQL databases and on-premises SQL Server - January 20, 2021