Help with SQL Restore powershell Script (Case # 03716957)

[Rmachado]

Hello.

We need to automate a backup from a SQL Server and restore one database in the test server, everyday.

I collected some scripts and made one working script (i will change the name server in it) . The only problem in the script is that it uses the old Start-VBRSQLDatabaseRestore, and the problem is that it only restore to c:\, and in the test server i need to be in e:\ for example
I tried to use the new Restore-VESQLDatabase that allow changing the folder, but i cannot make it work. Support told me that they cant help with scripts. Anyone can give a light of how to change / do it?

Script (im not the author of it!) :

Code: Select all

Add-PSSnapin -Name VeeamPSSnapIn
$restorepoint = Get-VBRApplicationRestorePoint -SQL -Name "ProductionSQL"
$ProductionDatabase = Get-VBRSQLDatabase -ApplicationRestorePoint $restorepoint[-1] -Name "ProductionDatabase"
$guestcreds = Get-VBRCredentials -Name "DOMAIN\VeeamSvc"
$sqlcreds = Get-VBRCredentials -Name "DOMAIN\VeeamSvc"
Start-VBRSQLDatabaseRestore -Database $ProductionDatabase -ServerName TestSQLServer -DatabaseName ProductionDatabaseTest -GuestCredentials $guestcreds -SqlCredentials $sqlcreds -Wait

[jhoughes]

Yes, you need to use the Restore-VESQLDatabase cmdlet which is now referenced in the documentation for Start-VBRSQLDatabaseRestore. You can find it here.

You can follow "Example 2. Restoring to Specific Folder" for most of what you are looking for.

[Rmachado]

Hello, thanks for your answer

Im still trying to get it to work, and i can find the backup, but in this case, i have a backup copy job and a tape job with this backup.

When i run this powershell, it selects the tape backup job or the backup copy job. Is there a way to force it to get from a specific backup?

Than kyou.

[veremin]

What exact script are you running? I'm wondering cause neither backup copy job restore point nor tape one should be selected by the provided cmdlet. Thanks!

[Rmachado]

Hello.

Im managed to get the script better, but its getting a backup from the last one (152 days) and not from yesterday.

Code: Select all

Add-PSSnapin -Name VeeamPSSnapIn
$restorepoint = Get-VBRApplicationRestorePoint -SQL 
Start-VESQLRestoreSession -RestorePoint $restorepoint[0] | Sort-Object –Property CreationTime –Descending | Select -First 1
$session = Get-VESQLRestoreSession
$database = Get-VESQLDatabase -Session $session[0] -Name "xxxxxx" 

write-host "session: $session"
write-host "database: $database"
write-host "restorepoint : $restorepoint"
$creds = Get-VBRCredentials -Name *xxx\xxxx*


Restore-VESQLDatabase -Database $database -databasename "xxxx-restore" -ServerName "Server-XXX" -TargetFolder "d:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA"

[jhoughes]

Your restore point line is incorrect, that is where you need the sort-object to be used.

Try this:

Code: Select all

$restorepoint = Get-VBRApplicationRestorePoint -SQL | Sort-Object –Property CreationTime –Descending | Select -First 1
Start-VESQLRestoreSession -RestorePoint $restorepoint

[Rmachado]

Hello.


I made the change and it worked! thank you!

[Rmachado]

Hello, i think i need your help again.

Im trying to use the "GET-VBRCredentials" and use it in the script, so i can put it in Windows task scheduler

I created a username in Veeam Credentials "SERVERNAME\Restore" , them im using this code

Code: Select all

$mycreds = Get-VBRCredentials -name *restore*
[\code]
I can see in write-host that he can take the credentials , but when i execute the restore :
[code]
Restore-VESQLDatabase -Database $Database -ServerName $TargetVM -TargetFolder $TargetFolder -GuestCredentials $mycreds -Force 

It doesnt work and give me this error :

Code: Select all

Restore-VESQLDatabase : Cannot bind parameter 'GuestCredentials'. Cannot convert the "SERVER\restore" value of type
"Veeam.Backup.PowerShell.Infos.CInternalCredentials" to type "System.Management.Automation.PSCredential".
[\code]

What im missing?

Thank you again

[tdewin]

I think you need just default powershell credentials

Code: Select all

$mycred = Get-Credential

[Rmachado]

Helo.

It doesnt work :

Code: Select all

Restore-VESQLDatabase : Cannot convert 'System.Object[]' to the type 'System.Management.Automation.PSCredential'
required by parameter 'GuestCredentials'. Specified method is not supported.
[\code]

[veremin]

1) Use Get-Credential instead of Get-VBRCredentials
2) Use specific name as -Name parameter, currently you're getting array of credentials due to asterisks specified

