SQL Server 2012 Restore Page using SSMS

Dear Friends,

Every release of SQL Server shipped with full of new features so is SQL Server 2012. Some of them are Availability Groups, Contained Databases, Column Store Indexes, User Defined roles,  DQS, Power View to name a few. We do observe lot of enhancements in GUI as well which ease up our task and helpful to the ones who doesn’t want to play with T-SQL. Needless to say, we do look on how newer versions of SQL Server have matured in terms of GUI. With this in mind, today I’m going to show you can restore a page using SSMS on SQL 2012 .

To begin with; at different times we do observe page level corruptions in a database as such definitely as database admins we are interested in restoring single page rather than restoring the entire database mostly to minimize downtime. In practical also when your database is quite huge one in size, you never want to go for full restoration only when a single page is identified to be corrupted. By saying so, my statement definitely is not a generic one understanding this may differ across environments.

We can say that page level restoration is not something new to us but yes SQL Server 2012 Restore Page using SSMS definitely something new in SQL Server 2012. Till SQL Server 2012 page restoration was possible using T-SQL but now with the launch of SQL Server 2012, you have the flexibility to use its improved GUI to achieve the same objective.

Today, I’ll be using AdventureWorks2012 database for the demo purpose. Table I’ll be using here is HumanResources.JobCandidate and I’m interested to corrupt Index Page for this table today. You can read more on how to identify a page here.

As we are discussing on SQL Server 2012 as such I’m not going to use DBCC IND to find out index page details instead will be using new DMV SYS.DM_DB_DATABASE_PAGE_ALLOCATIONS to retrieve similar information. This is an undocumented change and you can read more on this link.

So I ran the query as can be seen on following screenshot to obtain similar details;

1_SQL_Server_Restoring_Page_using_SSMS_in_SQL_Server_2012 - Copy

Ok, from query output I identified PageID 1057 of Page Type 2 i.e. Index Page (image below);

2_SQL_Server_Restoring_Page_using_SSMS_in_SQL_Server_2012 - Copy

Now time to corrupt this page but as always recommended before I do that, let me backup my database first (to be at the safer side);

3_SQL_Server_Restoring_Page_using_SSMS_in_SQL_Server_2012 - Copy

We are all set to corrupt PageID 1057. I’ll be using XVI32 editor to corrupt the page which can be downloaded from here; but before I do so I need to OFFLINE the database.

4_SQL_Server_Restoring_Page_using_SSMS_in_SQL_Server_2012 - Copy

5_SQL_Server_Restoring_Page_using_SSMS_in_SQL_Server_2012 - Copy

Now, before I open up hex-editor; I need to find out the page offset and for me it is 1057 * 8192 (i.e. No of bytes for the page) = 8658944. By doing so, I’m all set to corrupt the page now. I went to the exact page and corrupted that by entering some letter;

   

6_SQL_Server_Restoring_Page_using_SSMS_in_SQL_Server_2012 - Copy

By doing so, in technical terms I’ve corrupted the page. Time to bring database online and query corrupted table. Let’s do that;

7_SQL_Server_Restoring_Page_using_SSMS_in_SQL_Server_2012 - Copy

8_SQL_Server_Restoring_Page_using_SSMS_in_SQL_Server_2012 - Copy

All set to run Select query on HumanResources.JobCandidate table so going forward;

9_SQL_Server_Restoring_Page_using_SSMS_in_SQL_Server_2012 - Copy

It can be seen very clearly that I’ve corrupted the same page matched in hex-editor. Now it is the time to think about remediating the corrupted page.

As mentioned before starting SQL Server 2012 we can select this from SSMS by right clicking on database and choose restore page. As soon as I do that, I’ll get the following window;

10_SQL_Server_Restoring_Page_using_SSMS_in_SQL_Server_2012 - Copy

What’s great to see here is SQL Server has already ran a DBCC CHECKDB and pulled details on corrupted page. Good one to have for sure. This wizard also lets you select backup that you want to use to repair corrupted pages so let’s move ahead.

11_SQL_Server_Restoring_Page_using_SSMS_in_SQL_Server_2012

Yes, I’ve successfully restored corrupted page; so let’s check out if we can query HumanResources.JobCandidate.

12_SQL_Server_Restoring_Page_using_SSMS_in_SQL_Server_2012

We can very much retrieve data out of table is in question. Needless to say, this indeed a cool feature offered by SQL Server 2012.

Please note I’ve tested this in a LAB system for demonstration purpose and you should never ever try these steps in any production or live environments.

 

Regards

Kanchan Bhattacharyya

Like us on FaceBook Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on TwitterFollow me on FaceBook

   

About Kanchan Bhattacharyya

Kanchan is an astute IT professional, a seasoned SQL Database Administrator with 13+ years of industry experience. A calculated risk taker with deep technical knowledge and has gained proficiency in database consulting and solutions across different environments. Kanchan holds MBA degree in IT and an International Executive MBA in Project Management. He is very passionate about SQL Server and holds MCSE (Data Platform), MCSA – SQL 2012, MCITP – SQL 2008 certifications. Currently he is focusing on cloud and latest releases of SQL Server. When not working, Kanchan likes to spend his time reading on new technical developments specifically on SQL Server and other related technologies.

View all posts by Kanchan Bhattacharyya →

Leave a Reply

Your email address will not be published.