PowerShell script exchange
Post Reply
ChrisGundry
Expert
Posts: 173
Liked: 24 times
Joined: Aug 26, 2015 2:56 pm
Full Name: Chris Gundry
Contact:

SQL Restores via PowerShell

Post by 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
Veeam Software
Posts: 5704
Liked: 782 times
Joined: Sep 01, 2014 11:46 am
Location: Austria
Contact:

Re: SQL Restores via PowerShell

Post by 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
Expert
Posts: 173
Liked: 24 times
Joined: Aug 26, 2015 2:56 pm
Full Name: Chris Gundry
Contact:

Re: SQL Restores via PowerShell

Post by 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
Veeam Software
Posts: 406
Liked: 130 times
Joined: Sep 25, 2019 10:32 am
Full Name: Oleg Feoktistov
Contact:

Re: SQL Restores via PowerShell

Post by 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
Veeam Software
Posts: 406
Liked: 130 times
Joined: Sep 25, 2019 10:32 am
Full Name: Oleg Feoktistov
Contact:

Re: SQL Restores via PowerShell

Post by 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
Expert
Posts: 173
Liked: 24 times
Joined: Aug 26, 2015 2:56 pm
Full Name: Chris Gundry
Contact:

Re: SQL Restores via PowerShell

Post by 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
Veeam Software
Posts: 406
Liked: 130 times
Joined: Sep 25, 2019 10:32 am
Full Name: Oleg Feoktistov
Contact:

Re: SQL Restores via PowerShell

Post by 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
Expert
Posts: 173
Liked: 24 times
Joined: Aug 26, 2015 2:56 pm
Full Name: Chris Gundry
Contact:

Re: SQL Restores via PowerShell

Post by 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
Expert
Posts: 173
Liked: 24 times
Joined: Aug 26, 2015 2:56 pm
Full Name: Chris Gundry
Contact:

Re: SQL Restores via PowerShell

Post by 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
Product Manager
Posts: 17745
Liked: 1615 times
Joined: Oct 26, 2012 3:28 pm
Full Name: Vladimir Eremin
Contact:

Re: SQL Restores via PowerShell

Post by veremin »

Possible, use -File parameter for this. Thanks!

ChrisGundry
Expert
Posts: 173
Liked: 24 times
Joined: Aug 26, 2015 2:56 pm
Full Name: Chris Gundry
Contact:

Re: SQL Restores via PowerShell

Post by 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
Veeam Software
Posts: 406
Liked: 130 times
Joined: Sep 25, 2019 10:32 am
Full Name: Oleg Feoktistov
Contact:

Re: SQL Restores via PowerShell

Post by oleg.feoktistov » 1 person likes this post

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
Expert
Posts: 173
Liked: 24 times
Joined: Aug 26, 2015 2:56 pm
Full Name: Chris Gundry
Contact:

Re: SQL Restores via PowerShell

Post by ChrisGundry » 1 person likes this post

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

Post Reply

Who is online

Users browsing this forum: No registered users and 3 guests