在线页面级还原需要帮助吗？SQL11 SSM是您的朋友！

在SQL Server 2005中，Microsoft引入了在线还原，可以在同一数据库的其他部分可以恢复数据库的部分。在同一版本中，还引入了页面级还原。这两种技术的组合是在线页面级还原，它允许在数据库可用时恢复一个或多个数据页面。对于24x7商店来说，这是一个很好的功能，但需要一些技巧和练习。SQL11 Denali使它变得容易。首先，在线页面级还原是企业版及更高版本的功能。它还要求数据库处于完整或散装的恢复模型，并且首选完整的恢复模型。它的主要目标是能够恢复和修复损坏的页面，而无需离线数据库的其余部分。某些特殊数据页面无法在线恢复，但可以离线恢复。离线还原是进行整个数据库脱机的经典方式。 This type of restore can be done with any edition. A Data Page can be damaged in a variety of ways. For instance, during a power failure or hardware issue, a data page can become corrupt and marked “suspect”. The PAGEVERIFY setting on the database defines how the database picks up on suspect pages. The recommended setting and default is now CHECKSUM which involves a mathematical calculation for each page. If things don’t add up, the page is marked suspect. The usual symptom is an 823 or 824 error in the Error Log. If you are lucky, only a few rows are affected. If you are unlucky, the page may affect a whole table (if it is the root page) or the whole database (if it is the database boot page). SQL Server makes an entry in the MSDB table “dbo.suspect_pages” for each such data page. An event_type of 2 is a CHECKSUM error. The Online Page-Level Restore has a unique restore sequence. Normally we follow the strategy of backing up the “tail” of the log first, when we know that a database needs to be restored. This captures the latest updates for recovery even if the data files are damaged or unavailable. However, with an Online Page-Level Restore, we are told to Restore the damaged page(s) first using the PAGE clause of the RESTORE DATABASE statement, then restore the chain of log backups since that backup was made. Only then should we backup the “tail” of the log and then immediately restore it again. This has the effect of bringing the restored page up to date and synced with all the other pages. This special sequence is all documented in Books Online. It’s a tricky sequence and not intuitive. But it works. So how does SQL 11 Denali SSMS help out? Right-click the database in Object Explorer, then under Tasks, select Restore then choose Page…The Restore Page dialog appears. If you don’t know the page numbers that are suspect, click “Check Database Pages” – this does a DBCC CHECKDB PHYSICAL_ONLY and will copy the suspect page numbers into the list. Alternatively, you can look up the damaged page number(s) in the Error Log messages or in the suspect_pages table and enter them manually. An example page number may be of the format “1:743” which translates to a File Id of 1 and a Page Id of 743. SSMS requires you to enter the File Id and Page Id separately. SSMS knows which backups have been made via the Backup History tables in MSDB so will suggest a valid restore sequence. You can click the Verify button to check the validity of the backups. You can also click the Script/New Query Editor Window option to generate a valid script before clicking OK or Cancel (if you prefer to examine the script before running). In my testing with CTP3, the script was valid and the dialog worked as designed using the upgraded version of AdventureWorks2008R2 database (available on codeplex.com) as a sample. The Online Page-Level Restore is a powerful feature that is normally a complex operation to pull off but now SQL 11 Denali SSMS makes it easy. Watch this space for more cool Denali features. Cheers Brian MSDN SQL Server Denali Page Restores:http://msdn.microsoft.com/en-us/library/ms175168(V=SQL.110).aspx