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:

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

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

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:

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:

DBCC CHECKDB will also indicate a corruption issue:

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:

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

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

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.

Advertisement