WORKING WITH PAGE RESTORE OPTION IN SQL SERVER

USE page

go

CREATE TABLE recipes

(

  recipe_id INT  NOT NULL,

  recipe_name VARCHAR(30) NOT NULL,

  PRIMARY KEY (recipe_id),

  UNIQUE (recipe_name)

);

INSERT INTO recipes

(recipe_id, recipe_name)

VALUES

(1,'Tacos'),

(2,'Tomato Soup'),

(3,'Grilled Cheese'),

(4,'Tomatoes'),

(5,'A'),

(6,'B'),

(7,'C'),

(8,'D'),

(9,'E')

go

GO

/*

 Take the backup of Page database after you creating a table.

*/

USE master

go

BACKUP DATABASE Page

TO DISK='D:\Mirror\Page_Full_08232017.BAK'

WITH STATS=10

/*

 The below query will provided the indexes that are there in the recipes table

*/

USE PAGE

GO

SELECT object_name(object_id),*

FROM sys.indexes

WHERE  object_name(object_id)='recipes'

GO

/*

Enable below traceflag to show you ful information of index page and you can see that in the next command.

*/

DBCC TRACEON(3604)

GO

/*

 The below query will provided that particular nonclustered indexes on hom many pages.

 Here we are going to corrupt non clusted index info you can get that from above

 sys.indexes query

 1-->Clustered

 2-Non Clustered

 0-Heap

 After running below command i am going to corrupt one of the PagePID that is 308 in this case

*/

DBCC IND('PAGE','dbo.recipes',2)

GO

/*

Here we are seeing the information 312 nonclusterd index page.

1-->FileName

312--Index page id

1--Willl give you full info

*/

DBCC PAGE('PAGE',1,308,1)

/*

Calculate which slot you want to corrupt here. Slot means row here. and i want to chose 6th slot

We need to convert hexadecimal value to decimal value here. And the below one is the workaround

PageNumber*8192(bytes)+ConvertedDecimalvalue(Slot 6 hexa decimal value).In this case

https://www.binaryhexconverter.com/hex-to-decimal-converter

*/

(308*8192)+96=2523232

/*

Download "free download xvi32" to read data file. And run that as administrator

*/

/*

Capture mdf and ldf file place of database by running below query

D:\Program Files\Microsoft SQL Server\MSSQL12.RAMESH\MSSQL\DATA\PAGE.mdf

D:\Program Files\Microsoft SQL Server\MSSQL12.RAMESH\MSSQL\DATA\PAGE_log.ldf

*/

USE page

go

sp_helpfile

/*

Bring the database offline by running below query

*/

USE master

go

ALTER DATABASE [page]

SET OFFLINE WITH ROLLBACK IMMEDIATE

/*

1--> Open mdf file of page database from xvi32 thridpartytool

2--Search with 2523232 value where we convert from hexadecimal to decimal and serch the same. In the below image text box we need to provide and clik ok button in XVI32 editor

3--> Provide some junk values here like sss or xxx xxx and click on cross button to close and then it will ask from

prompt to save, then save.

 

Now bring the databse to online by running below query

*/

USE master

go

ALTER DATABASE [page]

SET ONLINE WITH ROLLBACK IMMEDIATE

/*

We are successfully corrupt the page and see the below error

After you brought online try to run the below command and see the errror message.

*/

USE PAGE

GO

select * from recipes

/*

We are successfully corrupt the page and see the below error

Now see dbcc checdb error message here.

*/

/*

Now right click on the database go to task and do the page restore.

You can find error page information there and click on Ok button it will get automatically suceed.

You no need to take manual tail log backup here. Just simply click on Ok button

*/

/*

Now you run DBCC CHECK again and you wont see any error issue resolved through PAGE RESTORE via tasks

*/