Restore-DbaDatabase and Point in Time restores
So far in this series, we’ve just been restoring to the latest point in time that is in the backups. But often you want to restore to a particular point in time. Maybe just before someone ran that TRUNCATE they shouldn’t have or because you need to look into an error in more detail
So here’s a little secret, if you dig into the Restore-DbaDatabase source code you’ll we always do a Point in Time restore, Just that the default point in time is 1 year ahead. This helps to keep the logic cleaner throughout the Restore-DbaDatabase pipeline
But we can do a bit more than that. Restore-DbaDatabase offers 2 types of Point in Time restores:
- Restore to a specific Point in Time
- Restore to a specific marked Transaction
These 2 options cover almost any reason why you’d want to restore to somewhere other than the end of the backup chain. Examples are below the fold
Point in Time Restores
This is the sort of Point in Time restore that most people think about. This is when you say ‘I want database db1 restored just as it was at 14:09 12/05/2020’
This is easy to do, you just provide the date and time you want to restore to:
Restore-DbaDatabase -SqlInstance server1\instance -Path c:\backups\db1 -RestoreTime (Get-Date '14:09 12/05/2020')
And we’ll do the work to restore that database to that point in time.
The resason for using
(Get-Date '14:09 12/05/2020') as the input parameter is that we’re expecting a DateTime object and that’s the easiest way to generate one.
This also means that you can easily cope with the other request of ‘Can you restore db2 to 30 minutes ago’ quickly as well:
Restore-DbaDatabase -SqlInstance server1\instance -Path c:\backups\db1 -RestoreTime (Get-Date).AddMinutes(-30)
Restoring to a specific Transaction
SQL Server also offers the ability to restore your database to a specific transaction via a Mark in the Transaction Log. This can be useful if you wanted to be able to restore to the beginning or the end of your overnight processing run without having to know exactly what time it started or finished.
To make a transaction in the log you declare it like this:
BEGIN TRANSACTION OvernightProcessing WITH MARK ... ... COMMIT TRANSACTION
WITH MARK tells SQL Sever to mark the transaction log with the transaction name, in this case ‘OvernightProcessing’.
You don’t even need to wrap the whole event with a transaction. You just need a marked transaction, that could be on that updates a work table or some other minor piece of work.
If you wanted to restore to that transaction you would use the following command:
Restore-DbaDatabase -SqlInstance server1\instance -Path c:\backups\db1 -StopMark OvernightProcessing
and off we’ll go. The restored database will be in a Restoring state when it completes with the OvernightProcessing transaction uncommitted. To open the database you need to recover it:
Restore-DbaDatabase -SqlInstance server1\instance -Database db1 -Recover
Which will recover the database and commit the transaction. Leaving you with a database just as it was at the end of the overnight run.
If you want to restore the database to just before the marked transaction, then use the
Restore-DbaDatabase -SqlInstance server1\instance -Path c:\backups\db1 -StopMark OvernightProcessing -StopBefore
This will stop at the end of the last transaction before the the OvernightProcessing mark in the transaction log. Leaving you with a database just as it was before the overnight run started
You can specify the same Mark more than once, so if you do some processing every 2 hours you can issue a mark every 2 hours.
By default Restore-DbaDatabase will stop at the first occurence of the Mark it comes across in the logs. This can cause 2 issues:
- If you’re using differential backups you won’t see all of the log backups as some will be skipped in favour of using the diffs for speed
- If you use the same mark more than once between full backups, it will stop at the first one every time
Both of these can be fixed though.
For the former, if you use the
-IgnoreDiff switch then Restore-DbaDatabase will only use Full and Log backups so it will see all Marks in the Transaction Log. This will be slower as differential backups roll up a log of log restores
For the latter there is the
StopAfterDate parameter, when this is set Restore-DbaDatabase will stop at the first mark after the datetime specified. So you need to have a rough idea of when the Mark was made but it will let you skip the earlier occurences. You can use it like this:
Restore-DbaDatabase -SqlInstance server1\instance -Path c:\backups\db1 -StopMark LargeJob -StopAfterDate (Get-Date).Hours(-2)
Which would stop after the first transaction marked LargeJob that appears under 2 hours ago.
Now, a little confession. At the time of posting, this functionality isn’t live in the production dbatools module. I was sure I’d written it years ago, but when I went to rest the example code I got a nasty suprise. It’s currently in as a Pull request, so if you’re reading this after the 13th May 2020 then it will be in the module. If it isn’t, please just bare with us for a couple of days until the next release comes out. Sorry about that, but it does show how quickly we can turn around new features.
Being able to restore a database to a specific point in time is a great way to set up a test system for fault finding, or to go back to a known good state after a problem
Point In Time restore let’s you get to a specific time with no prior setup needed.
With Marked transactions you can restore to a specific action, or just before an action occurs, without knowing when it happened. This option does need a bit of prior setup as you’ll need to have your transactions marked, which will involve working with your developers and introducing them to any processes you’re concerned about.
All posts in this series can be found at 31 Days of dbatools Backup and Restores