Page Level Restoration | SansSQL

Saturday, March 26, 2011

Page Level Restoration

Sometimes you might come across a situation where a particular Page of a SQL Server database gets corrupted due to various reasons. In such cases only that corrupted page can be recovered using a backup.
To explain how to recover a page from the backup, I will first need to corrupt a page on a database.
By using the below method I am going to simulate the page corruption and then recover using the backup taken before corruption.
First I am going to create a database by name "TestPageLevelRestore" and Set its recovery Model to FULL.
USE master;
GO
CREATE DATABASE TestPageLevelRestore
ON
( NAME = TestPageLevelRestore,
    FILENAME = 'D:\TestPageLevelRestore.mdf',
    SIZE = 10)
LOG ON
( NAME = TestPageLevelRestore_log,
    FILENAME = 'D:\TestPageLevelRestore_log.ldf',
    SIZE = 5MB) ;
GO
Print 'Database TestPageLevelRestore Created'
ALTER DATABASE TestPageLevelRestore SET RECOVERY FULL
Print 'Recovery Model of database TestPageLevelRestore has been changed to FULL'


Now Create a table and insert data into that table.
Use TestPageLevelRestore
GO
CREATE TABLE [Shift](
      [ShiftID] tinyint IDENTITY(1,1) NOT NULL,
      [Name] nvarchar(50) NOT NULL,
      [StartTime] datetime NOT NULL,
      [EndTime] datetime NOT NULL,
      [ModifiedDate] datetime NOT NULL,
 CONSTRAINT [PK_Shift_ShiftID] PRIMARY KEY CLUSTERED ([ShiftID] ASC)
)
Print 'Creation of Table "Shift" Completed'

SET IDENTITY_INSERT [Shift] ON
INSERT [Shift] ([ShiftID], [Name], [StartTime], [EndTime], [ModifiedDate]) VALUES (1, N'Day', '1900-01-01 07:00:00.000', '1900-01-01 15:00:00.000', '1998-06-01 00:00:00.000')
INSERT [Shift] ([ShiftID], [Name], [StartTime], [EndTime], [ModifiedDate]) VALUES (2, N'Evening', '1900-01-01 15:00:00.000', '1900-01-01 23:00:00.000', '1998-06-01 00:00:00.000')
INSERT [Shift] ([ShiftID], [Name], [StartTime], [EndTime], [ModifiedDate]) VALUES (3, N'Night', '1900-01-01 23:00:00.000', '1900-01-01 07:00:00.000', '1998-06-01 00:00:00.000')
SET IDENTITY_INSERT [Shift] OFF

Print 'Data Insertion to table "Shift" Completed'


Now take a FULL backup of the database
BACKUP DATABASE TestPageLevelRestore TO DISK='D:\TestPageLevelRestore_FullBackup.bak' WITH STATS=10
Print 'Full Backup Completed'


After the backup is completed, get the list of index ID's from which you can choose one to corrupt
--To get the list of index ID's from which you can choose one to corrupt
Use TestPageLevelRestore
Select * from sys.indexes where OBJECT_NAME(object_id)='Shift'


Now get the list of pages in that index.
--To get the list of pages
DBCC IND ('TestPageLevelRestore', 'Shift',1)


Now you can get the page level details using the below query
-- To display the contents
DBCC TRACEON (3604);
GO
--TO get the page level data details
DBCC PAGE('TestPageLevelRestore',1,147,3);


For corrupting a particular page using a hex editor, you need to get the offset value, to obtain the offset value of a page simply multiply the PageID with 8192
--Get the Offset Value. This can be obtained by multiplying the page ID with 8192.
--Once you get the result copy the result and set the database to offline
SELECT 147*8192 AS [OffSetValue]


Once you get the offset Value, just copy it and take the database Offline.
USE MASTER
ALTER DATABASE TestPageLevelRestore SET OFFLINE
Print 'Database TestPageLevelRestore is set to Offline. Now Open the TestPageLevelRestore.mdf file in the hex editor and press ctrl+g to go the page where the index data is located.
Choose Decimal and paste the offset value.
once you go to the location, then manuplate the value and save the file and exit hex editor.
After manuplating data bring database online.'


Now Open the data file of the database "TestPageLevelRestore.mdf" file in the hex editor and press ctrl+g to go the page where the index data is located.

Then choose Decimal option and paste the offset value.
Once you go to the location, then manuplate the value and save the file and exit hex editor.



You can now see that I have edited the page and saved the file.

Once you edit the file and saved it, exit the Hex Editor and bring back the database to Online state.
USE MASTER
ALTER DATABASE TestPageLevelRestore SET ONLINE
Print 'Database TestPageLevelRestore is set to Online'


Now try to read data from the table and you will get error which states that the read failed at page (x:xxxx)
--Select the data and you will get error stating that the read failed at page (x:xxxx)
USE TestPageLevelRestore
Select * from shift
select * from sys.master_files where DB_NAME(database_id)='TestPageLevelRestore'


So this means that the page is now corrupted. By this the simulation of page corruption is completed.
Now we need to start looking on how to recover the page using page level restore.
Before we start the recovery process, we need to backup the tail of the log.
USE master
BACKUP LOG TestPageLevelRestore TO DISK = 'D:\TestPageLevelRestore_log.bak' WITH INIT, NORECOVERY;
GO

After backing up the tail log, restore the corrupted page using the below command.
Restore DATABASE TestPageLevelRestore Page='1:147' FROM DISK='D:\TestPageLevelRestore_FullBackup.bak'


After the page restoration is completed, Restore the tail log backup.
RESTORE LOG TestPageLevelRestore FROM DISK = 'D:\TestPageLevelRestore_log.bak';
GO


Now you have restored the corrupted page from a good backup and this can be verified by selecting the data from the table and you will be able to retrieve the data.
USE TestPageLevelRestore
Select * from shift


The complete demo script that I have used in this post can be downloaded from here.

8 comments:

bunsql said...

Excellent demo, you are gr8.

Adarsh said...

Sandesh,
When the result of
DBCC IND ('TestPageLevelRestore','Shift',1)
returns (PageFID,PagePID )as (1,154) & (1,153),
I assume the page IDs as 154 & 153 resp.
But, in the next DBCC
DBCC PAGE ('TestPageLevelRestore',1,147,3);,
here, you use 1:147 as FileID and PageID. Can you tell me which page you are mentioning here.

I wish to see a post on Indexes, Data pages and their physical existence defined completely at Storage Level.

Adarsh said...

Sandesh,
I had another scenario where in the backup file was damaged. And had a few corrupted pages which just prevented some database objects to be accessed. And that was the latest & a lone backup file available. Is there any way by any chance to have the complete data restored without data loss.

Sandesh Segu said...

Hi Adarsh,
You can choose any page. I have randomly choosen page 147 to corrupt. You can also choose 153 or 154 to be more specific.

Sandesh Segu said...

If the backup is only corrupted then i am not sure if you can recover the database without data loss.
You can recover with data loss.

sqldbaonly said...

Msg 4346, Level 16, State 1, Line 1
RESTORE PAGE is not allowed with databases that use the simple recovery model.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

Ellis White said...

Very nice article, also read http://www.sqlrecoverysoftware.net/blog/sql-server-page-level-corruption.html

Mac Zee said...

Is there any way to get around not being able to restore a page against a database in Emergency mode?
For instance, if I have DBv1 (Emergency) and find the page that is corrupt PAGE: (1:53720973), I can see that page in another restored copy of the database DBv2. Could I find the good page in DBv2 using a hex editor and use a hex editor to fix the same page in DBv1?

Post a Comment