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;
Ok, from query output I identified PageID 1057 of Page Type 2 i.e. Index Page (image below);
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);
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.
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;
By doing so, in technical terms I’ve corrupted the page. Time to bring database online and query corrupted table. Let’s do that;
All set to run Select query on HumanResources.JobCandidate table so going forward;
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;
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.
Yes, I’ve successfully restored corrupted page; so let’s check out if we can query HumanResources.JobCandidate.
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 Twitter | Follow me on FaceBook
