jump to navigation

Using Page Restore in SQL Server 2012 using SSMS July 19, 2012

Posted by fofo in Sql Server, SQL Server 2012.
Tags:
trackback

In this post I would like to provide you with a hands-on example on how to perform a Page Restore using the SSMS in SQL Server 2012.We could perform page restores in SQL Server 2005/2008 (with T-SQL ) but in SQL Server 2012 we have SSMS UI support for the Page Restore operation.The Page Restore operation is intended for repairing isolated damaged pages.

In this post I will also show you how to corrupt a database by interfering (corrupting) with some database pages.

The Restore Pages feature let us restore damaged pages without restoring the entire database.These pages are marked as suspect.

Restore pages is only possible in the full and bulk-logged recovery model.We can only restore database pages.Page Restore must start with a full, file, or filegroup backup and there should be no break in the chain of log backups.

You can use any edition of SQL Server of 2012 you want for this example. SQL Server Express edition will do just fine.Download the Express edition, here .

In my case I will use Developer edition of 2012 for these example.

I connect to the SQL Server instance.

Type (copy – paste) the code below in a new query window


USE MASTER;
GO

CREATE DATABASE FOOTBALL;

USE FOOTBALL;
GO

CREATE TABLE Footballer
(
FootballerID int identity(1,1),
Footballer_No varchar(20),
Footballer_lastname nvarchar(50),
Footballer_firstname nvarchar(50),
Footballer_middlename nvarchar(50)
)

CREATE UNIQUE CLUSTERED INDEX cx_Foo_IX ON Footballer (FootballerID)

declare @CTR INT
declare @ctrstr varchar(7)
select @ctr=0
WHILE @CTR<60000
BEGIN
select @ctr=@ctr+1
select @ctrstr=Str(@ctr)
insert footballer(Footballer_No ,Footballer_lastname,Footballer_firstname, Footballer_middlename) values('F_No'+@ctrstr,'Footlname'+@ctrstr,'Footfname'+@ctrstr,'Footmname'+@ctrstr)
END

SELECT * FROM footballer

BACKUP DATABASE [FOOTBALL] TO  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\Backup\FOOTBALL.bak'
WITH NOFORMAT, NOINIT,  NAME = N'FOOTBALL-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

I am creating a database that I will corrupt in the process.Then I create a table and clustered index on that table.Then I populate the table with 60.000 records.

Then I take full database backup of the database.This backup contains un-damaged pages.

I would like to corrupt a page that is part of the clustered index.I will use the DBCC IND command.DBCC IND command provides the list of pages used by the table or index.


DBCC IND ('FOOTBALL', 'footballer',1)

Have a look at the picture below to see the results and the page I selected (1267).

Before I move on with the corruption of that particular page I must take the database offline.

Type and execute the code below


USE master;
ALTER DATABASE FOOTBALL SET OFFLINE;

Now I need to calculate the starting offset of the page I am about to corrupt.


SELECT 1267*8192 AS [StartingOffset]

In my case is 10379264

Now we need to open the .mdf file in a hexadecimal editor. You can download this free Hex editor here

It is very easy to install and run the editor. Make sure you launch this application with elevated privileges.Then navigate to the location of the .mdf file and open it.

Inside this file we need to locate our page. I press  GoTo (or Ctrl + G), then paste in the offset value (10379264) by selecting decimal.Then I press OK  and I am at the beginning of this page.

Have a look at the picture below

Now I am about to corrupt by simply changing the values and corrupting the page.

Have a look at the picture below

Now we need to bring the database online.

Type and execute the code below


USE master;
ALTER DATABASE FOOTBALL SET ONLINE;

Then we can run a simple query

Type and execute the code below


USE FOOTBALL;
SELECT FootballerID FROM Footballer

We will get an error. Have a look at the picture below

Now let’s run the DBCC CHECKDB command.


DBCC CHECKDB ('FOOTBALL')

Have a look at the picture below to see the results I have got.

You can query the suspect_pages table of  the msdb database.


SELECT * FROM msdb..suspect_pages

In my case this is the output I get when I run the statement above.

database_id    file_id     page_id              event_type       error_count            last_update_date
———– ———– ——————– ———– ———– ———————–
6                            1           1267                            1                             3                  2012-07-18 03:17:57.333

You can also have a look at the SQL Server Logs.You will identify the corruption immediately.

Have a look at the picture below

In order to restore the page, I select the database, I right-click on it (from the Object Explorer in the SSMS) then select Tasks->Restore->Page .

Have a look at the picture below


Then the Restore Page screen appears.You can see that the database is selected, the FileID and PageID of the damaged page.You can run DBCC CHECKDB command.You can also see the Tail-Log backup and all the other backups.
Have a look at the picture below

Then I press OK.Then I execute the statements below and there are no errors.


USE FOOTBALL;
SELECT FootballerID FROM Footballer
DBCC CHECKDB ('FOOTBALL');

To recap, we showed (do not do this in a production database) how to corrupt a page in a database and also how to perform Page Restore using SSMS UI in SQL Server 2012.

Hope it helps!!!

Advertisements

Comments»

1. Dot Net Rules : Using Page Restore in SQL Server 2012 using SSMS - July 19, 2012

[…] Restore operation.The Page Restore operation is intended for repairing isolated damaged pages. (read more) Share Posted: Πέμπτη, 19 Ιουλίου 2012 1:11 μμ από το μέλος […]


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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

%d bloggers like this: