System.String errors

[sam.baltz]

I have inherited a massive Powershell script (1900+ lines) from a previous employee that has not worked since the Powershell commands for Veeam were updated and the old ones stopped working. In a nutshell, we have a web frontend that our Developers can use to refresh a database (Savant) from one of our distribution centers by exporting their input information into an .xml file. The script is set to run every night by automated task that takes the info from the .xml file and puts it into the script and log any errors. This is the snippet of the script that is giving errors (proprietary info is omitted):

Code: Select all

function Start-SavantDevCloneRefresh {
    [CmdletBinding()]
    Param(
        [parameter(Mandatory = $True)]
        [ValidateSet("DCSTL", "DCCAR", "DCFRN", "DCTEX", "DCOSW", "DCPEN", "DCMES", "DCPET")]
        [String]$SourceSiteName,
        [parameter(Mandatory = $True)]
        [ValidateSet("REDACTED", "REDACTED")]
        [String]$TargetServerFQDN,
        [parameter(Mandatory = $true)]
        [boolean]$PreviousCloneExists
    )
    Process {
        #declare function variables
        
        $str_VeeamBackupName = $SourceSiteName + ">EXAGRID SQL to HQ Backup Copy"
        $str_VeeamServer = "REDACTED"
        $str_backupID = $null
        $str_TargetServer = $TargetServerFQDN
        $str_sourceServer = $null
        $str_DBName = "Savant" 
        $obj_VBRSQLdatabase = $null
        $obj_GuestCreds = $null 
        $obj_connectiontest = $null
        $obj_restorepoint = $null
        $path = @("D:\CloneDatabases\DataFiles\Savant.mdf","D:\CloneDatabases\LogFiles\Savant.ldf")

        #set source server based on text from Site Name param
        IF ($SourceSiteName -eq "DCSTL") {$str_sourceServer = "REDACTED"}
        IF ($SourceSiteName -eq "DCTEX") {$str_sourceServer = "REDACTED"}
        IF ($SourceSiteName -eq "DCCAR") {$str_sourceServer = "REDACTED"}
        IF ($SourceSiteName -eq "DCOSW") {$str_sourceServer = "REDACTED"}
        IF ($SourceSiteName -eq "DCFRN") {$str_sourceServer = "REDACTED"}
        IF ($SourceSiteName -eq "DCPEN") {$str_sourceServer = "REDACTED"}
        IF ($SourceSiteName -eq "DCMES") {$str_sourceServer = "REDACTED"}
        IF ($SourceSiteName -eq "DCPET") {$str_sourceServer = "REDACTED"}

        #define the SQL to run which executes a stored procedure 
        $SQL_SANITIZE_SP = "exec dbo.SP_SavantSensitiveDataCleanup"
        
        #define the SQL to fix logins on Savant database

        $SQL_POSTPROC_SP = "
        USE [Savant]
        GO
        EXEC sUT.sCloneDBPostProcessMaster
        "

        #define the SQL to set Savant to RESTRICTED_USER mode prior to sanitizing 

        $SQL_SET_RESTRICTED_USER = "
        USE [master]
        GO
        ALTER DATABASE [Savant] SET RESTRICTED_USER
        GO"

        #define SQL statements for setting single user and detaching database
        $SQL_set_single_user = "ALTER DATABASE $str_DBName SET SINGLE_USER WITH ROLLBACK IMMEDIATE"
        $SQL_drop_database = "DROP DATABASE $str_DBName"

        #If old copy exists, set old copy to single user and drop rather than detach
        IF ($PreviousCloneExists -eq $true) {

            Invoke-Sqlcmd -ServerInstance $str_TargetServer -Database master -query $SQL_set_single_user -QueryTimeout 300
            Start-Sleep -Seconds 30
            Invoke-Sqlcmd -ServerInstance $str_TargetServer -Database master -query $SQL_drop_database -QueryTimeout 300
    
        }
        
        #connect to Veeam Server if not connected
        $obj_connectiontest = Get-VBRServerSession
        IF ($obj_connectiontest.Server -eq "DMHQ-VEEAM1.dminet.com") {Continue}
        ELSE {Connect-VBRServer -Server $str_VeeamServer -Port 9392 }

        #Get the ID guid of the most recent backup of the specified server
        #Where-Object {$_.CreationTime -gt (get-date).addDays(-1)} | 
        #
        $str_backupID = Get-VBRBackup -Name $str_VeeamBackupName | Get-VBRRestorePoint | Where-Object {$_.IsConsistent -eq $true} | Sort-Object CreationTime -Descending | Select-Object -First 1 | ForEach-Object {$_.Id}
        $restorepoint = Get-VBRApplicationRestorePoint -SQL -Name "$str_sourceServer" | Sort -Property CreationTime -Descending | Select-Object -First 1
        $obj_restorepoint = Start-VESQLRestoreSession -RestorePoint $restorepoint | Where-Object {$_.Id -eq $str_backupID.Guid} 

        #Get the Veeam credential object needed to restore
        $obj_GuestCreds = Get-VBRCredentials -name "REDACTED"

        #Get the databases in the specified backup and choose Savant
        $obj_VBRSQLDatabase = Get-VESQLDatabase -Session "$obj_restorepoint" -Name "Savant"

        #Restore the database to alternate location - DCTEST-SQL1
        Restore-VESQLDatabase  -Database $obj_VBRSQLdatabase -ServerName $str_TargetServer -DatabaseName $str_DBName -TargetPath $path -GuestCredentials $obj_GuestCreds -SqlCredentials $obj_GuestCreds -Wait

        #disconnect from the Veeam Backup Server
        Disconnect-VBRServer

        #slight delay after restore before invoking stored procedure
        Start-Sleep -seconds 30

        #set the database to RESTRICTED_USER mode until it is sanitized
        Invoke-Sqlcmd -ServerInstance $str_TargetServer -Query $SQL_SET_RESTRICTED_USER -QueryTimeout 300

        #Execute the stored procedure to sanitize the database once mounted 
        Invoke-Sqlcmd -ServerInstance $str_TargetServer -Database master -query $SQL_SANITIZE_SP -QueryTimeout 300

        #Execute the SQL to fix logins
        Invoke-Sqlcmd -ServerInstance $str_TargetServer -Database $str_DBName -Query $SQL_POSTPROC_SP -QueryTimeout 1000

        #wait, then change back to C drive, and remove imported module from session
        Start-Sleep -seconds 15
        C:

The error I am getting is:

Get-VESQLDatabase : Cannot bind parameter 'Session'. Cannot convert the "" value of type
"System.String" to type "Veeam.SQL.PowerShell.VESQLRestoreSession".
At C:\Windows\system32\WindowsPowerShell\v1.0\Modules\DMDevCloneOps2\DMDevCloneOps2.psm1:1374
char:58
+ ... RSQLDatabase = Get-VESQLDatabase -Session "$obj_restorepoint" -Name " ...
+ ~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidArgument: (:) [Get-VESQLDatabase], ParameterBindingException
+ FullyQualifiedErrorId : CannotConvertArgumentNoMessage,Veeam.SQL.PowerShell.GetVESQLDatabas
eCmdlet

line 1374 is this section of the above code:

Code: Select all

#Get the databases in the specified backup and choose Savant
        $obj_VBRSQLDatabase = Get-VESQLDatabase -Session "$obj_restorepoint" -Name "Savant"

I originally opened case 03657045 back in August and was told that they can help with the syntax of the code, but since it was a custom script, there wasn't much more they could do (which is understandable). This script was written by a fairly competent scripter as part of a school project over the course of 4 months, and I am over my head. Any help would be greatly appreciated!

[chris.arceneaux]

Hi Sam,

Welcome to the Veeam forums!

The code looks good comparing it against Veeam documentation.

Can you please confirm that the line below is completing successfully? My thought is the restore session isn't kicking off correctly which, in turn, causes the error you're seeing as the $obj_restorepoint variable doesn't have the object type the Get-VESQLDatabase command requires.

$obj_restorepoint = Start-VESQLRestoreSession -RestorePoint $restorepoint | Where-Object {$_.Id -eq $str_backupID.Guid}

[oleg.feoktistov]

Hi Sam!

Additionally, after the test Chris suggested, make sure you pass $obj_restorepoint variable to -Session parameter without quotes:

Code: Select all

$obj_VBRSQLDatabase = Get-VESQLDatabase -Session $obj_restorepoint -Name "Savant"

Otherwise, its value will be recognized as "Veeam.SQL.PowerShell.VESQLRestoreSession" of type "System.String" and it will throw another error.

Thanks!
Oleg

[jhoughes]

Oleg definitely caught a problem there. If that session variable is valid, by having it wrapped in double quotes, PowerShell is performing text expansion of the variable contents.

Basically, you are giving it the text contents of that session object in a string, rather than the object itself, hence the errors for the wrong type of object.

Apologies if you understand these PowerShell basics and just didn't catch the quotes, but sometimes it helps to leave the explanation of why these errors occurring for anyone who reads these threads.

[sam.baltz]

Perfect, thanks all!

Let me try without the quotes. My knowledge of powershell is not the greatest, but I'm trying to learn a lot more so the explanation definitely helps!

I will post results here shortly.

[sam.baltz]

I re-ran it and it gave me a different error:

Code: Select all

Get-VESQLDatabase : Cannot validate argument on parameter 'Session'. The argument is null. 
Provide a valid value for the argument, and then try running the command again.
At C:\Windows\system32\WindowsPowerShell\v1.0\Modules\DMDevCloneOps2\DMDevCloneOps2.psm1:1374 
char:58
+ ... VBRSQLDatabase = Get-VESQLDatabase -Session $obj_restorepoint -Name " ...
+                                                 ~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidData: (:) [Get-VESQLDatabase], ParameterBindingValidationExc 
   eption
    + FullyQualifiedErrorId : ParameterArgumentValidationError,Veeam.SQL.PowerShell.GetVESQLDatab 
   aseCmdlet

However, I see the database restore job has kicked off in Veeam B&R, so it looks like it kind of works?

[oleg.feoktistov]

Sam, if you retrieve $obj_restorepoint alone, does it have any value assigned?
If it doesn't, try starting restore session, retrieving it and only then passing to -Session parameter:

Code: Select all

Start-VESQLRestoreSession -RestorePoint $restorepoint | Where-Object {$_.Id -eq $str_backupID.Guid}
$obj_restorepoint = Get-VESQLRestoreSession
$obj_VBRSQLDatabase = Get-VESQLDatabase -Session $obj_restorepoint -Name "Savant"

Thanks!

[sam.baltz]

Looks like the restore session failed with the below error:



I tried to retrieve the value of $obj_restorepoint and it came back "null", I'll try substituting your line of code and trying again.

[sam.baltz]

I was able to get a bit further with your update, now getting the below error:

Code: Select all

Restore-VESQLDatabase : Cannot bind parameter 'GuestCredentials'. Cannot convert the 
"REDACTED" value of type "Veeam.Backup.PowerShell.Infos.CInternalCredentials" to 
type "System.Management.Automation.PSCredential".
At C:\Windows\system32\WindowsPowerShell\v1.0\Modules\DMDevCloneOps2\DMDevCloneOps2.psm1:1378 
char:153
+ ... tr_DBName -TargetPath $path -GuestCredentials $obj_GuestCreds -SqlCre ...
+                                                   ~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidArgument: (:) [Restore-VESQLDatabase], ParameterBindingExcep 
   tion
    + FullyQualifiedErrorId : CannotConvertArgumentNoMessage,Veeam.SQL.PowerShell.RestoreVESQLDat 
   abaseCmdlet

[veremin]

It should be Guest OS credentials, not the credentials stored inside the backup server. Use Get-Credential to get them. Thanks!

[oleg.feoktistov]

To my mind, it would be more convenient to use CredentialManager powershell module to silently get stored credentials every time the script is run
instead of inputting them on every prompt Get-Credential cmdlet triggers.
So, simply install the module, add a new credential record to the server's Windows Credential Manager naming it like "RestoreCreds"
and then retrieve it like that:

Code: Select all

$obj_GuestCreds = Get-StoredCredential -target "RestoreCreds"
$obj_VBRSQLDatabase = Get-VESQLDatabase -Session "$obj_restorepoint" -Name "Savant"
Restore-VESQLDatabase  -Database $obj_VBRSQLdatabase -ServerName $str_TargetServer
-DatabaseName $str_DBName -TargetPath $path -GuestCredentials $obj_GuestCreds -SqlCredentials $obj_GuestCreds -Wait

Parsed and assigned to $obj_GuestCreds value will be automatically converted to PSCredential object and recognized by both -GuestCredentials and -SqlCredentials parameters.
Thanks!