This week I had to restore to a point in time for an entire instance. The instance had approx 25 databases on it, and all of them had to be replaced to get the information we needed. As this was on a SQL Availability Group, we are backing up the logs every 15 minutes. We also use Ola’s backup scripts, so if you’re not familiar with those, the directory structure is <Database Name> -> subfolder the DIFF, FULL and LOG in the database folder. So the choices were to go through the SSMS GUI or spend half a day working out the logic to script out the restores.
However, I came up with a different idea. I have been working the DBATools PowerShell module and was able to use that to script out a full restore with minimal lines of code, and way faster than half of a day working out the logic. What I came up with, well for me at the time, was pretty ingenious and damn sure easier than restoring via the GUI.
## Insert list of databases with format
$dbList= 'db1',
'db2',
'DB3',
'db4'
foreach ($dbName in $dbList)
{
## for the path, insert UNC path. for time, use 24 hr clock
$path = '<insert UNC path here>\' + $dbName
$rtime = '20:30 03/17/2020'
## dbatools module
restore-dbadatabase -Sqlinstance <server name here> -Path $path -DatabaseName $dbName -MaintenanceSolutionBackup -RestoreTime $rtime
}