SQL Server MVP Deep Dives 2, Chapter 8
Although performing page restores isn’t likely to be something that has to be done on a regular basis, anyone working with large databases should at least be familiar with the technique and requirements in case page restores should become necessary. – SQL Server MVP Deep Dives, chapter 8
So far the chapters in Deep Dives 2 have been about design and theory of how databases should be set up. There has been some practical advice and even some tools provided by the authors for accomplishing the concepts they describe, but Gail Shaw’s chapter on Page restores is the first one in the book that provides an end to end solution for a single task. Which is to say, I loved it. I have never had the occasion to perform a page restore, but after reading this chapter I know I have the steps to perform this task at my fingertips.
Ms. Shaw starts by listing the various levels of restores that are possible; Full database restore, Filegroup restore, data file restore, and, as of SQL Server 2005, page restore. A page restore is typically preformed when corruption has been identified within a database using tools such as DBCC CHECKDB. A very larger database can take a lot of offline time to restore, but if you can just restore the corrupt pages you will save time and with Enterprise edition you can do the restore while the database is still online. Gail is quick to point out that a page restore cannot be used to bring data back to a specific point in time, which I have to confess I thought might be a way to use it when I first started reading about this feature. She points out that some other limitations are that the database cannot be in Simple Recovery mode and certain types of pages are not available to be restored; allocation pages, file header pages, and data boot page. I confess that I had to do a little research to determine the function of these different types, but the page restore works for data pages and this is most of the database.
The intial restore command is very similar to a typical restore command, but you indicate the pages:
RESTORE DATABASE ExampleDB
PAGE = '1:46000','1:46001'
FROM DISK = 'E:\backups\ExampleDB.bak'
The reason you can’t restore to a point in time is that after this restore from the last Full backup, you then need to restore all of the log file backups to bring the database up to the present state. As Ms. Shaw outlines this process she also provides a script to use the backup sets to parse out all of the restore commands. Of course in the next version of SQL Server, SQL Server 2012, the ability to do a page restore from the GUI in Management studio has been added. Ms. Shaw shows a screen shot of how this works in 2012 and points out that it uses the backup sets automatically. And if you don’t trust the GUI in SSMS (I can’t imagine why?) you will have the ability to script out the steps of the GUI wizard.
I don’t do much with backups and restores these days, but after I finished reading this chapter I was looking around waiting for some corrupt pages to restore so I could show everyone how it worked.
Chapter Eight SQL Server MVP:
Gail Shaw (B|T) is a database consultant from Johannesburg, South Africa, specializing in performance tuning and database optimization. Before moving to consulting, she worked at a large South African investment bank and was responsible for the performance of the major systems there. Gail is a frequent poster on the SQLServerCentral forums and writes for the same site. She has presented multiple times at both TechEd Africa and the PASS Community Summit. She was awarded MVP for SQL Server in July 2008.