How to Restore a Page in SQL Server Standard and Enterprise Edition

One of the many restore features in SQL Server is the ability to restore one or more pages of data. This can be very convenient in some narrow situations – for example, corruption occurs on one page or an oops update is made to one record.

The page restore process is not straightforward, however, and, as I recently discovered, the Books Online article about it is confusing. See, you have to perform the restore offline in all versions except Enterprise Edition – but the only example Books Online gives is…Enterprise Edition.

Here’s a straightforward breakdown of how to do a page-level restore both offline and online. For the sake of brevity, let’s say I have two databases – TestRestoreOnline and TestRestoreOffline. Both are in Full recovery. Each has one damaged page, which I’m going to restore. (For a full demo script, click here.)

Offline

You should already have an existing full backup – mine is at D:\SQL Backups\TestRestoreOffline-Backup1.bak. I also have one transaction log backup, D:\SQL Backups\TestRestoreOffline-LogBackup1.trn.

That is an offline page restore. By putting the database in a NORECOVERY mode before the restores begin, the database can’t be accessed.

Online

An online page restore is only available in Enterprise Edition. This will allow users to access other objects in the database while you are restoring the page(s) needed.

You should already have an existing full backup (D:\SQL Backups\TestRestoreOnline-Backup1.bak) and log backup(s) (D:\SQL Backups\TestRestoreOnline-LogBackup1.trn).

The steps for an online restore differ slightly. The tail-log backup is taken after all the other log backups are applied, instead of at the beginning of the sequence.

Previous Post
#SQLPASS #Summit14 Keynote LiveBlog: Dr. Rimma Nehme
Next Post
Using sp_BlitzCache™ Advanced Features

24 Comments. Leave new

  • Quick question, why are the log restores necessary after the page restore from the full backup? You would think that after the page is restored in the first step that you could finish at this point. What am I missing?

    Thanks!

    Reply
    • Chuck – because transactions may have occurred since the full backup that actually modified the page. You have to check the log for those modifications to bring the page to its current desired state.

      Reply
  • I think you’re missing NORECOVERY from the online full backup restore statement.

    Reply
    • Jes Schultz Borland
      November 12, 2014 9:29 am

      No, actually, we don’t put the database in NORECOVERY at that point. If we did, it would take the database offline, and that’s the opposite of an online restore.

      Reply
      • Robbe Goetmaeckers
        November 13, 2014 5:38 am

        Which is odd, because if you look at the technet article about online restores there’s an example of an online page restore which does include the NORECOVERY on the database restore.

        And it clearly states “This example performs an online restore”. I don’t have the resources to test it right now, but I take it the technet article is wrong?

        http://technet.microsoft.com/en-us/library/ms175168(v=sql.110).aspx

        Reply
  • Just a reminder that online page restores also work on Developer edition, which is what most folks should be running on their test environments (and their dev laptops). It’s pretty cool to be able to test this functionality on one’s little box 🙂

    Good article, Jes!

    Reply
  • I didn’t even know you could do this as an online, which is def. good to know! Do you know if you can you do page level restores from transaction log backups, out of curiosity? I am just wondering from the perspective of if the page that is corrupt and was the result of a new page/page split that occurred after your most recent full backup. I feel like the answer is going to be no and that I’d just have to restore the full/diffs elsewhere, roll the transaction log backups in, do a full backup, and use that with a tail log of the live db (which kinda makes me hope i’m not right!).

    Reply
    • Jes Schultz Borland
      November 14, 2014 3:23 pm

      No, only the database backup contains database pages. The log file is more of a list of transactions – and the steps to roll them forward/backward.

      Reply
  • Thank you very much. I looked for offline page restore on the internet but could not find for quite a while till I hit your article. Appreciate for writing this article.

    Reply
  • does the Full backup used for the page restore have to be from a date before the page corruption happened or can I run a full backup a couple of daays before I am planning to do the page restore, then a differential before the actual page restore and use the latest Full to restore the page? Also, does restoring the page (only one page that was corrupted) create at risk for any of the other tables in the database? I am trying to decide which option will be better for me to fix for one corrupted page for one table in a large DB (3 TB) in prod. Using the page restore vs dbcc checktable repair_allow_data_loss

    Reply
    • Daniel – if you do a full backup of a database that has corruption, the full backup will contain the corrupt pages. You’ll need a backup from before the corruption occurred. Hope that helps!

      Reply
  • hello, very interesting article.
    I have a large DB with one page file corrupted for one table. The Full backup that is used to restore the page file does it have to be from a date prior to the page corruption or can it be after? Also, does it running the restore page risk any of the other tables in the database? I am trying to decide which option is less risky in a production environment to fix one page corruption for only one table, to do a page restore or to do dBCC dbcc checktable repair_allow_data_loss

    Reply
  • Quick question:

    Is there a way to find out when the page got corrupted, so that I can use a full backup from before that date?
    Eg. We run a DBCC CHECKDB every Sunday. Full backups every night. On Monday I notice DBCC said some pages are corrupted. Which full backup (from the previous week) should I restore the page from?

    Thanks!

    Reply
    • New DBA – let’s think through that question for a minute. Say the page became corrupted because you had a hard drive start to fail. How would you go about finding that out? (And more interestingly, how would SQL Server know?)

      Reply
  • Alberto Gonzalez
    December 30, 2015 4:33 pm

    I guess you backed up the log to bring the database in a consistent state, the thing that i do not understand is why you did it after restoring all the old transactions logs, I thought that this action was meant to be performed after the first restore.

    Reply
  • question! you said
    “The steps for an online restore differ slightly. The tail-log backup is taken after all the other log backups are applied, instead of at the beginning of the sequence.”
    Don’t I have to take tail log backup FIRST? I might be wrong, if so, can you tell where ( in your script) should I take tail log backup?
    Thanks

    Reply
  • Quick one,
    Under Online restore, you have mentioned following stmt;
    /* With Enterprise Edition, the “online” restore – a log backup with NORECOVERY – goes here. */
    BACKUP LOG TestRestoreOnline TO DISK=N’D:\SQL Backups\TestRestoreOnline-LogBackup2.trn’
    WITH NORECOVERY;
    GO

    Doesn’t this “NORECOVERY” clause put the DB into restoring state?
    Cheers,

    Reply
  • Will do. Appreciate your response. Thanks!

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.

Menu
{"cart_token":"","hash":"","cart_data":""}