SQL Restores via PowerShell

[ChrisGundry]

Hi all,

I have read several topics on these pages about how to use the new restore method, but I am at a loss! I have tried working it out myself and using examples etc, I think I am doing everything right, but yet I get an error...

I only get this far... I don't care about which restore point I use at this point, i just want to be able to do a restore...

Code: Select all

$RestorePoint = Get-VBRApplicationRestorePoint -SQL -Name "SQLServerName" | Select -First 1

Start-VESQLRestoreSession -RestorePoint $RestorePoint

At this point I then get an error!

Code: Select all

Start-VESQLRestoreSession : ExplorerManagementService: Failed to convert OIB ID to real OIB ID

Can anyone point me in the right direction?

ps. The Get-VBRApplicationRestorePoint command takes ages for us! We do have quite a lot of restore points, I am assuming this is the reason

Thanks!

[HannesK]

Hello,
I guess that the [0] is missing

I just took the examples from helpcenter
https://helpcenter.veeam.com/archive/ba ... point.html
https://helpcenter.veeam.com/docs/backu ... ml?ver=100

and it worked fine for me

Code: Select all

PS C:\Users\Administrator> $crmdb = Get-VBRApplicationRestorePoint -SQL -Name "HK-SQL-01" | Sort -Descending | Select -First 1
PS C:\Users\Administrator> Write-Host $crmdb
HK-SQL-01
PS C:\Users\Administrator> Start-VESQLRestoreSession -RestorePoint $crmdb[0]

Id                                   StartTime
--                                   ---------
270d7453-d807-4855-aeea-5fcf6e3a48fc 19.06.2020 09:00:12

[ChrisGundry]

Hi

I should have said, I tried that but got the same results. That [0] shouldn't be required as we have already used 'select -first 1', so there is only one object in the $RestorePoint array...

PS C:\Windows\system32> $RestorePoint

IsExchange : False
IsActiveDirectory : False
IsSharePoint : False
IsSQL : True
IsOracle : False
CreationTime : 14/06/2020 18:51:04
Type : Rollback
IsIndexed : False
IsCorrupted : False
Name : REMOVED
Id : bfe59a72-57cc-4804-b6a0-REMOVED

PS C:\Windows\system32> $RestorePoint = Get-VBRApplicationRestorePoint -SQL -Name "REMOVED" | Sort -Descending | Select -First 1

PS C:\Windows\system32> Start-VESQLRestoreSession -RestorePoint $RestorePoint[0]
Start-VESQLRestoreSession : ExplorerManagementService: Failed to convert OIB ID to real OIB ID
At line:1 char:1
+ Start-VESQLRestoreSession -RestorePoint $RestorePoint[0]
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [Start-VESQLRestoreSession], Exception
+ FullyQualifiedErrorId : System.Exception,Veeam.SQL.PowerShell.StartVESQLDatabaseRestoreSessionCmdlet

[oleg.feoktistov]

Hi Chris,

The Get-VBRApplicationRestorePoint command takes ages for us! We do have quite a lot of restore points, I am assuming this is the reason

We confirmed this behaviour and marked as a performance bug as per this thread.

Back to the issue - I saw that the RP you tested it on is of Rollback type. By any chance, do you experience the same problem when starting SQL restore for RPs of Full type? Meanwhile, I'll try to grab a lab with rollbacks to reproduce your case.

Thanks,
Oleg

[oleg.feoktistov]

Tested SQL restore with both full and rollback types. Started fine for me.
I'd advise to collect logs and reach our support for a deep dive.
Cheers!

[ChrisGundry]

Thanks for the update and your testing.

I will raise with support, but not sure what logs I could gather because the error is a PS error, not a Veeam error. The last time I spoke to support about a PS/Veeam issue, they said that PS was not supported and to post on the forum, which is why I posted this...

[oleg.feoktistov]

I'd start with the logs for SQL Explorer on VBR console:

Code: Select all

C:\ProgramData\Veeam\Backup\SQLExplorer\Logs\Veeam.SQL.Powershell_<datetime>.log

And the logs for particular backup on your source repository:

Code: Select all

C:\ProgramData\Veeam\Backup\<BackupName>\*

The last time I spoke to support about a PS/Veeam issue, they said that PS was not supported and to post on the forum, which is why I posted this...

Sounds really weird to me. Our troubleshooting capabilities through forums are limited. We can either reproduce an issue on our side under precise circumstances to confirm a bug, advise on architecture design/features or help find/develop PS scripts.


By the way, what VBR version you're on?
Do you happen to try to start restore from cloud connect repository?

Please also share case id here once submitted.

Thanks!

[ChrisGundry]

I think we found the issue! Without revealing too much information about our setup, it was an authentication issue. Checking the SQL Explorer log location gave me the information I needed. I didn't realise that this location would be used as it didn't seem we were far enough into any activities for anything to be logged by Veeam. Very happy that it was though! Just a shame the actual error was not presented in the PS console though.

I can now proceed with testing further, so that is great. Thanks

[ChrisGundry]

Is it possible to specify different file names for the restored DB, I can't seem to work out if it is possible? Thanks

[veremin]

Possible, use -File parameter for this. Thanks!

[ChrisGundry]

Seems it is actually -File AND -TargetPath that we need to use if we want to specify a new location or new file names.

$files = Get-VESQLDatabaseFile -Database $database

Code: Select all

$files
Path                                               Type
----                                               ----
C:\Program Files\File1.mdf                         Primary
C:\Program Files\File2.ldf                         Secondary

$path = @("C:\SQLDBfile1\new_file.mdf","C:\SQLDBfile2\new_file.ldf")
Restore-VESQLDatabase -Database $database -ServerName "SQLServer" -File $files -TargetPath $path -SqlCredentials $sqlcreds -GuestCredential $oscreds -UseSqlAuthentication

I have tested this with a basic DB restore and it works fine, which is good.

Although I am not sure how Veeam knows that $files/FILE1.mdf/file2.ldf relate to new_file.mdf and new_file.ldf when it does the restore?

[oleg.feoktistov]

Chris,

Once you invoke Restore-VESQLDabatase, on the backend these values are mapped based on their index in an array: the first member of an array passed to -File parameter is being mapped to the first member of an array passed to -TargetPath and so on.

Thanks!
Oleg

[ChrisGundry]

OK great thank you. It is good to know that it is done based on the order of the array because one of the DBs I will be working with has many files, and we will need to ensure they are restored to the correct locations.

Thanks