From the course: SQL Server 2016: Administer a Database Infrastructure

Perform page recovery - SQL Server Tutorial

From the course: SQL Server 2016: Administer a Database Infrastructure

Start my 1-month free trial

Perform page recovery

“

- [Narrator] Page restore is a technique for replacing corrupted pages of information in a database with uncorrupted data from a back up. To explore page recoveries, we first need a database that has some content. I'll go ahead and create one called Page DB and then switch into it. Next, we're going to have to set a back up and I want to make sure that it's using the full recovery model. So let's go ahead and alter the database and set as recovery model to full just in case it isn't. Then let's add a schema, a table, and some data. I'll create a schema called Warehouses. A table called Warehouse.newproducts, and some products here. Finally I'll go ahead and scroll down and just make sure that that executed without any issues, and there's the three products. Next we need to create a back up of this database. I'll create a back up of the Page DB database and store it on disk in a folder that I already have created called Temp SQL, and the name of the file will be PageDB.BAC. I'll include the width format line to simple overwrite any existing back up files that are in that same location if they already exist. Now for the rest of this example, we'll need to use our imagination a little bit because there isn't a way for me to force the situation that a page recovery solves. As hard drives age, portions may become unstable which can cause small discrepancies in the data that they're storing. There's a couple of ways to check to see if this has occurred in our database. The MSBD database has a table called DBO.suspectpages. And if we query out of it we can find out information on if there's any suspect pages in our databases. I'll go ahead and select everything from MSDB.DBO.suspectpages. Now I don't actually get any results which is good because I don't have any errors on this computer. But notice that the columns that are returned. We have the database ID, file ID, page ID and the type of event or error that has been identified. Other ways to discover if you have any corrupt pages in your database include messages in the error logs or event traces. However they're identified, you'll need the file ID and the page ID in order to attempt a page restore. Since we're not having any luck finding any actual errors, I'm going to have to pretend. Let's go ahead and scroll down a little bit in our script and we're going to run this DBCC or this database console command called IND. This'll give me information on the actual pages that are being used in the database. We need to supply three arguments. The first is the name of the database, Page DB. The second is a table we want to look at, Warehouse.newproducts, and the last one is the index ID that we want to look at. Here I'm saying negative one which means that I want to see all of the indexes. Let's go ahead and run this line on line 37. This is a very small table so it only returns two pages, but that's enough for our current purposes. I'm going to focus on this first item here. We have a file ID of one and a page ID of 306. For the rest of this example, I'm going to go ahead and pretend that this particular page is corrupted and we need to fix it. So armed with these two bits of information, we're ready to begin the restore. Sequel server management studio supports page restores as of version 17. You can find it by right clicking on the database. And first I'm going to have to refresh my database folder and there it is there. I'll right click on it, point to tasks, go to restore, and here at the bottom we'll find page. Now one thing to point out is that I'm in the developer edition of sequel server which is essentially the enterprise edition with a different license level. With enterprise, page recovery can be performed while the database is online. That means that we're swapping out the corrupt data page with a clean copy from the back up without impacting the user's access to the database. On express and standard editions, you'll need to take the database offline first. By coming up here underneath tasks and hitting the Take Offline option. Then, once that happens, you can go down to Restore and then choose Page. In the restore page window, there's a button here at the top that'll check database pages. This'll also identify suspect pages by executing a database console command called check DB physical_only. If I press this button though, again, it's not going to find any corrupt pages in this example. If it did, they would populate in this list right here. What we need to do is add a page manually. I'll press the add button. Now we can fill in our own file ID and page ID. I'll use the numbers that we identified earlier. File ID one, and the page ID 306. We can see specifically which back up files are going to get pulled from by taking a look at the files in the back up section here. It's probably a good idea to verify that these back up files are in good working order so I'll press the verify buttOn here and we'll see at the very top that the back up media was verified successfully. So that's good. Let's go ahead and press the okay button. Sequel server will go through the process of restoring our pages and you can see that the database page DB was restored successfully. I'll go ahead and press the okay button and that'll return us back out to the main interface. At this point if you're on the express or standard edition of sequel server, you'll want to go back over to your database, right click on it, point to Tasks, and then choose Bring Online. So that's it. Page recoveries when applicable can provide a very light weight option for restoring your data from a mild case of drive corruption. It must be noted though that once the drive begins to deteriorate, the problem will only get worse. You could take this early warning sign for what it is and consider making preparations for moving your entire database to new hardware. Before we go, let's go ahead and clean up our server. Scroll down. We'll switch into temp DB and then we'll drop the page DB database. Also remember to go back into the C temp sequel folder and delete that back up file.

Contents