Page Restoring

In SQL Server 2005 Book on Line, it mentioned that you can restore a database by pages instead of to restore the whole database. But, it also says "Page restore is supported only for read/write filegroups." If this is true, then how about most of the database files are not designed as filegroup (or just primary filegroup only) ? Can they enjoy this convenience too? Also, when you find a suspect page in suspect_pages table in msdb, how you find out in which log_backup file that contains this bad page?

Thanks,

Charley

Hi Charley.

Any read/write filegroup can make use of page-level restores, which includes any filegroup (even the primary), so long as the given filegroup hasn't been set as read-only. All database files are always part of a filegroup, so all database files would be eligible, so long as they are included in a read/write filegroup.

As for how to determine which backup contains a suspect page, there is no way to figure that out easily from the suspect_pages table (in this case, by easily I mean there is nothing in the table that would immediately tell you it is in backup x or y). You could usually assume it would be in any backup after the last_update value from the table, so long as the backup in question contains the page in question (for example, a log backup wouldn't, nor would a diff backup if the page in question hadn't been updated from the last full to the given diff backup). Of course, it also would depend on the event_type associated with the given page as well in some situations.

Repost if still unclear,

HTH,

|||

The sequence for restoring a database page is fundamentally the same as that for restoring a database file, except that you're moving FAR less data.

You need to start with a full backup, but specify only the page you want to restore. You would then apply a differential, and all log backups up to the current time. The page MUST be rolled forward to the same point in time as the rest of the database (i.e. now). That implies that you need to be in full recovery mode. At each stage, you specify the page(s) that you want to restore.

|||

Hi Chad and Kevin,

Thanks for your reply. The first part of my question is quite clear now. The implied question behind the second part of my question is actually how practical to take advantage of the new page restoring features. From your analysis and the example from BOL, we can see the way and the syntax of page restoring is very similar to a traditional restoring except the PAGE clause. As you both mentioned, the page restoring still have to start from full backup, diff backup if any and transaction log backups. My understanding, with provided page IDs in PAGE clause, the page restoring is actually using the full backup, diff backup and all log backups that may or may not be directly related to the suspect pages for scanning where the damage occurred, but only restore the suspect pages (not the whole data file) from the related backup. If the a specific transaction log backup contains the suspect page, in order to move data far less than normal restoring, the page restoring will only restore the question page from the located log backup. Also, as Kevin mentioned, all log backup up to the current time have to be included in page restoring. Therefore, to find out which backup contains the suspect page is not really important any more. Please let me know if my understanding is correct.

Thanks,

Charley

|||

Your understanding is correct. The restore sequence needs to include all the same backups that a complete restore would, with the exception that you have the option of breaking your full/differential backups up by filegroup. In this case, you'd only need the backups for the relevant filegroup.

While you do need to touch all of the same backups, as I pointed out, the volume of data moved (and hence the time to accomplish the restore) is FAR less. Compare restoring a 1TB database to an 8k page!

Also, if you are running Enterprise edition, the entire rest of the database, including all other pages in the file being restored to, stay ONLINE during the restore. The only user that would even notice that anything was happening would be one that happened to need access to data on that one page. This is a huge win in availability!

|||

Hi Kevin,

Thanks so much for your quick response and excellent explanation. You also answered my next question about the the online restoring.

Thanks,

Charley

|||

Hi Kevin,

Sorry, I have one more question about the online restoring. In BOL, it mentioned that a database is considered to be online whenever the primary filegroup is online, even if one or more of its secondary filegroups are offline. It also points out that during an online file restore, any file being restored and its filegroup are offline. My question is if the resotre file is contained in primary filegroup will it be taken offline too? In this case, how to make the online restoring works.

Thanks,

Charley

|||

The limitation of online restores is that any time the primary filegroup is offline (which includes any file in that filegroup being offline), the database is offline.

The way to mitigate this of course, is to lay out the database with a very small primary filegroup containing critical data without which the database is useless anyway. Then in the event of a disaster recovery, you can get the core of the database up quickly, and bring up other filegroups (say historical information) at a more leisurely pace.

|||

Thanks again.