Auto Restore Database "Case # 04145400"

[Uffe]

Hi

I trial to make a script to make at auto restoret of a test sql database.
Is works in Powershell ISE. Is not work in Powershell

I get this error:

Code: Select all

Restore-VESQLDatabase : File does not exist. File: [].
Failed to open storage for read access. Storage: [].
Failed to restore file from local backup. VFS link: [summary.xml]. Target file: [MemFs://frontend::CDataTransferCommand
Set::RestoreText_{703999c0-ebc7-49b2-a580-eebcc98052cc}]. CHMOD mask: [0].
Agent failed to process method {DataTransfer.RestoreText}.
At C:\Script\Auto-restore.ps1:34 char:1
+ Restore-VESQLDatabase -Database $database -databasename "SQL_ ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [Restore-VESQLDatabase], Exception
    + FullyQualifiedErrorId : System.Exception,Veeam.SQL.PowerShell.RestoreVESQLDatabaseCmdlet

My Script looks this (i have anonymize it)

Code: Select all

#add veeam powershell addin
Add-PSSnapin -Name VeeamPSSnapIn
#Get restor point
$restorepoint = Get-VBRApplicationRestorePoint -SQL -Name "SQL1"| Sort-Object –Property CreationTime –Descending | Select-Object -First 1
#Start restore service
Start-VESQLRestoreSession -RestorePoint $restorepoint
#Set session id in variabel
$session = Get-VESQLRestoreSession
#Set Datebase variable need to restore
$database = Get-VESQLDatabase -Session $session[0] -Name "SQL_Prod" 
#Show settinges for restore
write-host "session: $session"
write-host "database: $database"
write-host "restorepoint: $restorepoint"
#set login
$username = "Username for SQL Restore"
$pwdTxt = Get-Content "File location for password"
$securePwd = $pwdTxt | ConvertTo-SecureString 
$cred = New-Object System.Management.Automation.PSCredential -ArgumentList $username, $securePwd
#Get datebase
$files = Get-VESQLDatabaseFile -Database $database
#$files
#    Path                                               Type
#    D:\SQL_DBs\erp.mdf                                 Primary
#    C:\SQL\LOGs\erp_log.ldf                         Secondary
#
#Set filer place
$path = @("E:\Data\SQL_Test_DATA.mdf","F:\LOG\SQL_Test_LOG.ldf","E:\Data\SQL_Test_1_DATA.ndf","E:\Data\SQL_Test_2_DATA.ndf","E:\SQL_DK_2018_Test_3_DATA.ndf","E:\Data\SQL_Test_4_DATA.ndf","E:\Data\SQL_Test_5_DATA.ndf","E:\Data\SQL_Test_6_DATA.ndf","E:\SQL_Test_7_DATA.ndf")
#run restore
Restore-VESQLDatabase -Database $database -databasename "SQL_Test" -ServerName "SQLserver FQDN" -File $files -TargetPath $path -SqlCredentials $cred -Force
#Stop Restore service
Stop-VESQLRestoreSession -Session $session[0]

Any idea to what going wrong?

best regard Uffe

[veremin]

Run the script under administrator account and see whether it makes a difference. Thanks!

[Uffe]

HI

Thanks for Answer.

It same error.

I also have try to test in visual studio code. I get Same error

Best Regard Uffe

[jhoughes]

Just as a best practice, you should modify your code to save the session to a variable when you launch it. This will make sure that you are referencing the correct restore session.

Code: Select all

$session = Start-VESQLRestoreSession -RestorePoint $restorepoint

You’ll then need to update your line for getting the databases also:

Code: Select all

$database = Get-VESQLDatabase -Session $session -Name "SQL_Prod"

I noticed that you have a single filename that does not seem correct for the particular DB, unless it was manually named different, can you confirm that “E:\SQL_DK_2018_Test_3_DATA.ndf” is correct for the database? Sending the output of $files would help to confirm.

Also, be aware that the integrated terminal in VSCode will launch the newest version of PowerShell existing on the system. You will need to make sure that you select Windows PowerShell as the terminal shell, if you have installed PowerShell 6 or 7 on the system.

[Uffe]

Hi

Thanks for Answer.

I have modify the script with your changes.

Is still work in Powershell ISE not in PowerShell.

The script look this:

Code: Select all

#add veeam powershell addin
Add-PSSnapin -Name VeeamPSSnapIn
#Get restor point
$restorepoint = Get-VBRApplicationRestorePoint -SQL -Name "SQL1"| Sort-Object –Property CreationTime –Descending | Select-Object -First 1
#Start restore service
Start-VESQLRestoreSession -RestorePoint $restorepoint
#Set session id in variabel
$session = Start-VESQLRestoreSession -RestorePoint $restorepoint
#Set Datebase variable need to restore
$database = Get-VESQLDatabase -Session $session -Name "SQL_Prod" 
#Show settinges for restore
write-host "session: $session"
write-host "database: $database"
write-host "restorepoint: $restorepoint"
#set login
$username = "Username for SQL Restore"
$pwdTxt = Get-Content "File location for password"
$securePwd = $pwdTxt | ConvertTo-SecureString 
$cred = New-Object System.Management.Automation.PSCredential -ArgumentList $username, $securePwd
#Get datebase
$files = Get-VESQLDatabaseFile -Database $database
#$files
#    Path                                               Type
#    D:\SQL_DBs\erp.mdf                                 Primary
#    C:\SQL\LOGs\erp_log.ldf                         Secondary
#
#Set filer place
$path = @("E:\Data\SQL_Test_DATA.mdf","F:\LOG\SQL_Test_LOG.ldf","E:\Data\SQL_Test_1_DATA.ndf","E:\Data\SQL_Test_2_DATA.ndf","E:\Data\SQL_DK_2018_Test_3_DATA.ndf","E:\Data\SQL_Test_4_DATA.ndf","E:\Data\SQL_Test_5_DATA.ndf","E:\Data\SQL_Test_6_DATA.ndf","E:\SQL_Test_7_DATA.ndf")
#run restore
Restore-VESQLDatabase -Database $database -databasename "SQL_Test" -ServerName "SQLserver FQDN" -File $files -TargetPath $path -SqlCredentials $cred -Force
#Stop Restore service
Stop-VESQLRestoreSession -Session $session

Any ideers?

Best Regard Uffe

[jhoughes]

I think the problem is either with the files or path.

Please paste the output from the $files variable (details of the database files for your database).

Otherwise, your paths may be incorrect. I still see the mismatched name for file Test_3_DATA, and the final file is trying to write to the root of your E: drive.

Also, you may want to explicitly load the Explorer module and not just the Veeam snapin.

[Uffe]

I have changed the paths in the new script.

It was correct in original script

I can not understand at is can run in powershell_ise.exe with success and not in powershell.exe

[oleg.feoktistov]

Hi Uffe,

I see that our support engineer helped you find the root cause. Please make sure to declare variables with $ at the beginning

Thanks!
Oleg