How to Perform an Online Page Level Restore in SQL Server


By:   |   Updated: 2018-10-19   |   Comments (4)   |   Related: More > Restore

Problem

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?

Solution

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
GO
DROP DATABASE IF EXISTS [CorruptionTest]
GO
CREATE DATABASE [CorruptionTest]
GO
ALTER DATABASE [CorruptionTest] SET RECOVERY FULL;
GO
ALTER DATABASE [CorruptionTest] SET PAGE_VERIFY CHECKSUM  
GO
CREATE TABLE [CorruptionTest].[dbo].[mssqltips_online]
(increment INT, randomGUID uniqueidentifier, randomValue INT, BigCol CHAR(2000) DEFAULT 'a',
INDEX CIX_MSSQLTips_increment1 UNIQUE CLUSTERED (increment))
GO
CREATE TABLE [CorruptionTest].[dbo].[mssqltips_corrupt]
(increment INT, randomGUID uniqueidentifier, randomValue INT, BigCol CHAR(2000) DEFAULT 'a',
INDEX CIX_MSSQLTips_increment1 UNIQUE CLUSTERED (increment))
GO

SET NOCOUNT ON;
DECLARE @counter INT = 1;
BEGIN TRAN
   WHILE @counter <= 250000
   BEGIN
      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
   END;
COMMIT TRAN;
GO
DBCC CHECKDB('CorruptionTest') WITH NO_INFOMSGS
GO
BACKUP DATABASE [CorruptionTest] TO DISK = 'D:\BACKUP\CorruptionTest_Full.BAK' WITH COMPRESSION
GO			

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.

SELECT TOP 10
   sys.fn_PhysLocFormatter(%%physloc%%) PageId,
   *
FROM [CorruptionTest].[dbo].[mssqltips_corrupt]
GO			
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;
GO  
ALTER DATABASE [CorruptionTest] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
DBCC WRITEPAGE ('CorruptionTest', 1, 282, 4182, 4, 0x00000000, 1)
GO
DBCC WRITEPAGE ('CorruptionTest', 1, 283, 4166, 16, 0x00000000000000000000000000000000, 1)
GO
ALTER DATABASE [CorruptionTest] SET MULTI_USER;
GO			

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.

SELECT @@SPID SESSION_ID
GO
DECLARE @COUNT INT
WHILE 1 = 1
BEGIN
   SELECT @COUNT = COUNT(*) 
       FROM [CorruptionTest].[dbo].[mssqltips_online]
END
GO			
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]
GO			
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
GO
			

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

SELECT COUNT(*) 
FROM [CorruptionTest].[dbo].[mssqltips_corrupt]
WHERE increment NOT BETWEEN 3 AND 10
GO			
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

Summary

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


Last Updated: 2018-10-19


get scripts

next tip button



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.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





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

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

 Simon,

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,

Kenney


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

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

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

 



download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools