How to get as Close to a point in time as possible (Case # 03809871)

[wmthompson1]

Hi All,
My question: how to get as Close to a point in time as possible (Case # 03809871) ? I have working code (below) but was not sure. We had an accidental and slightly random update at 1:09 p.m. Just need to get close, not exact, not after. We have incremental SQL server backups running periodically throughout the day (back-up is in an off peak window early a.m.). I'm actually not a dba so apologies in advance if there are any missing facts.

Code: Select all

# How to get as Close to a point in time as possible (Case # 03809871) :
clear
Add-PSSnapin -Name VeeamPSSnapIn -ErrorAction SilentlyContinue

Import-Module Veeam.SQL.PowerShell

Disconnect-VBRServer
Connect-VBRServer -Server "192.168.123.123"  

$ErrorActionPreference = "Stop"

$source_vm          = "sourceDBSvr"
$source_db_name     = "MyDBName"

$target_vm          = "testDBSvr"

$target_database = $source_db_name

Write-Host $target_database 
$TargetFolder01 =  "F:\MSSQL\"

$User = "domain\admin123"
$PWord = ConvertTo-SecureString -String "******" -AsPlainText -Force
$target_credentials = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $User, $PWord

# my question is; how do I get point in time 1:09 p.m., but as close as I can get to 1:09 p.m.?
$restorepoint = Get-VBRApplicationRestorePoint -Name $source_vm  -SQL | Where {$_.CreationTime -lt (get-date -Year 2019 -Month 10 -Day 10 -Hour 13 -Minute 09 -Second 59 ) } | Sort-Object –Property CreationTime –Descending | Select -First 1

Start-VESQLRestoreSession -RestorePoint $restorepoint

$session = Get-VESQLRestoreSession
$database = Get-VESQLDatabase -Session $session[0] -Name $source_db_name 
$files = Get-VESQLDatabaseFile -Database $database

# $files

$path = @("F:\MSSQL\DATA\MyDBName.mdf","F:\MSSQL\LOG\MyDBName_log.ldf")

# How to get as close to a point in time as possible ?
Restore-VESQLDatabase -Database $database -databasename $target_database -ServerName $target_vm -Force  -File $files  -TargetPath $path -SqlCredentials $target_credentials -GuestCredential $target_credentials 

Stop-VESQLRestoreSession -Session $session

# display and disconnect ------------>
write-host "session: $session"
write-host "database: $database"
write-host "restorepoint : $restorepoint"
Disconnect-VBRServer
# version 2.1.1

[veremin]

If you want to get as close a possible, why not to use Veeam Explorer for Microsoft SQL? It provides convenient UI that allows you to restore to particular transaction even - should be easier than finding a proper moment via script. Thanks!

[oleg.feoktistov]

Hi William,

When it comes to your script, to parse restore points closer to the time you need, you can also try to append a wildcard after minutes in the string input.
It looks more convenient to me than passing Get-Date with exact parameters to CreationTime property and then using less than:

Code: Select all

$restorepoint = Get-VBRApplicationRestorePoint -Name $source_vm  -SQL | Where {$_.CreationTime -like "10/10/2019 13:09:*"}

or else:

Code: Select all

$restorepoint = Get-VBRApplicationRestorePoint -Name $source_vm  -SQL | Where {$_.CreationTime -like "10/10/2019 13:0*"}

Make sure you use either -like or -match operators in your Where query.

Thank you,