Data Page Restore to resolve Page Level Corruption

SQL Page Level Corruption can happen due to several reasons such as Server failure, disk failure etc. These data corruption is always a nightmare for DBAs, but this can be corrected if you have right backups in place which is the most important and essential thing for a DBA. Let’s put it in this way “You can get everything else wrong as a DBA as long as you get backups right!”.

Now the objective is to restore one or more damaged pages without restoring the whole database.

A page restore is intended for restoring only few individual damaged pages as it will be faster than restoring the File or Filegroup or Database. However, while restoring a large number of damaged/corrupted pages from the database file, repairing/restoring the whole Database/File/Filegroup is somewhat recommended.

Also please note that, It is not essential to restore the pages in all types of errors at page level. A problem can also arise in cached data, such as a secondary index, that can be fixed by recalculating the data. For example, a DBA drops secondary index and rebuilds it, the corrupted data, although fixed, is not indicated as such in the suspect_pages table.

Possible scenarios of Page Level Restore

Online page restore

Online page restore is only available in Enterprise and Developer Editions. In most cases,  corrupted pages can be restored while the database remains online, including the filegroup to which a page is being restored.

Offline page restore

All editions of SQL Server support restoring pages when the database is offline. In an offline page restore, the database is offline while damaged pages are restored. At the end of the restore sequence, the database comes online.

Limitations and Restrictions:

The page restore can be performed in case of corrupted/damaged pages, but there are some limitations as below:

  • Databases that are using Full or Bulk-logged recovery models are only appropriate for page level restore. Page restore is supported only for read/write filegroups.
  •  Only database pages can be restored. Page restore cannot be used in following:
    • Catalog of full-text based
    • Database of transaction log
    • Page 1:9 that shows database boot page
    • Page 0 of all files as it is file boot page
    • Allocation pages – PFS, SGAM, and GAM
  • For a database that uses the bulk-logged recovery model, page restore has the following additional conditions:

    • Backing up while filegroup or page data is offline is problematic for bulk-logged data, because the offline data is not recorded in the log. Any offline page can prevent backing up the log. In this cases, consider using DBCC REPAIR, because this might cause less data loss than restoring to the most recent backup.

    • If a log backup of a bulk-logged database encounters a bad page, it fails unless WITH CONTINUE_AFTER_ERROR is specified.

    • Page restore generally does not work with bulk-logged recovery.

      Best practice to implement SQL server page restore is to set database to full recovery model. Then, try to create log backup. In case, backup works properly then, it is safe to continue page restore, otherwise we should avoid.
      In case, there is a backup failure then, you are supposed to face either of the conditions: Loss of data, which was done in the last log backup
      Instead, you can try DBCC with the syntax of REPAIR_ALLOW_DATA_LOSS with the main risk of losing corrupt data in the procedure.

 

Let’s get straight to Demo

We would need to create a database in Full recovery, insert some dummy data and manually corrupt a page of the database to replicate the scenario where one of the data page gets damaged to hardware failure.

Let’s create the database with Full recovery model:

USE [master]; 
GO
CREATE DATABASE PageRestoreDemoDB;
GO
IF DATABASEPROPERTYEX(N'PageRestoreDemoDB', N'Recovery') <> 'FULL'
ALTER DATABASE PageRestoreDemoDB SET RECOVERY FULL;
GO
ALTER DATABASE PageRestoreDemoDB SET PAGE_VERIFY CHECKSUM;
GO

Create table and insert some data: 

USE [PageRestoreDemoDB];
GO  
--Create Table
IF NOT EXISTS ( SELECT  1
                FROM    INFORMATION_SCHEMA.TABLES
                WHERE   TABLE_NAME = 'TblPageRecovery' )
    BEGIN
        CREATE TABLE [TblPageRecovery]
            (
              [id] INT IDENTITY ,
              [CreatedDate] DATETIME DEFAULT GETDATE()
            );
    END
-- Insert Default values
INSERT  INTO [TblPageRecovery]
        DEFAULT VALUES;
GO 100 -- This will run the Insert 100 times

Take the Full and Log backup:

BACKUP DATABASE [PageRestoreDemoDB] 
TO  DISK = N'G:\backups\PageRestoreDemoDB_Full.bak' 
WITH NAME = N'PageRestoreDemoDB-Full Database Backup'
GO
BACKUP LOG [PageRestoreDemoDB] 
TO  DISK = N'G:\backups\PageRestoreDemoDB_Log1.trn' 
WITH NAME = N'PageRestoreDemoDB-Log Backup'
GO 

Check if backups have been taken successfully:

;WITH backup_cte
AS ( SELECT bs.database_name ,
backup_type = CASE type
WHEN 'D' THEN 'Full'
WHEN 'L' THEN 'Log'
WHEN 'I' THEN 'Differential'
ELSE 'other'
END ,
bmf.physical_device_name ,
bs.backup_finish_date ,
rownum = ROW_NUMBER() OVER ( PARTITION BY bs.database_name,
type ORDER BY bs.backup_finish_date DESC )
FROM msdb.dbo.backupset bs
INNER JOIN msdb..backupmediafamily bmf ON bmf.media_set_id = bs.media_set_id
)
SELECT database_name ,
backup_type ,
backup_finish_date ,
backup_cte.physical_device_name AS Backup_File_Path
FROM backup_cte
WHERE database_name = 'PageRestoreDemoDB'
ORDER BY backup_cte.backup_finish_date;

Here you can see your backup type and location:

Migrationa

You would need to enable following trace flag if you want to print the output to the console not the error log:

Migrationa

As we will be using undocumented commands here so following Trace flag will enable the help on undocumented commands:

Migrationa

Following queries will find all the pages available in the table

DBCC IND ('PageRestoreDemoDB', 'TblPageRecovery', -1);
GO
SELECT TOP 100
sys.fn_PhysLocFormatter(%%physloc%%) PageId
, *
FROM [PageRestoreDemoDB].[dbo].[TblPageRecovery]
GO

Here you can see the pages:

Migrationa

Let’s corrupt one page 287 using undocumented DBCC WRITEPAGE and try to select the data from table:

ALTER DATABASE PageRestoreDemoDB SET SINGLE_USER;
GO
DBCC WRITEPAGE (N'PageRestoreDemoDB', 1, 287, 4000, 1, 0x45, 1);
GO
ALTER DATABASE PageRestoreDemoDB SET MULTI_USER;
GO
SELECT *
FROM PageRestoreDemoDB.dbo.TblPageRecovery

DBCC ran successfully and corrupted page 287. The Select query gave following error:

Migrationa

DBCC CHECKDB will also indicate a corruption issue:

Migrationa

We can also query the msdb.dbo.suspect_pages table to get the details of the corrupted pages.

Let’s restore the damaged page while database will remain online (you can use GUI or SQL command as below):

USE [master]
GO
-- Restore damaged Page from Full Backup RESTORE DATABASE [PageRestoreDemoDB] PAGE='1:287' FROM DISK = N'G:\backups\PageRestoreDemoDB_Full.bak' WITH NORECOVERY; BACKUP LOG [PageRestoreDemoDB] TO DISK = N'G:\backups\PageRestoreDemoDB_Log1.trn' WITH NAME = N'PageRestoreDemoDB_LogBackup_2020-03-11_23-39-51'; RESTORE LOG [PageRestoreDemoDB] FROM DISK = N'G:\backups\PageRestoreDemoDB_Log.trn' WITH NORECOVERY; RESTORE LOG [PageRestoreDemoDB] FROM DISK = N'G:\backups\PageRestoreDemoDB_Log1.trn' GO

Following output shows that damaged page has been recovered:

Migrationa

Let’s try to read the data from the table:

Migrationa

We can access the data. Let’s check Suspect pages in msdb.

Migrationa

Suspect page entry has disappeared now.

In this blog post, we have restored database at page-level.  Please leave a comment if you have any question or feedback.

8 thoughts on “Data Page Restore to resolve Page Level Corruption

Add yours

  1. Can I simply say what a relief to discover someone who actually understands what they’re discussing over the internet.

    You certainly understand how to bring an issue to light and make it important.
    A lot more people have to read this and understand this
    side of the story. I was surprised you’re not more popular given that you definitely
    have the gift.

    Like

  2. Another great article… specially the way examples have been used to explain things

    Thanks for all these nice blogs..keep writing more

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Website Powered by WordPress.com.

Up ↑

%d bloggers like this: