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:
CREATE DATABASE PageRestoreDemoDB;
IF DATABASEPROPERTYEX(N'PageRestoreDemoDB', N'Recovery') <> 'FULL'
ALTER DATABASE PageRestoreDemoDB SET RECOVERY FULL;
ALTER DATABASE PageRestoreDemoDB SET PAGE_VERIFY CHECKSUM;
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:
AS ( SELECT bs.database_name ,
backup_type = CASE type
WHEN 'D' THEN 'Full'
WHEN 'L' THEN 'Log'
WHEN 'I' THEN 'Differential'
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_cte.physical_device_name AS Backup_File_Path
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);
SELECT TOP 100
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;
DBCC WRITEPAGE (N'PageRestoreDemoDB', 1, 287, 4000, 1, 0x45, 1);
ALTER DATABASE PageRestoreDemoDB SET MULTI_USER;
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.
Very well explained, thanks for sharing it.
Another nicely exlained blog, thanks for sharing.
I like this web site very much, Its a rattling nice spot to read and incur info .
Thanks all for your feedback, glad you found it useful, your kind feedback is much appreciated.
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.
The examples were bang on as always.
Great work SQLWizard.
This article really helped me in understanding page level restore in SQL server
Another great article… specially the way examples have been used to explain things
Thanks for all these nice blogs..keep writing more