Restore to a point in time

by | Mar 20, 2020

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

}
Garland MacNeill

Garland MacNeill

Position

I'm a SQL DBA by day, currently working in the Duluth, MN area. My technical interests include SQL Server and SQL development. As I continue to expand my career, my focus is more on development on the SQL platform.
%d bloggers like this: