With the new release of SQL Server Denali just around the corner, there are several new features that will be included. This article is going to focus on the improved Graphical User Interface (GUI) enhancements for Page Level Restore. I am only trying to familiarize you with the new features that will be available with the new release of SQL Server Denali. Also, all of the information is based upon SQL Server Denali CTP 3 and could change in the final release, which is currently slated to be the first half of 2012.
Page Level Restore Access
Has anyone ever had the privilege of logging onto a server and trying to run a query and get this kind of error message?
Msg 824, Level 24, State 2, Line 1
SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0x56af1030; actual: 0x56951030). It occurred during a read of page (1:379) in database ID 13 at offset 0x000000002f6000 in file 'C:\Program Files\Microsoft SQL Server\MSSQL11.DENALICTP3\MSSQL\DATA\CorruptMe.mdf'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
Then you would have to take the database offline and run a DBCC Checkdb command and end up with an error that looks similar to this
Msg 8976, Level 16, State 1, Line 1
Table error: Object ID 245575913, index ID 2, partition ID 72057594039173120, alloc unit ID 72057594043564032 (type In-row data). Page (1:379) was not seen in the scan although its parent (1:2737) and previous (1:378) refer to it. Check any previous errors.
Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 245575913, index ID 2, partition ID 72057594039173120, alloc unit ID 72057594043564032 (type In-row data), page (1:379). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 133129 and -4.
Msg 8928, Level 16, State 1, Line 1
Object ID 245575913, index ID 2, partition ID 72057594039173120, alloc unit ID 72057594043564032 (type In-row data): Page (1:379) could not be processed. See other errors for details.
Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 245575913, index ID 2, partition ID 72057594039173120, alloc unit ID 72057594043564032 (type In-row data). Page (1:380) is missing a reference from previous page (1:379). Possible chain linkage problem.
Basically, if you were running the enterprise version of SQL Server 2005/2008, then you could go through the motions of doing a page level restore. The steps were the following.
- Take a full backup of the existing database – this is in case anything happens during the page level restore.
- Find latest backup set and not directories
- Find the page that needs to be replaced. In this example, it is page 379.
- Restore the page- The commands were as follows
USE master;
GO
RESTORE
DATABASE CorruptMe PAGE = '1:379' FROM DISK = 'C:\CorruptMe.bak';
GO
- Finally, you had to backup the tail end of the log and then restore it. All while the database was offline.
Note: If you were running Enterprise version of SQL Server in a mirrored environment, SQL Server would automatically go get the corrupted page from the mirrored set and replace the corrupted page for you.
Assuming you have received this kind of error message, then you understand the importance of one of the new features that will be available in the new version of SQL Server. Under the “Tasks”à”Restore” menu there is now a new menu item that has been added called “Page”.
If you open it up, it will bring you to a page like following. Note that for this example I have manually gone in and corrupted a single page using a hex editor. It has also run the command on the database.
dbcc checkdb with physical_only
DBCC results for 'CorruptMe'.
Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 245575913, index ID 2, partition ID 72057594039173120, alloc unit ID 72057594043564032 (type In-row data), page (1:379). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 133129 and -4.
Msg 8928, Level 16, State 1, Line 1
Object ID 245575913, index ID 2, partition ID 72057594039173120, alloc unit ID 72057594043564032 (type In-row data): Page (1:379) could not be processed. See other errors for details.
CHECKDB found 0 allocation errors and 2 consistency errors in table 'DeadBirdies' (object ID 245575913).
CHECKDB found 0 allocation errors and 2 consistency errors in database 'CorruptMe'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (CorruptMe).
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
It has automatically detected the page corruption for you and listed the corrupted page in the window. It has also listed the current backup sets that it can pull the corrupted page from to correct the problem as well as take a transaction log tail backup and reapply it and roll all of the current transactions forward.
Now, for my example, this process took just mere seconds to complete. But if you are working with a large database and having to restore several pages1000 pages is the max number of pages that can be restored in a single restore operation for SQL 2005/2008), it might take a while. Also, at this point in time, I could not find if the maximum number of pages to be restored in single restore operation has increased or not.
So what do you do next?
You need only press the “OK” button at the bottom of the screen and it will run the following commands behind the scenes.
USE [master]
RESTORE DATABASE [CorruptMe] PAGE='1:379' FROM DISK = N'd:\SQLBackups\CorruptMe_LogBackup_2011-09-22_22-53-50.bak' WITH FILE = 2, NORECOVERY, NOUNLOAD, STATS = 5
BACKUP LOG [CorruptMe] TO DISK = N'd:\SQLBackups\CorruptMe_LogBackup_2011-09-28_15-52-14.bak' WITH NOFORMAT, NOINIT, NAME = N'CorruptMe_LogBackup_2011-09-28_15-52-14', NOSKIP, NOREWIND, NOUNLOAD, STATS = 5
RESTORE LOG [CorruptMe] FROM DISK = N'd:\SQLBackups\CorruptMe_LogBackup_2011-09-28_15-52-14.bak' WITH NOUNLOAD, STATS = 5
GO
Finally, you will be given a completion screen and the database is backup and running.
In conclusion, this GUI enhancement will allow Database Administrators to quickly recover a database at a time that is most critical. These types of errors which in the past were more routine due to the cycling of power on and off to the disk sub systems. These errors took time to recover and you needed to know where to go get the page from the specific backup set. Now, it takes just a few seconds to find the specific page that has the error on it as the history of the backup sets is presented to you and you just point and click. Conversely, you are given the option to script everything out like the figure below. Hopefully in the past you have done them in the correct order. As if I could write the following script under pressure (DBA humor)!
USE [master]
RESTORE DATABASE [CorruptMe] PAGE='1:379' FROM DISK = N'd:\SQLBackups\CorruptMe_LogBackup_2011-09-22_22-53-50.bak' WITH FILE = 2, NORECOVERY, NOUNLOAD, STATS = 5
BACKUP LOG [CorruptMe] TO DISK = N'd:\SQLBackups\CorruptMe_LogBackup_2011-09-28_15-52-14.bak' WITH NOFORMAT, NOINIT, NAME = N'CorruptMe_LogBackup_2011-09-28_15-52-14', NOSKIP, NOREWIND, NOUNLOAD, STATS = 5
RESTORE LOG [CorruptMe] FROM DISK = N'd:\SQLBackups\CorruptMe_LogBackup_2011-09-28_15-52-14.bak' WITH NOUNLOAD, STATS = 5