In today's article, I want to talk about an important topic that every DBA should know: how to perform page level restore operations in SQL Server. Suppose you have a damaged page in SQL Server. You want to restore only the page in question from the latest database backup, not the entire database.

1, How to break a page

In the first step, I want to show you how to create a specific page corruption scenario in a table (or index). First, I want to construct some data.

USE master
GO

CREATE DATABASE PageLevelRestores
GO

USE PageLevelRestores
GO

-- Create a table where every record fits onto 1 page of 8kb
CREATE TABLE Test
(
    Filler CHAR(8000)
)
GO

-- Insert 4 records
INSERT INTO Test VALUES (REPLICATE('A', 8000))
INSERT INTO Test VALUES (REPLICATE('B', 8000))
INSERT INTO Test VALUES (REPLICATE('C', 8000))
INSERT INTO Test VALUES (REPLICATE('D', 8000))
GO

-- Retrieve the selected records
SELECT * FROM Test;

Next, perform a full backup. This backup contains all the pages belonging to the Test table. This is very important because next we will break a specific page of this table. To find out the pages belonging to the Test table, I use the DBCC IND command to return all the pages belonging to the table.

-- Perform a full database backup
BACKUP DATABASE PageLevelRestores TO DISK = N'C:\Backups\PageLevelRestores.bak'
GO
-- Retrieve the first data page for the specified table (columns PageFID and PagePID)
DBCC IND(PageLevelRestores, Test, -1)
GO

To break a specific page, use the unpublished DBCC WRITEPAGE command.

ALTER DATABASE PageLevelRestores SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
-- Let's corrupt page 90...
DBCC WRITEPAGE(PageLevelRestores, 1, 90, 0, 1, 0x41, 1)
DBCC WRITEPAGE(PageLevelRestores, 1, 90, 1, 1, 0x41, 1)
DBCC WRITEPAGE(PageLevelRestores, 1, 90, 2, 1, 0x41, 1)
GO
ALTER DATABASE PageLevelRestores SET MULTI_USER;

Here I simulated a storage error and wrote some garbage into the stored page. Now when you read the database from the table again, SQL Server will return your 824 I/O error, because the verification of the damaged page failed.

-- Retrieve the selected records
SELECT * FROM Test;

Once SQL Server detects a corrupted page during I/O access, it records it in the msdb.dbo.suspect_ In pages.

SELECT * FROM msdb.dbo.suspect_pages;

It's a good idea to monitor specific tables in msdb. You can get whether there are damaged pages in your database. Now let's make things worse and insert another record into the table.

-- Now we have additional transaction that we don't want to loose...
INSERT INTO Test VALUES (REPLICATE('E', 8000));

 

2, How to restore a damaged page

Now you want to restore this database to the correct state without losing data. What would you do?

First, we need to perform the so-called tail log backup: backup the transactions that have occurred since the last transaction log backup.

-- Backup the transaction log
BACKUP LOG PageLevelRestores TO DISK = 'C:\Backups\PageLevelRestores_LOG1.bak' WITH INIT;

There is no transaction log backup here, so our backup will include all executed transactions since the full backup. You can now initiate page level restore operations in SQL Server. Use the traditional RESTORE DATABASE command, but as long as you specify the pages you want to restore, you do not need to restore the entire database. For large databases, this makes a big difference.

USE master
-- Restore full database backup
RESTORE DATABASE PageLevelRestores
    PAGE = '1:90'
    FROM DISK = 'C:\Backups\PageLevelRestores.bak'
    WITH NORECOVERY
GO

Now it's the tricky part: after executing the RESTORE DATABASE command, you need to make another transaction log backup, because next you need to make sure that all the changes made on this available page are used for the restore. Without this additional log backup, SQL Server cannot bring your page back online.

-- Backup the tail of the log...
BACKUP LOG PageLevelRestores TO DISK = 'C:\Backups\PageLevelRestores_LOG_TAIL.bak' WITH INIT;

After this additional log backup, you can restore all log backups in the correct order, and finally bring the database online.

-- Restore all available log backups in the correct order
RESTORE LOG PageLevelRestores FROM
    DISK = 'C:\Backups\PageLevelRestores_LOG1.bak'
    WITH NORECOVERY
GO
-- Finally restore the tail log backup
RESTORE LOG PageLevelRestores FROM
    DISK = 'C:\Backups\PageLevelRestores_LOG_TAIL.bak'
    WITH NORECOVERY
GO
-- Finally finish with the restore sequence
RESTORE DATABASE PageLevelRestores WITH RECOVERY
GO

Now when you query the table again, you will see that the SELECT statement executes successfully without any I/O errors and no data is lost in this table.

USE PageLevelRestores
GO
-- Retrieve the selected records
SELECT * FROM Test;