[sandesh.c]

Hello.

I am trying to restore VeeamBackup Database from 1 server to other but getting error as below

Restore-VESQLDatabase : Failed to get access to SQL Server 172.30.20.32.
At C:\Users\Administrator\Documents\1st Nov\Code 1.ps1:16 char:1
+ Restore-VESQLDatabase -Database $database -databasename "VeeamBackup-restore" -S ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [Restore-VESQLDatabase], Exception
+ FullyQualifiedErrorId : Failed to get access to SQL Server 172.30.20.32.,Veeam.SQL.PowerShell.RestoreVESQLDatabaseCmdlet

Script is mentioned below, what is wrong in this ?

Thanks !

Code: Select all

Add-PSSnapin -Name VeeamPSSnapIn

$restorepoint = Get-VBRApplicationRestorePoint -SQL | Sort-Object –Property CreationTime –Descending | Select -First 1
Start-VESQLRestoreSession -RestorePoint $restorepoint

$session = Get-VESQLRestoreSession
$database = Get-VESQLDatabase -Session $session[0] -Name "VeeamBackup" 

write-host "session: $session"
write-host "database: $database"
write-host "restorepoint: $restorepoint"
$creds = Get-VBRCredentials -Name "172.30.20.32\Administrator"
#$creds1 = Get-Credential -UserName Administrator


Restore-VESQLDatabase -Database $database -databasename "VeeamBackup-restore" -ServerName "172.30.20.32" -TargetFolder "C:\Program Files\Microsoft SQL Server\MSSQL13.VEEAMSQL2016\MSSQL\DATA" -SqlCredentials $cred

[veremin]

Just to make sure - it isn't a copy/paste issue that in restore cmdlet you use $cred variable instead of declared $creds one? Thanks!

[sandesh.c]

Thank you for your reply, but after changing the typing error still i am getting below error.

Restore-VESQLDatabase : Cannot bind parameter 'SqlCredentials'. Cannot convert the "172.30.20.32\Administrator" value of type
"Veeam.Backup.PowerShell.Infos.CInternalCredentials" to type "System.Management.Automation.PSCredential".
At C:\Users\Administrator\Documents\1st Nov\Code 1.ps1:16 char:141
+ ... SqlCredentials $creds
+ ~~~~~~
+ CategoryInfo : InvalidArgument: (:) [Restore-VESQLDatabase], ParameterBindingException
+ FullyQualifiedErrorId : CannotConvertArgumentNoMessage,Veeam.SQL.PowerShell.RestoreVESQLDatabaseCmdlet

[oleg.feoktistov]

Like Vladimir and Timothy mentioned above, use Get-Credential instead of Get-VBRCredentials as -SqlCredentials parameter doesn't accept the output of Get-VBRCredentials.
Best Regards,
Oleg

[jdoty]

Hi I am trying to run the following Command:

Code: Select all

Restore-VESQLDatabase -Database $database -ServerName $TargetSvr -InstanceName $TargetInstName -File $FilesArray -TargetPath $TargetFolders -force -GuestCredentials $Mycred -verbose

Restore-VESQLDatabase : Cannot bind parameter 'File'. Cannot convert the "WJH.DB.Warehouse.mdf" value of type
"System.String" to type "Veeam.SQL.PowerShell.VESQLDatabaseFile".
At line:1 char:102
+ ... me $TargetSvr -InstanceName $TargetInstName -File $FilesArray -Target ...
+                                                       ~~~~~~~~~~~
    + CategoryInfo          : InvalidArgument: (:) [Restore-VESQLDatabase], ParameterBindingException
    + FullyQualifiedErrorId : CannotConvertArgumentNoMessage,Veeam.SQL.PowerShell.RestoreVESQLDatabaseCmdlet

I am getting an error on the -File element. I created an array with the file names ($FilesArray), but the command doesn't like it. How do I convert the array in the a Veeam.SQL.PowerShell.VESQLDatabaseFile object?

[veremin]

You should use Get-VESQLDatabaseFile cmdlet. Thanks!