Page restores with Restore-DbaDatabase
Oops, missed a day yesterday. In my defence repairing our payment system is pretty important 🙂
You may not alway want to restore a whole database. A common piecemeal way of restoring a database is just restoring pages that have become corrupted
What is a page? All data in SQL Server is handled in units called pages. A SQL Server page is an 8kb allocation of disk or memory. Memory? Yes, it make things easier if you use the same size for both as you don’t need to convert or split things up, which will only slow things down.
When I say all data in SQL Server is store in page size chunk, I mean pretty much everything, data, indexes, binary data, the SQL Server internal data defining tables, view, stored procedures and so on. The main thing not store on pages are Transaction Log records, they have their own format
Unfortunately sometimes those pages can become corrupted. This could be through drive failures, controller failues or very rarely a bug in the application.
You may even have some corrupted pages in your databases. If the page isn’t read very often then you might not notice it’s corrupted. But at some point you will.
Don’t think, that just because you can can backup and restore a database that it’s guaranteed corruption free, it may still sneak through.
The only way to be sure is to run regular DBCC checks. Really you want to be running it nightly. Unfortunately, a DBCC check involved reading every page of data, so has an impact on your SQL Server instance’s performance. You can can offload the checks to other instances by restoring the database, but this can make tracking down the root cause of the corruption harder (Brent Ozar with a timely post on this very subject)
So what do you do when you find you’ve got corruption?. You first need to find out when the corruption occured, this is why it’s important to check regularly as you want to know ASAP not weeks after the fact.
We’re going to restore just the corrupted pages from our SQL Server backups using Restore-DbaDatabase. We need to go from the full backup BEFORE the corruptions occured, and then roll the pages forward using the transaction logs. If you catch the corruption early there’s less work for SQL Server to do to recover these, finding out a week after your last full backup can make this a lengthy process.
To find just the pages we need to restore we’re going to use another dbatools command,
Get-DbaSuspectPage. This looks at the
msdb.dbo.suspect_pages table, which keeps a log of all pages in the SQL Server instance which have been marked as suspect. Keeping an eye on this table can help spot corruption, but unless every page in your database gets read regularly it won’t pick up every bit of potential corrupt.
So, your luck’s run out and you’ve found you’ve a lot of corrupted data pages. Hopefully this is just after a full backup.
First we run Get-DbaSuspectPage to find out how bad it is and which pages are suspect:
$pages = Get-DbaSuspectPage -SqlInstance server1\instance -Database MyProdDb
The number of pages (
$pages.count) will give you an idea of how bad things are.
For the next step you need to know which Edition of SQL Server you’re running:
- If you’re running Enterprise Edition, then you can do this online. Users may notice a drop in performance, but will be able to access the database.
- If you’re running Standard Edition, then unfortunately it’s an offline process. Users will not be able to use the database until the restore and recovery have finished.
SQL Server won’t warn you, so check before you run this. This will help you check:
Connect-DbaInstance -SqlInstance server1\Instance | Select-Object -Property Edition
If you need to, let your users know
Now we’re going to use our most recent backups to recover those pages:
Get-DbaDbBackupHistory -SqlInstance Server1\instance -Database pagerestore -last | Restore-DbaDatabase -SqlInstance server1\instance -PageRestore $pages -TrustDbBackupHistory -DatabaseName MyProdDb -PageRestoreTailFolder c:\temp
I’m using Get-DbaDbBackupHistory to get the BackupHistory to speed things up. You can pass in files if you want to, but it will take a bit longer
There’s a couple of new parameters on the Restore-DbaDatabase command which are only used with this type of restore:
This lets Restore-DbaDatabase know that we’re going to be doing page restores by passing in a list of the pages than need restoring. In this case we’re passing in the output from Get-DbaSuspectPage as it’s the easiest way
As part of the recovery phase for a page restore SQL Server needs to take a tail log backup of the database and then restore it. The folder passed in via this parameter is the location that the tail backup will be put in. So make sure SQL Server can read and write to the location and that you have plenty of space to use.
As soon as the Page restore has completed, take a full backup of your database. Now if anything breaks you can start from a known good point to start from. Definitely start looking at why the corruption may have occured, sometimes it’s just plain bad luck other times it points to issues in applications or hardware, if you can find the culprit and remove it now it’ll be so much better.
SQL Sever database corruption happens, not often, but it can happen so it’s best to be prepared to jump on it as soon as it appears. So CHECKDB regularly and keep an on msdb.dbo.suspect_pages. If you start seeing lots of corruption then dig down to find out what’s causing it.
All posts in this series can be found at 31 Days of dbatools Backup and Restores