Tuesday, 27 October 2015 00:00

SQL FAQ – How Does A Page Level Restore Improve SQL Server Recovery Provisions?

Rate this item
(0 votes)

For very large Microsoft SQL Server databases, a complete restore operation can take many hours. During this time the database cannot be used to prevent data being entered and lost as information is copied back from tape or disk. Obviously in a high-availability environment any downtime is costly, so keeping it to a minimum is essential.

Fortunately page level restore techniques can be used to keep recovery times to a minimum by reducing the amount of data that needs to copied back from the backup media. Since the release of Microsoft SQL Server 2005, DBAs have had the option of carrying out a ‘page level restore’ which allows them to recover a ‘handful’ of pages, rather than having to restore entire datasets and copy information back into the original database.

The page level restore operation is perfect for situations where data becomes corrupted during writes through a faulty disk controller, misconfigured antivirus software or an IO subsystem. Better still, restore level operations can be performed online for Enterprise editions of Microsoft SQL Server.

Prerequisites

As with any database recovery operation, page level restores are reliant on having a complete backup from which to work. If such a backup is not available, you will need to investigate an alternative method of recovering data from the server disks direct.

And although you can carry out the page level restore with the database online, you may decide to keep things safe by switching to single user mode whilst you transfer data using:

ALTER DATABASE <DBName> SET SINGLE_USER
WITH ROLLBACK AFTER 10 SECONDS
GO

This command ensures that everyone is out of the system and cannot enter until you change the mode back again. You will also want to ensure that you have the end of the log file backed up so that you have all transactions fully accounted for and to prevent any further data loss:

BACKUP LOG DBName
TO DISK = N'X:\SQLBackups\DBName_TailEnd.trn'
GO

Discovering which pages are corrupt

Usually the first sign that something is wrong will be an inconsistency error generated when a select statement is executed. The Microsoft SQL Server Messages pane will show an error like:

Msg 5242, Level 22, State 1, Line 1
An inconsistency was detected during an internal operation in database ‘DBName’(ID:9) on page (1:254).
Please contact technical support. Reference number 4.
As you would expect, the page number referenced, 254, is the corrupt page that needs to be recovered.

Carrying out a page level SQL Server recovery

The Microsoft SQL Server page recovery operation is very similar to that of a full recovery, the only difference being the use of the NORECOVERY command:

RESTORE DATABASE DBName
PAGE = 'fileid:pageid'  -- e.g. 1:254
FROM DISK = 'X:\SQLBackups\DBName_lastFull.BAK'
WITH
NORECOVERY
GO

This will the re-insert the corrupted page from backup without going through the time-consuming process of a full SQL Server recovery. You can recover more than one page at a time simply by appending additional fileid:pageid combinations separated by commas.

You can test that the data has been recovered successfully by running DBCC CHECKDB on the affected database – it should complete without error.

Finishing up the page level SQL Server recovery

Although the corrupt page has been recovered successfully, you will also need to apply the transaction logs to ensure that all data is present and correct. For each transaction log generated since your last full backup, run the RESTORE LOG command:

RESTORE LOG DBName
FROM DISK = 'X:\SQLBackups\DBName_LogFileFrom2PM.TRN'
WITH NORECOVERY
GO
RESTORE LOG DBName
FROM DISK = 'X:\SQLBackups\DBName_LogFileFrom215PM.TRN'
WITH NORECOVERY
GO

Again, note the NORECOVERY option will ensure that you restore nothing but the specified log files. Finally you need to apply the tail log backup taken in the Prerequisites section above:

RESTORE LOG DBName
FROM DISK = 'X:\SQLBackups\DBName_TailEnd.trn
WITH RECOVERY

This time you do want to recover the database, hence the WITH RECOVERY command.

At this point you can re-test the database to ensure that everything is working as it should. Once you are satisfied, switch the database back to multi-user mode to restore access for your users:

ALTER DATABASE AdventureWorks SET MULTI_USER
GO

And there you have it, corruption problems resolved without having to undergo a full SQL Server recovery. The page level restore option ensures that downtime and user inconvenience is kept to a minimum, as are the costs associated with a database outage.

Reference :http://blog.krollontrack.co.uk/concepts-explained/sql-faq-how-does-a-page-level-restore-improve-sql-server-recovery-provisions/

Last modified on Tuesday, 27 October 2015 10:57
Data Recovery Expert

Viktor S., Ph.D. (Electrical/Computer Engineering), was hired by DataRecoup, the international data recovery corporation, in 2012. Promoted to Engineering Senior Manager in 2010 and then to his current position, as C.I.O. of DataRecoup, in 2014. Responsible for the management of critical, high-priority RAID data recovery cases and the application of his expert, comprehensive knowledge in database data retrieval. He is also responsible for planning and implementing SEO/SEM and other internet-based marketing strategies. Currently, Viktor S., Ph.D., is focusing on the further development and expansion of DataRecoup’s major internet marketing campaign for their already successful proprietary software application “Data Recovery for Windows” (an application which he developed).

54 comments

  • Comment Link Manual Thursday, 16 November 2017 09:09 posted by Manual

    Do you got slipping balcony door in your abode?

  • Comment Link Heidi Monday, 13 November 2017 17:27 posted by Heidi

    Very good post. I'm dealing with some of these issues as well..

  • Comment Link Adolph Sunday, 12 November 2017 06:08 posted by Adolph

    Admiring the hard work you put into your website and in depth information you provide.

    It's good to come across a blog every once in a
    while that isn't the same out of date rehashed material.
    Wonderful read! I've saved your site and I'm adding your RSS feeds to my Google account.

  • Comment Link Mitchel Sunday, 12 November 2017 04:37 posted by Mitchel

    We are a group of volunteers and opening a new scheme in our community.
    Your site provided us with valuable information to work on. You've done a formidable
    job and our entire community will be thankful to you.

  • Comment Link Thomas Sunday, 12 November 2017 03:23 posted by Thomas

    Now I am going to do my breakfast, when having my breakfast coming
    again to read further news.

  • Comment Link Blaine Monday, 06 November 2017 13:29 posted by Blaine

    Thanks in support of sharing such a fastidious idea,
    paragraph is good, thats why i have read it fully

  • Comment Link Devon Thursday, 02 November 2017 12:58 posted by Devon

    Oh my goodness! Incredible article dude!
    Thank you, However I am having problems with your RSS.

    I don't know the reason why I am unable to subscribe to it.
    Is there anyone else getting similar RSS problems?
    Anybody who knows the solution will you kindly respond?
    Thanx!!

  • Comment Link Isobel Thursday, 02 November 2017 09:35 posted by Isobel

    Wow, that's what I was searching for, what a stuff! existing here at this weblog,
    thanks admin of this site.

  • Comment Link Hilario Thursday, 02 November 2017 07:04 posted by Hilario

    Your means of describing all in this paragraph is actually fastidious, every one be able to simply be aware
    of it, Thanks a lot.

  • Comment Link Lesley Thursday, 02 November 2017 02:27 posted by Lesley

    Greetings! Very helpful advice in this particular post! It is the little changes
    that will make the most significant changes. Many thanks for
    sharing!

Leave a comment

Make sure you enter the (*) required information where indicated. HTML code is not allowed.