No matter how hard the dbatools; team tries, there’s always someone who wants to do things we’d never thought. This is one of the great things with getting feedback direct from a great community. Unfortunately a lot of these ideas are either too niche to implement, or would be a lot of complex code for a single use case.
As part of the
Restore-DbaDatabase stack rewrite, I wanted to do make things easier for users to be able to get their hands dirty within the Restore stack. Not necessarily needing to dive into the core code and the world of GitHub Pull Requests, but by manipulating the data flowing through the pipeline using standard PowerShell techniques, all the while being able to do the heavy lifting without code.
So, below the fold we’ll be looking at some examples of how you can start going to town with your restores.
Improving header scan performance
One of the most common requests has been ways of speeding up reading the headers from the backup files. Unfortunately we have to do this so we can be sure of what they contain, and there aren’t any shortcuts (I’ve looked into it in some depth before).
So we can now offer a couple more options to do this:
Spread the load over time.
Get-DbaBackupInformation it’s now possible to scan the headers ahead of time, and just add to them in small batches going forward. So you could scan every 3 hours, and if you needed more recent file, you’d only have to scan the files written since the last scan:
Run parallel jobs on a single server
Another option is to run multiple scans at the same time. For this example I’m using the PoshRsJob module as it’s one I use a bit, but any other runspace/jobs options would work just as well.
In the first example we scan multiple directories on the same SQL Server Instance. We pass an array of folders into
Start-RsJob and scan each folder in parallel as a seperate job.
Run parallel jobs on multiple servers
Perhaps you want to spread the load even more? The run the scans across multiple SQL Instances. This example uses a simple allocation routine that just ‘cross joins’ the options:
Custom Log and Data Folders for each database
Perhaps you want to restore a number of databases, and you want seperate Data and Log folders for each database, eg:
db1 – c:\folder\data\db1 and c:\folder\log\db1
db2 – c:\folder\data\db2 and c:\folder\log\db2
By dipping into the restore pipleine we can loop through the databases contained within the backup information and apply custom formatting to each database:
Creating multiple environments
Perhaps you need to refresh multiple environments at the same time. Using Get-DbaBackupInformation to create a BackupHistory you can use the same scanned files multiple times, saving scanning them repeatedly:
Creating different points in time for comparison
Perhaps you’re trying to find out when an issue occured. This example restores the same database to multiple points in time so you can compare the state between them.
Rolling forward looking for data
Building on the one above, perhaps you’d rather something else did the checking for you? With this script we gradually roll forward a database a minute at a time. We use a SQL query (quite a simple one here) to indicate when the process should stop.
Complex Rebasing of backups
When people use
Get-DbaDbBackupHistory to get the pre-created Backup History directly from SQL Server, it’s a very common request for a way of changing the path of where the backup files were to where they are now. One thing that catches a lot of people out is that the Backup History object stores the Backup file paths as an array. This allows us to easily cope with striped backupsets where the backup consists of multiple files. The simplest way is a nested ForEach loop:
With this method you can manipulate the strings to your heart’s content using any standard PowerShell technique.
6 thoughts on “Getting complex with Restore-DbaDatabase”
Thank you for the insight into Restore-DbaDatabase! I’d like to see some of your examples, but they don’t seem to be showing on your blog post.
Sorry to hear that. The examples are hosted on github so should be publicly available. They are showing on all my devices, so I’m not sure what to suggest
If you wouldn’t mind could you try these 2 links please:
https://stuart-moore.com/complex-sql-server-restore-scenarios-with-the-dbatools-restore-dbadatabase-pipeline/ (my original post)
https://gist.github.com/Stuart-Moore/c9a31104759d66b7427311a6b6ae7a3b (one of the gists that’s used in the post)
Then it might help pinpoint the issue
No worries, Stuart. Thank you for the quick follow-up. For a paragraph near the end of your post, the last sentence ends with a colon. I thought there was supposed to be code for the ForEach loop there.
“…The simplest way is a nested ForEach loop:”
Pingback: Additional Restore-DbaDatabase Functionality – Curated SQL
Hi, have anybody has a solution for RESTORE WITH CDC?
We have a solution in dbatools now, Restore-DbaDatabase has a `KeepCDC` switch that will preserve your CDC information while restoring.
Hope that helps. Let me know if you have any problems with it.