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;