How to Perform an Online Page Level Restore in SQL Server

By:   |   Comments (4)   |   Related: > Restore


I have a SQL Server database in FULL recovery model. Our nightly database integrity checks were successful, a full database backup was completed and then an outage occurred which corrupted a couple of data pages in the database. We want to perform a page level restore on our live database from the full database backup. Are all objects still accessible when the page restore operation takes place in SQL Server?


An online page level restore is only available in SQL Server Enterprise and equivalent, such as Developer and Evaluation Edition. During the online page restore operation, users can still access all other objects in the database except the corrupted pages being restored. In the situation when you only have a latest known good full backup prior to the corruption, this backup is sufficient to be used for page level restore operations.

Even when your database is not on Enterprise\Developer\Evaluation Edition, the same steps can be used with the exception that the database needs to be OFFLINE during the page restore operation.

Create a Test SQL Server Database

We will create a test database using the script below and at the end of the script we will perform a full database backup.

The SQL Server version used in this tip is SQL Server 2016 Developer Edition, which has all the features and functions of SQL Server Enterprise Edition.

-- Update and specify a valid backup path at the end of the script

USE master
CREATE DATABASE [CorruptionTest]
CREATE TABLE [CorruptionTest].[dbo].[mssqltips_online]
(increment INT, randomGUID uniqueidentifier, randomValue INT, BigCol CHAR(2000) DEFAULT 'a',
INDEX CIX_MSSQLTips_increment1 UNIQUE CLUSTERED (increment))
CREATE TABLE [CorruptionTest].[dbo].[mssqltips_corrupt]
(increment INT, randomGUID uniqueidentifier, randomValue INT, BigCol CHAR(2000) DEFAULT 'a',
INDEX CIX_MSSQLTips_increment1 UNIQUE CLUSTERED (increment))

DECLARE @counter INT = 1;
   WHILE @counter <= 250000
      INSERT INTO CorruptionTest.dbo.mssqltips_online (increment, randomGUID, randomValue) 
      VALUES (@counter, NEWID(), ABS(CHECKSUM(NewId())) % 140000000)

      INSERT INTO CorruptionTest.dbo.mssqltips_corrupt (increment, randomGUID, randomValue)
      VALUES (@counter, NEWID(), ABS(CHECKSUM(NewId())) % 140000000)

      SET @counter += 1

The query execution below outputs the top 10 rows with the page id and its slot number. We will (randomly) choose to corrupt page id 282 and 283 by overwriting the 2 highlighted values with 0x0.

   sys.fn_PhysLocFormatter(%%physloc%%) PageId,
FROM [CorruptionTest].[dbo].[mssqltips_corrupt]
database page information

Using the undocumented DBCC WRITEPAGE below, we will corrupt the value in the 2 mentioned columns to all zeros. If you wish to learn how the input parameters are calculated, refer to my previous tip on using undocumented DBCC WRITEPAGE to instigate SQL Server database corruption to understand how to use the command. In this demonstration, you can try to corrupt any data page id and we will recover it from the full database backup.

USE master;
DBCC WRITEPAGE ('CorruptionTest', 1, 282, 4182, 4, 0x00000000, 1)
DBCC WRITEPAGE ('CorruptionTest', 1, 283, 4166, 16, 0x00000000000000000000000000000000, 1)

Start a Control Query

The preparation work is now done. We will execute the query below in new Query windows and leave the query running. It will continuously scan all the rows in table [dbo].[mssqltips_online]. This only serves as a control query to make sure the connectivity at the database level is always ONLINE when we perform the page level restore operation.

WHILE 1 = 1
       FROM [CorruptionTest].[dbo].[mssqltips_online]
The control query is just to make sure database connectivity is ONLINE during the whole page restore operation

We have corrupted 2 data pages, 282 and 283 in table [dbo].[mssqltips_corrupt]. When we execute the query below against [dbo].[mssqltips_corrupt], it will fail with an error message as expected.

SELECT COUNT(*) FROM [CorruptionTest].[dbo].[mssqltips_corrupt]
sql server error message

SQL Server Page Level Restore

We will now execute the steps to perform an online page level restore from our good known full database backup.

Right-click on database [CorruptionTest] > Tasks > Restore > Page…

restore database ssms

In the Restore Page, click the [Check Database Pages] button. This will perform database integrity checks in the background and automatically populate the corrupted Page IDs that needs to be restored. In our demonstration, the list matches the 2 Page IDs that we have corrupted.

Perform database integrity checks from Restore Page

Click on the [Verify] button, this will verify the backup media to make sure the backup set is complete and readable. We can click [OK] to start the page restore from the [Restore Page] GUI here, but let’s investigate further and script out the command.

Perform database backup verification from Restore Page

The scripted-out command from the [Restore Page] form contains the three commands as below:

USE [master]

RESTORE DATABASE [CorruptionTest] PAGE='1:282, 1:283' FROM DISK = N'D:\Backup\CorruptionTest_Full.BAK' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 5

