需要联机页面级别恢复的帮助吗?SQL11 SSMS是您的朋友!

在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