sqlskills-logo-2015-white.png

Search Engine Q&A #22: Can all page types be single-page restored?

I've had a few follow-ups on my two posts about boot page and file header page corruption – asking if its possible to do single-page restore operations for these pages. Let's try:

CREATE DATABASE BootPageTest;
GO

— Single page restore is only possible using the FULL recovery model
ALTER DATABASE BootPageTest SET RECOVERY FULL;
GO

BACKUP DATABASE BootPageTest TO DISK = 'C:sqlskillsBootPageTest.bck';
GO
BACKUP LOG BootPageTest TO DISK = 'C:sqlskillsBootPageTest.trn';
GO

RESTORE DATABASE BootPageTest PAGE = '1:9' FROM DISK = 'C:sqlskillsBootPageTest.bck';
GO

Msg 3111, Level 16, State 1, Line 2
Page (1:9) is a control page which cannot be restored in isolation. To repair this page, the entire file must be restored.
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.

The answer is no. The following page types cannot be restored using single-page restore:

  • File header pages (see here)
  • Boot page (see here)
  • GAM, SGAM, DIFF map, ML map pages (see here)

7 thoughts on “Search Engine Q&A #22: Can all page types be single-page restored?”

  1. Can I use single page restore to restore a single table from a backup of the database & the tran. log?
    I am an Oracle DBA with responsibility for some SQL Server 2005 databases. I miss the import/export
    feature in Oracle that lets me export all the tables of a database, a schema, or a single table along with
    its structure, data, indexes, permissions, etc. With a full database export, I can restore the whole
    database, one schema, several tables, or one table at my option. I am really looking for similar
    functionality in SQL Server.

    Reply

  3. ERROR:
    Msg 829, Level 21, State 1, Line 4
    Database ID 5, Page (1:246) is marked RestorePending, which may indicate disk corruption. To recover from this state, perform a restore.

    The above error is produced when a SELECT operation is a performed on the deliberately corrupted user table.

    I performed the following on [Microsoft SQL Server 2014 – 12.0.2000.8 (X64) Developer Edition], but it doesn’t seem to resolve the issue:

    1. Create test database (FULL recovery model)
    2. Create a table and insert some rows
    3. Take a FULL Backup
    4. Corrupt a clustered index page using DBCC WRITEPAGE
    5. Perform an online single-page restore
    RESTORE DATABASE [TEST]
    PAGE = ‘1:246’
    FROM DISK = ‘D:\SQLBackups\20150531_104030_TEST_FULL.bak’;
    GO

    CHECKDB OUTPUT:
    Msg 8939, Level 16, State 98, Line 4
    Table error: Object ID 245575913, index ID 1, partition ID 72057594040549376, alloc unit ID 72057594045792256 (type In-row data), page (1:246). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 133129 and -6.

    Msg 8928, Level 16, State 1, Line 4
    Object ID 245575913, index ID 1, partition ID 72057594040549376, alloc unit ID 72057594045792256 (type In-row data): Page (1:246) could not be processed. See other errors for details.

    Msg 8980, Level 16, State 1, Line 4
    Table error: Object ID 245575913, index ID 1, partition ID 72057594040549376, alloc unit ID 72057594045792256 (type In-row data). Index node page (1:280), slot 0 refers to child page (1:246) and previous child (0:0), but they were not encountered.

    Msg 8978, Level 16, State 1, Line 4
    Table error: Object ID 245575913, index ID 1, partition ID 72057594040549376, alloc unit ID 72057594045792256 (type In-row data). Page (1:281) is missing a reference from previous page (1:246). Possible chain linkage problem.

    CHECKDB found 0 allocation errors and 4 consistency errors in table ‘t1’ (object ID 245575913).
    CHECKDB found 0 allocation errors and 4 consistency errors in database ‘TEST’.
    repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (TEST).

    I have not tested this simple demo on another version/edition but wanted to know if there was a step missing in resolving the issue. Thank you.

    Reply

  4. The FULL backup is taken before the page is intentionally corrupted. I performed the BACKUP operation using the “CHECKSUM” operation to ensure the page was not somehow corrupt, but a page level restore still does not succeed. I’m thinking it is the version/edition that may be the issue. Thank you.

    Reply

    1. It works in all editions (and Developer is Enterprise). And I know it works on 2014 as I demo’d it 3 weeks ago in class.

      Send me your end-to-end repro script in email please.

      Reply

Leave a Reply

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

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.

Collaborate With Us
Choose a Course