BACKUP LOG [CorruptionTest] TO DISK = N'D:\Backup\CorruptionTest_LogBackup_2018-09-03_20-25-42.bak' WITH NOFORMAT, NOINIT, NAME = N'CorruptionTest_LogBackup_2018-09-03_20-25-42', NOSKIP, NOREWIND, NOUNLOAD, STATS = 5
RESTORE LOG [CorruptionTest] FROM DISK = N'D:\Backup\CorruptionTest_LogBackup_2018-09-03_20-25-42.bak' WITH NOUNLOAD, STATS = 5

The 3 commands can be described as performing the steps below:

  • Step 1: Perform restore of pages from full database backup file with NORECOVERY
  • Step 2: Perform a backup of (tail) transaction log
  • Step 3: Perform a restore from the (tail) transaction log backup

We will go through Step 1 and Step 2 - 3 separately to understand its impact individually.

Step 1: Restore page from database with NORECOVERY

This restore command only restores specific pages from the full database backup. It does not disrupt any queries executing against the database. As we would expect, the control query (session 59) is still executing.

restore database tsql

The pages restore operation completed. When we execute the query below, it prompts a different error message stating page id 282 is inaccessible because it is in RestorePending. Reason it didn’t prompt page id 283 is inaccessible because the query terminates as it accesses page id 282 first due to the logical ordering of the table enforced by the clustered index on column [increment].

SELECT COUNT(*) FROM [CorruptionTest].[dbo].[mssqltips_corrupt]
database error message

To also prove the point all objects in the database are accessible except the corrupted pages being restored, we will execute the query below which does returns the row count without error. Note that increment 3 and 10 is part of the non-corrupted page. The in-depth explanation is not covered in this tip, but it has to do with the way the SQL Server storage engine accesses data in different situations.

FROM [CorruptionTest].[dbo].[mssqltips_corrupt]
WHERE increment NOT BETWEEN 3 AND 10
query results

Step 2 & 3: Perform a tail-log backup and Restore of the tail log

Step 2 is an important and a required step. If you have taken any other transaction log backups after the full backup, you will need to restore them in sequence prior to this this step. Then finally the last step would always be to restore the tail log backup.

We only have the full database backup prior. Hence, we will execute Step 2 and 3 in a single batch. Note that the restore tail log does not specify NORECOVERY. So the restored pages will be brought ONLINE when the restore log command completes.

backup database commands sql server

During this log backup and restore process, the control query (session 59) is still executing. This ensures the database connectivity was ONLINE and available throughout the page restore process.

We now execute the query below which will scan all the rows in table [dbo].[mssqltips_corrupt] and this query executes successfully. We have now completed our page restore operation and fixed the page corruption.

query results

To doubly make sure the corruption is resolved, we perform a database integrity checks. The command executed successfully without error.

dbcc checkdb results


SQL Server has made it quite easy to perform page level restore using the SSMS GUI. This feature is especially useful in the situation where few data pages are corrupted in a very large database. By performing page level restores, this can significantly reduce the recovery time objective for the database.

But there is a caveat. In this tip, we have performed the full database backup on the same SQL Server instance. Hence SQL Server was able to automatically map the full database backup to the GUI. Otherwise, you will need to rely on some toolset or script to generate the same commands for the restores. Nevertheless, knowing the required sequence and with practice, you can still achieve the same outcome with accuracy and ease before real disaster strikes.

Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Simon Liew Simon Liew is an independent SQL Server Consultant in Sydney, Australia. He is a Microsoft Certified Master for SQL Server 2008 and holds a Masterís Degree in Distributed Computing.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Tuesday, October 23, 2018 - 8:50:27 AM - Simon Liew Back To Top (78035)

Hi Kenney,

The tail log backup and restore is done after the last log backup is restored. The sequence you've described are correct

1. Restore from latest full backup at page level prior to corruption (Sunday full backup)

2. Retore from latest differential backup prior to corruption (Monday differential)

3. Restore all log backups since Monday 2AM

4. When all log backups are restored, take a tail-log backup and restore


Step 4 would bring the restored page online. 

Monday, October 22, 2018 - 12:40:01 PM - Kenney Hill Back To Top (78025)


Thank you for this post, some how I've missed this feature in almost 20 years of working with SQL.

I would like to ask clarification about order of restores and backups in a more real world scenario.  There are weekly fulls (Sunday 2AM), daily diffs (M-Sa 2AM), and quarter hourly log backups.  The problem occurs Monday morning, so you need to restore the full from Sunday, the diff from Monday, and the log backups since 2AM.  At what point do we backup the tail-log?  After the last log backup is restored or another point?  The tail-log is restored last but when is it taken I'm a little unclear on.

Thank you,


Friday, October 19, 2018 - 11:34:05 PM - Simon Liew Back To Top (77992)

Thank you for reading the tip Don. I hope you find it useful too.

Friday, October 19, 2018 - 9:10:29 AM - Don Kolenda Back To Top (77983)

 This was a great post, and the steps were explained very well. Great work! 


get free sql tips
agree to terms