SQL database restore issue due to inconsistent latest restore session information

[JenHutchinson]

Support case number 03946235

I'm restoring SQL databases using the powershell scripts that many other's have documented. It works great during the day, but I'm getting inconsistent results when I schedule several restores overnight. Below is the gist of my script without all the error checking and target data/log file setup steps.

question #1 - during the day, when I look at the 5 most recent restore points using Get-VBRApplicationRestorePoint, I get multiple restore points with the same CreationTime but different ids. Which of these restore points do I use and how do I change my cmdlet to pick up that specific restore point? I run my job after midnight and want the full database backups from yesterday's full/incremental backup job. Here's an example without all the IsExchange IsActiveDirectory etc. fields

Get-VBRApplicationRestorePoint -SQL -Name $SourceVM | Sort -Property CreationTime -Descending | select -First 4

IsSQL : True
CreationTime : 1/7/2020 10:13:42 PM
Type : Increment
Name : xxx my source vm
Id : 751efa1a-9541-49a2-a1f9-69da712220e1

IsSQL : True
CreationTime : 1/7/2020 10:13:42 PM
Type : Increment
Name : xxx my source vm
Id : a7eb0380-bb81c-4177-8952-aa4d19345304

IsSQL : True
CreationTime : 1/7/2020 10:13:42 PM
Type : Increment
Name : xxx my source vm
Id : 6e7de41e-5e02-4c2a-b528-1380f4af78cb

IsSQL : True
CreationTime : 1/6/2020 10:13:42 PM
Type : Increment
Name : xxx my source vm
Id : 1b8e4181-83d0-4661-9647-7dcb2a30e1e8


question #2 - I'm getting inconsistent restore points when the restores are running concurrently the backup copy job. How would this impact the Get-VBRApplicationRestorePoint cmdlet and how do I work around this? Maybe its the SQL application transaction log backup job and not the backup copy job, but something definitely changes the most recent available restore point. When I retest the next day, I get the expected restore point from the previous night.

Thanks
Jen


Generic restore script for a default SQL instance that works during the daytime. I'm a local admin, database admin and have Veeam admin privileges, so I don't have to add credentials to the restore step

$ErrorActionPreference = "stop"
Add-PSSnapin VeeamPSSnapIn
Import-Module Veeam.SQL.Powershell
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null

#parameter values for testing script changes
$SourceVM = "xxx"
$SourceDB = "xxx"
$TargetInstance = "xxx"
$VeeamServer = "xxx"
$BackupJob = "xxx"

Connect-VBRServer -Server $VeeamServer
$restore_point = Get-VBRApplicationRestorePoint -SQL -Name $SourceVM | Sort -Property CreationTime -Descending | Select -First 1
Start-VESQLRestoreSession -RestorePoint $restore_point[0]
$session = Get-VESQLRestoreSession

$database = Get-VESQLDatabase -Session $session[0] -Name $SourceDB

$SourceDBfiles = Get-VESQLDatabaseFile -Database $database
#$TargetDBfiles = xxx removed all the steps to initialize the target database data and log file names

$RestoreInterval = Get-VESQLDatabaseRestoreInterval -Database $database
$RestoreInterval.FromUtc
$RestoreInterval.ToUTC

Restore-VESQLDatabase -Database $database -Servername $TargetInstance -File $SourceDBfiles -TargetPath $TargetDBfiles -Force -RecoveryState Recovery

Stop-VESQLRestoreSession -Session $session
Disconnect-VBRServer

[oleg.feoktistov]

Hi Jen,

I cannot say for sure what cause such issues. Please keep working with our support engineer on your return as the points you shared here require advanced troubleshooting.

Thanks,
Oleg

[JenHutchinson]

Super, thank you Oleg. How do I mark this post as closed or do I delete it?

[oleg.feoktistov]

Hi Jen,

You just did
Can you please share the outcome on how the issue was solved? In case somebody will google the same problem and encounter this thread.

Thank you,
Oleg