By: Simon Liew | Comments (4) | Related: > 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
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
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 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…
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.
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.
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.
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
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
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.
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.
To doubly make sure the corruption is resolved, we perform a database integrity checks. The command executed successfully without error.
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
- Restore Pages (SQL Server)
- Online Restore (SQL Server)
- Checking to make sure a SQL Server backup is useable
About the author
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
