Restore SQL DB to another server using transaction logs

[Sherro]

Hi Community!

We're currently using a PowerShell script that restores a specific SQL DB to another server. This has been working fine when we've only needed the nightly backup but we'd like to do another copy at midday.

The Veeam component of the script is as follows:

Code: Select all

$restorePoint = Get-VBRApplicationRestorePoint -SQL -Name $sqlSourceName | sort creationtime -Descending | select -First 1
$session = Start-VESQLRestoreSession -RestorePoint $restorepoint
$db = Get-VESQLDatabase -Session $session -db $dbName
$restoreinterval = Get-VESQLDatabaseRestoreInterval -Database $db
Restore-VESQLDatabase -Database $db -DatabaseName $dbName -ServerName $targetDBserver -SqlCredentials $credential -ToPointInTimeUtc $restoreinterval.ToUtc -force

Restore works, however it only restores data from the previous night's backup job. Is there a way to restore using the latest point in time like you can in the GUI using transaction logs?

Cheers!

[david.domask]

Hi @Sherro

https://helpcenter.veeam.com/docs/backu ... ml?ver=110

Try the -PointInTime parameter here. I haven't done this in awhile, but I believe the Get-VESQLDatabaseRestoreInterval will return available Point in Time restores points (read: transaction logs) and then when you include the -PointInTime flag, it should try to do just that. What are the intervals returned by your variable for that btw? Do you see the desired interval?

[Sherro]

Thanks for the reply,

I'll give the -PointInTime parameter a go when I'm in the office but I quickly jumped on to check what interval is returned.

Get-VESQLDatabaseRestoreInterval returns only one period:

Code: Select all

FromUtc                ToUtc                 
-------                -----                 
13/06/2022 12:18:58 PM 13/06/2022 12:18:58 PM

Which is the start of the week? When I run Get-VBRApplicationRestorePoint I can clearly see in my local time one that ran last night at 10:22 PM

[david.domask]

Hrm, to confirm, are Transaction Log backups enabled on the backup? I might be wrong on this functionality and need to check it, but can you confirm that please?

[Sherro]

Sure are! every 15 minutes.

When I do a manual restore to another server via the GUI, I'm able to adjust the slider all the way to the last 15 minute block.

[david.domask]

Okay, let me double-check, maybe I've misremembered on this I'll update a bit later.

[Sherro]

Hi David, any luck finding anything?