PowerShell script exchange
Post Reply
Rmachado
Service Provider
Posts: 17
Liked: 4 times
Joined: Dec 15, 2016 11:39 pm
Contact:

Help with SQL Restore powershell Script (Case # 03716957)

Post by 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
Veeam Software
Posts: 279
Liked: 112 times
Joined: Apr 20, 2017 4:19 pm
Full Name: Joe Houghes
Location: Austin, TX
Contact:

Re: Help with SQL Restore powershell Script (Case # 03716957)

Post by 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.
Husband, Father, Solutions Architect, Geek Extraordinaire | @AustinVMUG & @ATXPowerShell leader | VMware vExpert | Cisco Champion

Rmachado
Service Provider
Posts: 17
Liked: 4 times
Joined: Dec 15, 2016 11:39 pm
Contact:

Re: Help with SQL Restore powershell Script (Case # 03716957)

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

Re: Help with SQL Restore powershell Script (Case # 03716957)

Post by 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
Service Provider
Posts: 17
Liked: 4 times
Joined: Dec 15, 2016 11:39 pm
Contact:

Re: Help with SQL Restore powershell Script (Case # 03716957)

Post by 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
Veeam Software
Posts: 279
Liked: 112 times
Joined: Apr 20, 2017 4:19 pm
Full Name: Joe Houghes
Location: Austin, TX
Contact:

Re: Help with SQL Restore powershell Script (Case # 03716957)

Post by 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
Husband, Father, Solutions Architect, Geek Extraordinaire | @AustinVMUG & @ATXPowerShell leader | VMware vExpert | Cisco Champion

Rmachado
Service Provider
Posts: 17
Liked: 4 times
Joined: Dec 15, 2016 11:39 pm
Contact:

Re: Help with SQL Restore powershell Script (Case # 03716957)

Post by Rmachado » 1 person likes this post

Hello.


I made the change and it worked! thank you!

Rmachado
Service Provider
Posts: 17
Liked: 4 times
Joined: Dec 15, 2016 11:39 pm
Contact:

Re: Help with SQL Restore powershell Script (Case # 03716957)

Post by 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
Veeam Software
Posts: 1664
Liked: 586 times
Joined: Mar 02, 2012 1:40 pm
Full Name: Timothy Dewin
Contact:

Re: Help with SQL Restore powershell Script (Case # 03716957)

Post by tdewin »

I think you need just default powershell credentials

Code: Select all

$mycred = Get-Credential

Rmachado
Service Provider
Posts: 17
Liked: 4 times
Joined: Dec 15, 2016 11:39 pm
Contact:

Re: Help with SQL Restore powershell Script (Case # 03716957)

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

Re: Help with SQL Restore powershell Script (Case # 03716957)

Post by 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
Lurker
Posts: 2
Liked: never
Joined: Nov 01, 2019 11:41 am
Full Name: Sandesh Chaudhari
Contact:

Re: Help with SQL Restore powershell Script (Case # 03716957)

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

Re: Help with SQL Restore powershell Script (Case # 03716957)

Post by 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
Lurker
Posts: 2
Liked: never
Joined: Nov 01, 2019 11:41 am
Full Name: Sandesh Chaudhari
Contact:

Re: Help with SQL Restore powershell Script (Case # 03716957)

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

Re: Help with SQL Restore powershell Script (Case # 03716957)

Post by 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
Novice
Posts: 4
Liked: 1 time
Joined: Nov 20, 2018 12:25 am
Full Name: Jeff Doty
Contact:

Converting String to Veeam.SQL.PowerShell.VESQLDatabaseFile

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

Re: Help with SQL Restore powershell Script (Case # 03716957)

Post by veremin »

You should use Get-VESQLDatabaseFile cmdlet. Thanks!

Post Reply

Who is online

Users browsing this forum: Google [Bot] and 5 guests