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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
/* This example uses an OFFLINE restore, which is applicable to all versions of SQL Server. */ USE master; GO /* The NORECOVERY statement in this last log backup makes the database "offline" - you don't actually set it OFFLINE. */ BACKUP LOG TestRestoreOffline TO DISK=N'D:\SQL Backups\TestRestoreOffline-LogBackup2.trn' WITH NORECOVERY; GO /* Restore full backup, specifying one PAGE. I used sys.dm_db_database_page_allocations to find the page number. */ RESTORE DATABASE TestRestoreOffline PAGE='1:293' --Have multiple? Separate with commmas. FROM DISK=N'D:\SQL Backups\TestRestoreOffline-Backup1.bak' WITH NORECOVERY; /* Restore log backups */ RESTORE LOG TestRestoreOffline FROM DISK=N'D:\SQL Backups\TestRestoreOffline-LogBackup1.trn' WITH NORECOVERY; RESTORE LOG TestRestoreOffline FROM DISK=N'D:\SQL Backups\TestRestoreOffline-LogBackup2.trn' WITH NORECOVERY; /* Bring database "online" */ RESTORE DATABASE TestRestoreOffline WITH RECOVERY; |
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).
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
/* This is an example of an online page restore. */ USE master; GO /* Restore full backup, specifying one PAGE. I used sys.dm_db_database_page_allocations to find the page number. */ RESTORE DATABASE TestRestoreOnline PAGE='1:293' --Have multiple? Separate with commmas. FROM DISK=N'D:\SQL Backups\TestRestoreOnline-Backup1.bak' WITH NORECOVERY; /* Restore log backups */ RESTORE LOG TestRestoreOnline FROM DISK=N'D:\SQL Backups\TestRestoreOnline-LogBackup1.trn' WITH NORECOVERY; /* 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 /* Restore the last log backup */ RESTORE LOG TestRestoreOnline FROM DISK=N'D:\SQL Backups\TestRestoreOnline-LogBackup2.trn' WITH NORECOVERY; /* Restore database */ RESTORE DATABASE TestRestoreOnline WITH RECOVERY; |
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.
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!
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.
I think you’re missing NORECOVERY from the online full backup restore statement.
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.
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
Robbe, thank you for pointing that out. I see the issue with code now and it’s been fixed.
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!
Great point, Argenis!
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!).
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.
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.
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
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!
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
Jessi – the full backup has to be from before the page got corrupted.
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!
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?)
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.
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
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,
Yep!
Thanks for the confirmation Brent. So, in that case, it’s not really an ONLINE restore right?
Sorry, if I am missing something there.
Sorry, the original author isn’t here anymore, but you can totally play around with the code and see how it works. (I don’t do this particular thing myself.) For questions on how features work, head on over to a Q&A site like https://DBA.StackExchange.com. Thanks!
Will do. Appreciate your response. Thanks!