Public/New-AGMLibMSSQLMount.ps1

Function New-AGMLibMSSQLMount ([string]$appid,[string]$targethostid,[string]$mountapplianceid,[string]$imagename,[string]$imageid,[string]$targethostname,[string]$appname,[string]$sqlinstance,[string]$dbname,[string]$recoverypoint,[string]$recoverymodel,[string]$overwrite,[string]$label,[string]$consistencygroupname,[string]$dbnamelist,[string]$dbnameprefix,[string]$dbnamesuffix,[string]$recoverdb,[string]$userlogins,[string]$username,[string]$password,[string]$base64password,[string]$mountmode,[string]$mapdiskstoallesxhosts,[string]$mountpointperimage,[string]$sltid,[string]$slpid,[switch][alias("g")]$guided,[switch][alias("m")]$monitor,[switch][alias("w")]$wait) 
{
    <#
    .SYNOPSIS
    Mounts an MS SQL Image

    .EXAMPLE
    New-AGMLibMSSQLMount
    You will be prompted for Appname and target Hostname

    .EXAMPLE
    New-AGMLibMSSQLMount -appid 5552336 -targethostname demo-sql-4 -w
    Mounts the latest snapshot from AppID 5552336 to host named demo-sql-4, and waits for jobname to be printed.This will not be an appaware mount.

    .EXAMPLE
    New-AGMLibMSSQLMount -appid 5552336 -targethostname demo-sql-4 -m
    Mounts the latest snapshot from AppID 5552336 to host named demo-sql-4, and monitors the job to completion. This will not be an appaware mount.

    .EXAMPLE
    New-AGMLibMSSQLMount -appid 5552336 -targethostname demo-sql-4 -label "TDM Mount" -sqlinstance DEMO-SQL-4 -dbname avtest -w
    Mounts the latest snapshot from AppID 5552336 to host named demo-sql-4, creating a new DB called avtest on SQL Instance DEMO-SQL-4

    .EXAMPLE
    New-AGMLibMSSQLMount -appid 5534398 -targethostname demo-sql-5 -label "AV instance mount" -sqlinstance DEMO-SQL-5 -consistencygroupname avcg -dbnamelist "smalldb1,smalldb2" -dbnameprefix "nonprod_" -dbnamesuffix "_av"
    Mounts the latest snapshot from AppID 5534398 to host named demo-sql-5, creating two new DBs called nonprod_smalldb1_av and nonprod_smalldb2_av on SQL Instance DEMO-SQL-5

    .EXAMPLE
    New-AGMLibMSSQLMount -imagename Image_25256330 -label "testdb" -targethostid 5534262 -sqlinstance "HQ-SQL\MSSQL2014" -consistencygroupname "avtest" -dbnamelist "smalldb1,smalldb2,smalldb3,model" -dbnameprefix "" -dbnamesuffix "" -recoverdb true -userlogins false -recoverymodel "Same as source" -overwrite "no" -recoverypoint "2020-07-18 13:01:32"

    A SQL Mount generated by the -g option which shows every option as selected by the user.

    .DESCRIPTION
    A function to mount MS SQL Image

    * Image selection can be done three ways:

    1) Run this command in guided mode to learn the available images and select one
    2) Learn the imagename and specify that as part of the command with -imagename
    3) Learn the Appid and Cluster ID for the appliance that will mount the image and then use -appid and -mountapplianceid
    This will use the latest snapshot, dedupasync, StreamSnap or OnVault image on that appliance

    Note default values don't need to specified. So for instance these are both unnecessary: -recoverdb true -userlogins false

    * label
    -label Label for mount, recommended

    * mount host options:
    -sqlinstance The SQL instance on the host we are mounting into
    -targethostname The target host specified by name. Ideally use the next option
    -targethostid The target host specified by id

    * mounted app names
    -dbname If mounting only one DB use this option
    -dbnamelist If mounting more than one DB, use this comma separated
    -consistencygroupname If mounting more than one DB then you will need to specify a CG name. This is used on the Appliance side to group the new apps, the mounted host wont see this name
    -dbnamesuffix option to add a suffix
    -dbnameprefix option to add a prefix
    
    * Other options

    -mountpointperimage
    -recoverypoint The point in time to roll forward to, in ISO8601 format like 2020-09-02 19:00:02
    -recoverymodel use either: "Same as source" (default) or "Simple" or "Full" or "Bulk logged"
    -overwrite use either: "no" (default) "stale" or "yes"
    -recoverdb true=Recover database after restore (default) false=Don't recovery database after restore
    -userlogins false=Don't recover user logins(default) true=Recover User Logins

    * Reprotection:

    -sltid xxxx (short for Service Level Template ID) - if specified along with an slpid, will reprotect the mounted child app with the specified template and profile
    -slpid yyyy (short for Service Level Profile ID) - if specified along with an sltid, will reprotect the mounted child app with the specified template and profile

    * Username and password:
    
    -username This is the username (optional)
    -password This is the password in plain text (not a good idea)
    -base64password This is the password in base 64 encoding
    To create this:
    $password = 'passw0rd'
    $Bytes = [System.Text.Encoding]::Unicode.GetBytes($password)
    $base64password =[Convert]::ToBase64String($Bytes)

    * VMware specific options
    -mountmode use either nfs, vrdm or prdm
    -mapdiskstoallesxhosts Either true to do this or false to not do this. Default is false.

    #>


    # its pointless procededing without a connection.
    if ( (!($AGMSESSIONID)) -or (!($AGMIP)) )
    {
        Get-AGMErrorMessage -messagetoprint "Not logged in or session expired. Please login using Connect-AGM"
        return
    }
    else 
    {
        $sessiontest = (Get-AGMSession).session_id
        if ($sessiontest -ne $AGMSESSIONID)
        {
            Get-AGMErrorMessage -messagetoprint "Not logged in or session expired. Please login using Connect-AGM"
            return
        }
    }

    if (($appname) -and (!($appid)) )
    {
        $appgrab = Get-AGMApplication -filtervalue appname=$appname
        if ($appgrab.id.count -ne 1)
        { 
            Get-AGMErrorMessage -messagetoprint "Failed to resolve $appname to a single App ID. Use Get-AGMLibApplicationID and try again specifying -appid."
            return
        }
        else {
            $appid = $appgrab.id
            $apptype = $appgrab.apptype
        }
    }

    if ( ($appid) -and (!($appname)) )
    {
        $appgrab = Get-AGMApplication -filtervalue id=$appid
        if(!($appgrab))
        {
            Get-AGMErrorMessage -messagetoprint "Cannot find appid $appid"
            return
        }
        else 
        {
            $appname = ($appgrab).appname
            $apptype = $appgrab.apptype
        }
    }

    # learn about the image if supplied a name
    if ( ($imagename) -and (!($imageid)) )
    {
        $imagecheck = Get-AGMImage -filtervalue backupname=$imagename
        if (!($imagecheck))
        {
            Get-AGMErrorMessage -messagetoprint "Failed to find $imagename using: Get-AGMImage -filtervalue backupname=$imagename"
            return
        }
        else 
        {
            $imagegrab = Get-AGMImage -id $imagecheck.id
            $imageid = $imagegrab.id
            $consistencydate = $imagegrab.consistencydate
            $endpit = $imagegrab.endpit
            $appname = $imagegrab.appname
            $appid = $imagegrab.application.id
            $apptype = $imagegrab.apptype      
            $restorableobjects = $imagegrab.restorableobjects
            $mountapplianceid = $imagegrab.cluster.clusterid
        }
    }

    # learn about the image if supplied an ID
    if ( ($imageid) -and (!($imagename)) )
    {
        $imagegrab = Get-AGMImage -filtervalue id=$imageid
        if (!($imagegrab))
        {
            Get-AGMErrorMessage -messagetoprint "Failed to find Image ID $imageid using: Get-AGMImage -filtervalue id=$imageid"
            return
        }
        else 
        {
            $consistencydate = $imagegrab.consistencydate
            $endpit = $imagegrab.endpit
            $appname = $imagegrab.appname
            $appid = $imagegrab.application.id
            $apptype = $imagegrab.apptype      
            $restorableobjects = $imagegrab.restorableobjects
            $mountapplianceid = $imagegrab.cluster.clusterid
        }
    }



    # if the user gave us nothing to start work, then enter guided mode
    if (( (!($appname)) -and (!($imagename)) -and (!($appid)) ) -or ($guided))
    {
        $guided = $true
        Clear-Host
        Write-Host "What App Type do you want to work with:"
        Write-Host "1`: SQL Server (default)"
        Write-Host "2`: Sql Instance"
        Write-Host "3`: SQL Server Availability Group"
        Write-Host "4`: Consistency Group"
        Write-Host ""
        [int]$userselection = Read-Host "Please select from this list"
        if ($userselection -eq "") { $userselection = 1 }
        if ($userselection -eq 1) {  $apptype = "SqlServerWriter"  }
        if ($userselection -eq 2) {  $apptype = "SqlInstance"  }
        if ($userselection -eq 3) {  $apptype = "SQLServerAvailabilityGroup"  }
        if ($userselection -eq 4) {  $apptype = "ConsistGrp"  }
        Clear-Host
        $applist = Get-AGMApplication -filtervalue "apptype=$apptype&managed=True" | sort-object appname
        if ($applist.id.count -eq 0)
        { 
            Get-AGMErrorMessage -messagetoprint "Failed to find any $apptype apps"
            return
        }
        $i = 1
        foreach ($app in $applist)
        { 
            $applistname = $app.appname
            $appliance = $app.cluster.name 
            if ($userselection -eq 1)
            {
                $pathname = $app.pathname
                Write-Host -Object "$i`: $pathname`\$applistname ($appliance)"
            }
            else {
                Write-Host -Object "$i`: $applistname ($appliance)"
            }
            
            $i++
        }
        While ($true) 
        {
            Write-host ""
            $listmax = $applist.appname.count
            [int]$appselection = Read-Host "Please select a protected App (1-$listmax)"
            if ($appselection -lt 1 -or $appselection -gt $listmax)
            {
                Write-Host -Object "Invalid selection. Please enter a number in range [1-$($listmax)]"
            } 
            else
            {
                break
            }
        }
        $appname =  $applist.appname[($appselection - 1)]
        $appid = $applist.id[($appselection - 1)]

    }

    if ( ($targethostname) -and (!($targethostid)) )
    {
        $hostcheck = Get-AGMHost -filtervalue hostname=$targethostname
        if ($hostcheck.id.count -ne 1)
        { 
            Get-AGMErrorMessage -messagetoprint "Failed to resolve $targethostname to a single host ID. Use Get-AGMLibHostID and try again specifying -targethostid"
            return
        }
        else {
            $hostgrab = Get-AGMHost -id $hostcheck.id
            $targethostid = $hostgrab.id
            $vmtype = $hostgrab.vmtype
            $transport = $hostgrab.transport
            $diskpref = $hostgrab.diskpref
            $vcenterid = $hostgrab.vcenterhost.id
            #if the VM doesn't have a transport, then the vCenter must have one
            if ( ($vmtype -eq "vmware") -and (!($transport)) )
            {
                $vcgrab = Get-AGMHost -filtervalue id=$vcenterid 
                $transport = $vcgrab.transport
            }
        }
    }

    if ( ($targethostid) -and (!($targethostname)) )
    {
        $hostgrab = Get-AGMHost -filtervalue id=$targethostid
        if (!($hostgrab))
        {
            Get-AGMErrorMessage -messagetoprint "Failed to resolve $targethostid to a single host ID. Use Get-AGMLibHostID and try again specifying -targethostid"
            return
        }
        $targethostid = $targethostid
        $targethostname=$hostgrab.hostname
        $vmtype = $hostgrab.vmtype
        $transport = $hostgrab.transport
        $diskpref = $hostgrab.diskpref
        $vcenterid = $hostgrab.vcenterhost.id
        if ( ($vmtype -eq "vmware") -and (!($transport)) )
        {
            $vcgrab = Get-AGMHost -filtervalue id=$vcenterid 
            $transport = $vcgrab.transport
        }
    }
    
    # this if for guided menu
    if ($guided)
    {
       if (!($label))
       {
           Clear-Host
           [string]$label = Read-host "Label"
       }
        
        if (!($imagename))
        {  
            $imagelist1 = Get-AGMImage -filtervalue "appid=$appid&jobclass=snapshot&jobclass=StreamSnap&jobclass=OnVault&jobclass=dedupasync"  | select-object -Property backupname,consistencydate,endpit,id,jobclass,cluster | Sort-Object consistencydate,jobclass
            if ($imagelist1.id.count -eq 0)
            {
                Get-AGMErrorMessage -messagetoprint "Failed to fetch any Images for appid $appid"
                return
            }
            $imagelist = $imagelist1  | Sort-Object consistencydate
            if ($imagelist1.id.count -eq 1)
            {
                $imagegrab = Get-AGMImage -id $($imagelist).id
                $imagename = $imagegrab.backupname                
                $consistencydate = $imagegrab.consistencydate
                $endpit = $imagegrab.endpit
                $appname = $imagegrab.appname
                $appid = $imagegrab.application.id
                $apptype = $imagegrab.apptype      
                $restorableobjects = $imagegrab.restorableobjects
                $jobclass = $imagegrab.jobclass
                $mountapplianceid = $imagegrab.cluster.clusterid
                $mountappliancename = $imagegrab.cluster.name
                write-host "Found one $jobclass image $imagename, consistency date $consistencydate on $mountappliancename"
            } 
            else
            {
                Clear-Host
                Write-Host "Image list. Choose the best jobclass and consistency date on the best appliance"
                write-host ""
                $i = 1
                foreach ($image in $imagelist)
                { 
                    $image | Add-Member -NotePropertyName select -NotePropertyValue $i
                    $image | Add-Member -NotePropertyName appliancename -NotePropertyValue $image.cluster.name
                    $i++
                }
                #print the list
                $imagelist | select-object select,consistencydate,jobclass,appliancename,backupname,id | Format-table *
                # ask the user to choose
                While ($true) 
                {
                    Write-host ""
                    $listmax = $imagelist.Length
                    [int]$imageselection = Read-Host "Please select an image (1-$listmax)"
                    if ($imageselection -lt 1 -or $imageselection -gt $imagelist.Length)
                    {
                        Write-Host -Object "Invalid selection. Please enter a number in range [1-$($imagelist.Length)]"
                    } 
                    else
                    {
                        break
                    }
                }
                $imageid =  $imagelist[($imageselection - 1)].id
                $imagegrab = Get-AGMImage -id $imageid
                $imagename = $imagegrab.backupname                
                $consistencydate = $imagegrab.consistencydate
                $endpit = $imagegrab.endpit
                $appname = $imagegrab.appname
                $appid = $imagegrab.application.id
                $apptype = $imagegrab.apptype      
                $restorableobjects = $imagegrab.restorableobjects
                $mountapplianceid = $imagegrab.cluster.clusterid
                $mountappliancename = $imagegrab.cluster.name
            }
            
        }
        
        # now we check the log date
        if ($endpit)
        {
            write-host ""
            $recoverypoint = Read-Host "Roll forward time (hitting enter means no roll-forward)`: $consistencydate to $endpit"
            if ($recoverypoint)
            {
                if ([datetime]$recoverypoint -lt $consistencydate)
                {
                    Get-AGMErrorMessage -messagetoprint "Specified recovery point $recoverypoint is earlier than image consistency date $consistencydate. Specify an earlier image."
                    return
                }
                elseif ([datetime]$recoverypoint -gt $endpit)
                {
                    Get-AGMErrorMessage -messagetoprint "Specified recovery point $recoverypoint is later than available logs that go to $endpit"
                    return
                }
            }
        }
    
        if ( (!($targethostname)) -and (!($targethostid)))
        {
            $hostgrab1 = Get-AGMApplication -filtervalue "apptype=SqlInstance&sourcecluster=$mountapplianceid"
            $hostgrab = ($hostgrab1).host | sort-object -unique id | select-object id,name | sort-object name 
            if ($hostgrab -eq "" )
            {
                Get-AGMErrorMessage -messagetoprint "Cannot find any hosts with SQLInstances"
                return
            }
            Clear-Host
            Write-Host "Target host selection menu"
            $i = 1
            foreach ($name in $hostgrab.name)
            { 
                Write-Host -Object "$i`: $name"
                $i++
            }
            While ($true) 
            {
                $listmax = $hostgrab.name.count
                [int]$hostselection = Read-Host "Please select a host (1-$listmax)"
                if ($hostselection -lt 1 -or $hostselection -gt $listmax)
                {
                    Write-Host -Object "Invalid selection. Please enter a number in range [1-$($listmax)]"
                } 
                else
                {
                    break
                }
            }
            $targethostname =  $hostgrab.name[($hostselection - 1)]
            $targethostid = $hostgrab.id[($hostselection - 1)]
        }

        # now we determine the instance to mount to
        $instancelist = Get-AGMApplication -filtervalue "hostid=$targethostid&apptype=SqlInstance"
        if ( (!($instancelist)) -or ($instancelist.count -eq 0) )
        {
            Get-AGMErrorMessage -messagetoprint "Failed to fetch any SQL Instances on $targethostname. Specify a target host with discovered SQL Instances"
            return
        }
        if ($instancelist.Length -eq 1)
        {
            $sqlinstance = ($instancelist).appname
        } 
        else
        {
            write-host ""
            Write-Host "SQL instance list"
            $i = 1
            foreach
            ($instance in $instancelist.appname)
                { Write-Host -Object "$i`: $instance"
                $i++
            }
            While ($true) 
            {
                Write-host ""
                $listmax = $instancelist.Length
                [int]$instanceselection = Read-Host "Please select an instance (1-$listmax)"
                if ($instanceselection -lt 1 -or $instanceselection -gt $instancelist.Length)
                {
                    Write-Host -Object "Invalid selection. Please enter a number in range [1-$($instancelist.Length)]"
                } 
                else
                {
                    break
                }
            }
            $sqlinstance =  $instancelist[($instanceselection - 1)].appname
        }
        # reprotection
        Clear-Host
        Write-Host "Reprotection"
        Write-Host "1`: Don't manage new application (default)"
        Write-Host "2`: Manage new application"
        Write-Host ""
        [int]$userselection = Read-Host "Please select from this list (1-2)"
        if ($userselection -eq "") { $userselection = 1 }
        if ($userselection -eq 2) 
        {   
            # slt selection
            Clear-Host
            Write-Host "SLT list"
            $objectgrab = Get-AGMSLT | sort-object name
            $i = 1
            foreach
            ($object in $objectgrab)
                { Write-Host -Object "$i`: $($object.name) ($($object.id))"
                $i++
            }
            While ($true) 
            {
                Write-host ""
                $listmax = $objectgrab.Length
                [int]$objectselection = Read-Host "Please select from this list (1-$listmax)"
                if ($objectselection -lt 1 -or $objectselection -gt $listmax)
                {
                    Write-Host -Object "Invalid selection. Please enter a number in range [1-$listmax]"
                } 
                else
                {
                    break
                }
            }
            $sltid =  $objectgrab[($objectselection - 1)].id

            #slp selection
            Clear-Host
            Write-Host "SLP list"
            $objectgrab = Get-AGMSLP -filtervalue clusterid=$mountapplianceid | sort-object name
            $i = 1
            foreach
            ($object in $objectgrab)
                { Write-Host -Object "$i`: $($object.name) ($($object.id))"
                $i++
            }
            While ($true) 
            {
                Write-host ""
                $listmax = $objectgrab.Length
                [int]$objectselection = Read-Host "Please select from this list (1-$listmax)"
                if ($objectselection -lt 1 -or $objectselection -gt $listmax)
                {
                    Write-Host -Object "Invalid selection. Please enter a number in range [1-$listmax]"
                } 
                else
                {
                    break
                }
            }
            $slpid =  $objectgrab[($objectselection - 1)].id
        }


        # now we look for restoreable objects
        if ($apptype -ne "SqlServerWriter")
        {
            if (!($restorableobjects))
            {
                Write-Host -Object "The image did not have any restoreable objects"
                return
            }
            if ($restorableobjects.length -eq 1)
            {
                $dbnamelist = $restorableobjects.name
            }
            else 
            {
                $dbnamelist1 = ""
                $dbnamelist = ""
                Clear-Host
                Write-Host "DB list (either enter 0 or a comma separated list e.g. 1,2)"
                Write-Host "0`: All DBs (default)"
                $i = 1
                foreach
                ($db in $restorableobjects.name)
                    { Write-Host -Object "$i`: $db"
                    $i++
                }
                $listmax = $restorableobjects.name.Length
                [string]$userselection = Read-Host "Please select from this list (0 or comma separated list)"
                if (($userselection -eq "0") -or ($userselection -eq ""))
                {
                    foreach ($db in $restorableobjects.name)
                    {
                        $dbnamelist1 = $dbnamelist1 + "," + $db 
                    }
                    $dbnamelist = $dbnamelist1.substring(1)
                }
                else
                {
                    foreach ($db in $userselection.Split(","))
                    {
                        $dbnamelist1 = $dbnamelist1 + "," + $restorableobjects.name[($db - 1)]
                    }
                    $dbnamelist = $dbnamelist1.substring(1)
                }
            }
        }
         # if the dbnamelist has only one DB in it, we need to get a DB name, otherwise we need to enter CG processing.
        if (($apptype -eq "SqlServerWriter") -or ($dbnamelist.Split(",").count -eq 1))
        {
            if (!($dbname))
            {
                Clear-Host
                While ($true) 
                {
                    $dbname = Read-Host "SQL Server Database Name"
                    if ($dbname -eq "")
                    {
                        Write-Host -Object "The DB Name cannot be blank"
                    } 
                    else
                    {
                        break
                    }
                }
            }
        }
        else 
        {
            # consistency group is mandatory
            Clear-Host
            While ($true) 
            {
                $consistencygroupname = Read-Host "Name of Consistency Group"
                if ($consistencygroupname -eq "")
                {
                    Write-Host -Object "The CG Name cannot be blank"
                } 
                else
                {
                    break
                }
            }
            $dbnameprefix = Read-Host "Prefix for SQL Server Database Name"
            $dbnamesuffix = Read-Host "Suffix for SQL Server Database Name"
        }
        # recover DB
        Clear-Host
        Write-Host "Recover database"
        Write-Host "1`: Recover database after restore(default)"
        Write-Host "2`: Don't recovery database after restore"
        Write-Host ""
        [int]$userselection = Read-Host "Please select from this list (1-2)"
        if ($userselection -eq "") { $userselection = 1 }
        if ($userselection -eq 1) {  $recoverdb = "true"  }
        if ($userselection -eq 2) {  $recoverdb = "false"  }
        # recover User Logins
        Clear-Host
        Write-Host "User Login recovery"
        Write-Host "1`: Don't recover user logins(default)"
        Write-Host "2`: Recover User Logins"
        Write-Host ""
        [int]$userselection = Read-Host "Please select from this list (1-2)"
        if ($userselection -eq "") { $userselection = 1 }
        if ($userselection -eq 1) {  $userlogins = "false"  }
        if ($userselection -eq 2) {  $userlogins = "true"  }

        Write-host ""
        $username = read-host "Username (optional)"
        if ($username)
        {
            $passwordenc = Read-Host -AsSecureString "Password"
            if ($passwordenc.length -ne 0)
            {
                $UnsecurePassword = ConvertFrom-SecureString -SecureString $passwordenc -AsPlainText
                $base64password = [System.Convert]::ToBase64String([System.Text.Encoding]::UTF8.GetBytes($UnsecurePassword))
            }
        }

        # recovery model
        Clear-Host
        Write-Host "Recovery model"
        Write-Host "1`: Same as source (default)"
        Write-Host "2`: Simple Logging mode"
        Write-Host "3`: Full Logging mode"
        Write-Host "4`: Bulk Logged mode"
        Write-Host ""
        [int]$userselection = Read-Host "Please select from this list (1-4)"
        if ($userselection -eq "") { $recoverymodel = "Same as source" }
        if ($userselection -eq 1) {  $recoverymodel = "Same as source" }
        if ($userselection -eq 2) {  $recoverymodel = "Simple"  }
        if ($userselection -eq 3) {  $recoverymodel = "Full"  }
        if ($userselection -eq 4) {  $recoverymodel = "Bulk logged"  }
        #overwrite existing database
        Clear-Host
        Write-Host "Overwrite existing database"
        Write-Host "1`: No (default)"
        Write-Host "2`: Only if its stale"
        Write-Host "3`: Yes"
        Write-Host ""
        [int]$userselection = Read-Host "Please select from this list (1-3)"
        if ($userselection -eq "") { $overwrite = "no" }
        if ($userselection -eq 1) {  $overwrite = "no"  }
        if ($userselection -eq 2) {  $overwrite = "stale"  }
        if ($userselection -eq 3) {  $overwrite = "yes"  }

        # if this is a VMTarget
        if ($vmtype -eq "vmware")
        {
            if (($diskpref -eq "BLOCK") -and ($transport -ne "GUESTVMISCSI"))
            {
                Clear-Host
                Write-Host "Mount mode" 
                if ($transport -eq "NFS")
                {
                    $defaultmode = 3
                    Write-Host "1`: vrdm"
                    Write-Host "2`: prdm"
                    Write-Host "3`: nfs(default)"
                }
                else 
                {
                    $defaultmode = 1
                    Write-Host "1`: vrdm(default)"
                    Write-Host "2`: prdm"
                    Write-Host "3`: nfs"
                }
                Write-Host ""
                [int]$userselection = Read-Host "Please select from this list (1-3)"
                if ($userselection -eq "") { $userselection = $defaultmode }
                if ($userselection -eq 1) {  $mountmode = "vrdm"  }
                if ($userselection -eq 2) {  $mountmode = "prdm"  }
                if ($userselection -eq 3) {  $mountmode = "nfs"  }
        
                # map to all ESX host
                Clear-Host
                Write-Host "Map to all ESX Hosts"
                Write-Host "1`: Do not map to all ESX Hosts(default)"
                Write-Host "2`: Map to all ESX Hosts"
                Write-Host ""
                [int]$userselection = Read-Host "Please select from this list (1-2)"
                if ($userselection -eq "") { $userselection = 1 }
                if ($userselection -eq 1) {  $mapdiskstoallesxhosts = "false"  }
                if ($userselection -eq 2) {  $mapdiskstoallesxhosts = "true"  }
            }
        }
        #volume info section
        $logicalnamelist = $restorableobjects.volumeinfo | select-object logicalname,capacity,uniqueid | sort-object logicalname | Get-Unique -asstring
        Clear-Host  
        

        if ($logicalnamelist.count -eq 1)
        {
            Write-Host "This image has only one drive. You can change mount point used or allow the Connector to determine this"
            Write-Host ""
            $mountpointperimage = ""
            $mountpointperimage = Read-Host "Mount Location (optional)"
            
        }
        if ($logicalnamelist.count -gt 1)
        {
            Write-Host "This image has more than one drive. You can enter a Mount Location, or press enter to set mount points per drive. "
            Write-Host ""
            $mountpointperimage = ""
            $mountpointperimage = Read-Host "Mount Location (optional)"
            if ($mountpointperimage -eq "")
            {
                Clear-Host
                $mountpointspervol = ""
                $mountpointspervol1 = ""
                Write-Host "Set mount Locations per drive, or press enter to allow the Connector to determine this."
                foreach ($logicalname in $logicalnamelist)
                { 
                    $capacity = [math]::Round($logicalname.capacity / 1073741824,1)
                    $diskname = $logicalname.logicalname
                    $uniqueid = $logicalname.uniqueid
                    $mountpointgrab = ""
                    [string]$mountpointgrab = Read-Host "$diskname $capacity GiB"
                    if ($mountpointgrab -ne "")
                    {
                        $mountpointspervol1 = $mountpointspervol1 + "," + "$uniqueid" + "=" + "$mountpointgrab"
                    }
                    if ($mountpointspervol1 -ne "")
                    {
                        $mountpointspervol = $mountpointspervol1.substring(1)
                    }
                }
            }
        }

        # we are done
       Clear-Host  
        Write-Host "Guided selection is complete. The values entered would result in the following command:"
        Write-Host ""
        if ( ($dbname) -and (!($dbnamelist)) )
        {   
            if ($recoverypoint)
            {
                Write-Host -nonewline "New-AGMLibMSSQLMount -appid $appid -mountapplianceid $mountapplianceid -imagename $imagename -label `"$label`" -targethostid $targethostid -sqlinstance `"$sqlinstance`" -dbname `"$dbname`" -recoverdb $recoverdb -userlogins $userlogins -recoverymodel `"$recoverymodel`" -overwrite `"$overwrite`" -recoverypoint `"$recoverypoint`""
                if ($username)
                {
                    Write-Host -nonewline " -username $username -base64password `"$base64password`""
                }
                if ($mountmode)
                {
                    Write-Host -nonewline " -mountmode $mountmode -mapdiskstoallesxhosts $mapdiskstoallesxhosts"
                }
                if ($mountpointperimage)
                {
                    Write-Host -nonewline " -mountpointperimage `"$mountpointperimage`""
                }
                if ($mountpointspervol)
                {
                    Write-Host -nonewline " -mountpointspervol `"$mountpointspervol`""
                }
                if ($sltid)
                {
                    Write-Host -nonewline " -sltid $sltid -slpid $slpid"
                }
            }
            else 
            {
                Write-Host -nonewline "New-AGMLibMSSQLMount -appid $appid -mountapplianceid $mountapplianceid -imagename $imagename -label `"$label`" -targethostid $targethostid -sqlinstance `"$sqlinstance`" -dbname `"$dbname`" -recoverdb $recoverdb -userlogins $userlogins -recoverymodel `"$recoverymodel`" -overwrite `"$overwrite`""
                if ($username)
                {
                    Write-Host -nonewline " -username $username -base64password `"$base64password`""
                }
                if ($mountmode)
                {
                    Write-Host -nonewline " -mountmode $mountmode -mapdiskstoallesxhosts $mapdiskstoallesxhosts"
                }
                if ($mountpointperimage)
                {
                    Write-Host -nonewline " -mountpointperimage `"$mountpointperimage`""
                }
                if ($mountpointspervol)
                {
                    Write-Host -nonewline " -mountpointspervol `"$mountpointspervol`""
                }
                if ($sltid)
                {
                    Write-Host -nonewline " -sltid $sltid -slpid $slpid"
                }
            }
        }
        elseif ( ($dbname) -and ($dbnamelist) )
        {   
            if ($recoverypoint)
            {
                Write-Host -nonewline "New-AGMLibMSSQLMount -appid $appid -mountapplianceid $mountapplianceid -imagename $imagename -label `"$label`" -targethostid $targethostid -sqlinstance `"$sqlinstance`" -dbnamelist `"$dbnamelist`" -dbname `"$dbname`" -recoverdb $recoverdb -userlogins $userlogins -recoverymodel `"$recoverymodel`" -overwrite `"$overwrite`" -recoverypoint `"$recoverypoint`""
                if ($username)
                {
                    Write-Host -nonewline " -username $username -base64password `"$base64password`""
                }if ($mountmode)
                {
                    Write-Host -nonewline " -mountmode $mountmode -mapdiskstoallesxhosts $mapdiskstoallesxhosts"
                }
                if ($mountpointperimage)
                {
                    Write-Host -nonewline " -mountpointperimage `"$mountpointperimage`""
                }
                if ($mountpointspervol)
                {
                    Write-Host -nonewline " -mountpointspervol `"$mountpointspervol`""
                }
                if ($sltid)
                {
                    Write-Host -nonewline " -sltid $sltid -slpid $slpid"
                }
            }
            else 
            {
                Write-Host -nonewline "New-AGMLibMSSQLMount -appid $appid -mountapplianceid $mountapplianceid -imagename $imagename -label `"$label`" -targethostid $targethostid -sqlinstance `"$sqlinstance`" -dbnamelist `"$dbnamelist`" -dbname `"$dbname`" -recoverdb $recoverdb -userlogins $userlogins -recoverymodel `"$recoverymodel`" -overwrite `"$overwrite`""
                if ($username)
                {
                    Write-Host -nonewline " -username $username -base64password `"$base64password`""
                }
                if ($mountmode)
                {
                    Write-Host -nonewline " -mountmode $mountmode -mapdiskstoallesxhosts $mapdiskstoallesxhosts"
                }
                if ($mountpointperimage)
                {
                    Write-Host -nonewline " -mountpointperimage `"$mountpointperimage`""
                }
                if ($mountpointspervol)
                {
                    Write-Host -nonewline " -mountpointspervol `"$mountpointspervol`""
                }
                if ($sltid)
                {
                    Write-Host -nonewline " -sltid $sltid -slpid $slpid"
                }
            }
        }
        else 
        {
            if ($recoverypoint)
            {
                Write-Host -nonewline "New-AGMLibMSSQLMount -appid $appid -mountapplianceid $mountapplianceid -imagename $imagename -label `"$label`" -targethostid $targethostid -sqlinstance `"$sqlinstance`" -consistencygroupname `"$consistencygroupname`" -dbnamelist `"$dbnamelist`" -recoverdb $recoverdb -userlogins $userlogins -recoverymodel `"$recoverymodel`" -overwrite `"$overwrite`" -recoverypoint `"$recoverypoint`""
                if ($username)
                {
                    Write-Host -nonewline " -username $username -base64password `"$base64password`""
                }
                if ($dbnameprefix)
                {
                    Write-Host -nonewline " -dbnameprefix `"$dbnameprefix`""
                }
                if ($dbnamesuffix)
                {
                    Write-Host -nonewline " -dbnamesuffix `"$dbnamesuffix`""
                }
                if ($mountmode)
                {
                    Write-Host -nonewline " -mountmode $mountmode -mapdiskstoallesxhosts $mapdiskstoallesxhosts"
                }
                if ($mountpointperimage)
                {
                    Write-Host -nonewline " -mountpointperimage `"$mountpointperimage`""
                }
                if ($mountpointspervol)
                {
                    Write-Host -nonewline " -mountpointspervol `"$mountpointspervol`""
                }
                if ($sltid)
                {
                    Write-Host -nonewline " -sltid $sltid -slpid $slpid"
                }
            }
            else 
            {
                Write-Host -nonewline "New-AGMLibMSSQLMount -appid $appid -mountapplianceid $mountapplianceid -imagename $imagename -label `"$label`" -targethostid $targethostid -sqlinstance `"$sqlinstance`" -consistencygroupname `"$consistencygroupname`" -dbnamelist `"$dbnamelist`" -recoverdb $recoverdb -userlogins $userlogins -recoverymodel `"$recoverymodel`" -overwrite `"$overwrite`""
                if ($username)
                {
                    Write-Host -nonewline " -username $username -base64password `"$base64password`""
                }
                if ($dbnameprefix)
                {
                    Write-Host -nonewline "-dbnameprefix `"$dbnameprefix`""
                }
                if ($dbnamesuffix)
                {
                    Write-Host -nonewline "-dbnamesuffix `"$dbnamesuffix`""
                }
                if ($mountmode)
                {
                    Write-Host -nonewline " -mountmode $mountmode -mapdiskstoallesxhosts $mapdiskstoallesxhosts"
                }
                if ($mountpointperimage)
                {
                    Write-Host -nonewline " -mountpointperimage `"$mountpointperimage`""
                }
                if ($mountpointspervol)
                {
                    Write-Host -nonewline " -mountpointspervol `"$mountpointspervol`""
                }
                if ($sltid)
                {
                    Write-Host -nonewline " -sltid $sltid -slpid $slpid"
                }
            }
        }
        Write-Host ""
        Write-Host "1`: Run the command now (default)"
        Write-Host "2`: Show the JSON used to run this command, but don't run it"
        Write-Host "3`: Exit without running the command"
        $userchoice = Read-Host "Please select from this list (1-3)"
        if ($userchoice -eq 2)
        {
            $jsonprint = "yes"
        }
        if ($userchoice -eq 3)
        {
            return
        }

    }

        
    if ($targethostid -eq "")
    {
        Get-AGMErrorMessage -messagetoprint "Cannot proceed without a targethostid or targethostname"
        return
    }

    if (($appid) -and ($mountapplianceid) -and (!($imageid)))
    {
        # if we are not running guided mode but we have an appid without imageid, then lets get the latest image on the mountappliance ID
        $imagegrab = Get-AGMImage -filtervalue "appid=$appid&targetuds=$mountapplianceid&jobclass=snapshot&jobclass=StreamSnap&jobclass=dedupasync&jobclass=OnVault" -sort "consistencydate:desc,jobclasscode:asc" -limit 1
        if ($imagegrab.count -eq 1)
        {   
            $imageid = $imagegrab.id
            $imagename = $imagegrab.backupname
        }
        else 
        {
            Get-AGMErrorMessage -messagetoprint "Failed to fetch a snapshot, dedupasync, StreamSnap or OnVault Image for appid $appid on appliance with clusterID $mountapplianceid"
            return
        }
    }

    if ( ($sltid) -and (!($slpid)) )
    {
        Get-AGMErrorMessage -messagetoprint "An sltid $sltid was specified without an slpid with -slpid yyyy. Please specify both"
        return
    }
    if ( (!($sltid)) -and ($slpid) )
    {
        Get-AGMErrorMessage -messagetoprint "An slpid $slpid was specified without an sltid using -sltid xxxx. Please specify both"
        return
    }
    
    # learn about the image
    if (!($imagename)) 
    {
        Get-AGMErrorMessage -messagetoprint "No image was found to mount"
        return
    }

    # recovery point handling
    if ($recoverypoint)
    {
        $recoverytime = Convert-ToUnixDate $recoverypoint
    }

    # recovery or not
    if (!($recoverdb))
    { 
        $recoverdb = "true" 
    }


    if (!($userlogins))
    {
        $userlogins = "false"
    }

    if (!($recoverymodel))
    {
        $recoverymodel = "Same as source"
    }

    if (!($overwrite))
    {
        $overwrite = "no"
    }

    
    if (!($label))
    {
        $label = ""
    }

    if ($password)
    {
        $Bytes = [System.Text.Encoding]::Unicode.GetBytes($password)
        $base64password =[Convert]::ToBase64String($Bytes)
    }


    # turn DB name into a list of selected objects
    if ($dbnamelist)
    {
        $selectedobjects = @(
            foreach ($db in $dbnamelist.Split(","))
            {
            @{
                restorableobject = $db
            }
        }
        )
    }
    else 
    {
        $selectedobjects = @(
            @{
                restorableobject = $appname
            }
        )
    }


    if ($mountmode -eq "vrdm")
    {
        $physicalrdm = 0
        $rdmmode = "independentvirtual"
    }
    if ($mountmode -eq "prdm")
    {
        $physicalrdm = 1
        $rdmmode = "physical"
    }
    if ($mountmode -eq "nfs")
    {
        $physicalrdm = 2
        $rdmmode = "nfs"
    }




    if ($mapdiskstoallesxhosts)
    {
        if (($mapdiskstoallesxhosts -ne "true") -and  ($mapdiskstoallesxhosts -ne "false"))
        {
            Get-AGMErrorMessage -messagetoprint "The value of Map to all ESX hosts of $mapdiskstoallesxhosts is not valid. Must be true or false"
            return
        }
        $restoreoptions = @(
            @{
                name = 'mapdiskstoallesxhosts'
                value = "$mapdiskstoallesxhosts"
            }
        )
    }

    if ($mountpointperimage)
    {
        if ($restoreoptions)
        {
            $imagemountpoint = @{
                name = 'mountpointperimage'
                value = "$mountpointperimage"
            }
            $restoreoptions = $restoreoptions + $imagemountpoint
        }
        else 
        {
            $restoreoptions = @(
            @{
                name = 'mountpointperimage'
                value = "$mountpointperimage"
            }
        )
        }
    }

    if ($mountpointspervol)
    {
        $restoreobjectmappings = @(
            foreach ($mapping in $mountpointspervol.Split(","))
            {
                $firstword = $mapping.Split("=") | Select-object -First 1
                $secondword = $mapping.Split("=") | Select-object -skip 1
                [pscustomobject]@{
                    restoreobject = $firstword
                    mountpoint = $secondword}
            } 
        )
    }


    
    if ($dbname) 
    {
        $provisioningoptions = @(
            @{
                name = 'sqlinstance'
                value = $sqlinstance
            },
            @{
                name = 'dbname'
                value = $dbname
            },
            @{
                name = 'recover'
                value = $recoverdb
            },
            @{
                name = 'userlogins'
                value = $userlogins
            },
            @{
                name = 'recoverymodel'
                value = $recoverymodel
            },
            @{
                name = 'overwritedatabase'
                value = $overwrite
            }
        )
        # reprotect
        if ($sltid)
        {
            $provisioningoptions= $provisioningoptions +@{
                name = 'reprotect'
                value = "true"
            }
            $provisioningoptions= $provisioningoptions +@{
                name = 'slt'
                value = $sltid
            }
            $provisioningoptions= $provisioningoptions +@{
                name = 'slp'
                value = $slpid
            }
        }
        #authentication
        if ($username)
        {
            $provisioningoptions= $provisioningoptions +@{
                name = 'username'
                value = $username
            }
            $provisioningoptions= $provisioningoptions +@{
                name = 'password'
                value = $base64password
            }
        }



        $body = [ordered]@{
            label = $label;
            image = $imagename;
            host = @{id=$targethostid}
            provisioningoptions = $provisioningoptions
            appaware = "true";
            migratevm = "false";
        }
        if ($restoreoptions)
        {
            $body = $body + @{ restoreoptions = $restoreoptions }
        }
        if ($selectedobjects)
        {
            $body = $body + @{ selectedobjects = $selectedobjects }
        }
        if ($recoverytime)
        {
            $body = $body + @{ recoverytime = [string]$recoverytime }
        }
        if ($mountmode)
        {
            $body = $body + @{ physicalrdm = $physicalrdm }
            $body = $body + @{ rdmmode = $rdmmode }
        }
        if ($restoreobjectmappings)
        {
            $body = $body + @{ restoreobjectmappings = $restoreobjectmappings }
        }
    }
    elseif ( ($consistencygroupname) )
    {
        if (!($dbnamelist))
        {
            Get-AGMErrorMessage -messagetoprint "ConsistencyGroupName was specified but dbnamelist was not. Please specify dbnamelist to identify which DBs to mount"
            return
        }
        $provisioningoptions = @(
                @{
                    name = 'ConsistencyGroupName'
                    value = $consistencygroupname
                }
        )
        if ($dbnameprefix -ne "")
        {
            $provisioningoptions= $provisioningoptions + @{
                name = 'dbnameprefix'
                value = $dbnameprefix
            }
        }
        if ($dbnamesuffix -ne "")
        {
            $provisioningoptions= $provisioningoptions + @{
                name = 'dbnamesuffix'
                value = $dbnamesuffix
            }
        }
        $provisioningoptions= $provisioningoptions + @{
            name = 'sqlinstance'
            value = $sqlinstance
        }
        $provisioningoptions= $provisioningoptions +@{
            name = 'recover'
            value = $recoverdb
        }
        $provisioningoptions= $provisioningoptions +@{
            name = 'userlogins'
            value = $userlogins
        }
        $provisioningoptions= $provisioningoptions +@{
            name = 'recoverymodel'
            value = $recoverymodel
        }
        $provisioningoptions= $provisioningoptions +@{
            name = 'overwritedatabase'
            value = $overwrite
        }
        if ($sltid)
        {
            $provisioningoptions= $provisioningoptions +@{
                name = 'reprotect'
                value = "true"
            }
            $provisioningoptions= $provisioningoptions +@{
                name = 'slt'
                value = $sltid
            }
            $provisioningoptions= $provisioningoptions +@{
                name = 'slp'
                value = $slpid
            }
        }
        $body = [ordered]@{
            label = $label;
            image = $imagename;
            host = @{id=$targethostid};
            selectedobjects = $selectedobjects
            provisioningoptions = $provisioningoptions
            appaware = "true";
            migratevm = "false";
        }
        if ($restoreoptions)
        {
            $body = $body + @{ restoreoptions = $restoreoptions }
        }
        if ($recoverytime)
        {
            $body = $body + @{ recoverytime = [string]$recoverytime }
        }
        if ($restoreobjectmappings)
        {
            $body = $body + @{ restoreobjectmappings = $restoreobjectmappings }
        }
    }
    else 
    {
        $body = @{
            label = $label;
            image = $imagename;
            host = @{id=$targethostid}
        }
    }

    

    $json = $body | ConvertTo-Json

    if ($monitor)
    {
        $wait = "y"
    }

    if ($jsonprint -eq "yes")
    {
        $compressedjson = $body | ConvertTo-Json -compress
        Write-host "This is the final command:"
        Write-host ""
        Write-host "Post-AGMAPIData -endpoint /backup/$imageid/mount -body `'$compressedjson`'"
        return
    }

    Post-AGMAPIData  -endpoint /backup/$imageid/mount -body $json

    if ($wait)
    {
        Start-Sleep -s 15
        $jobgrab = Get-AGMJob -filtervalue "appid=$appid&jobclasscode=5&isscheduled=false&targethost=$targethostname" -sort queuedate:desc -limit 1 
        if (!($jobgrab.jobname))
        {
            Start-Sleep -s 15
            $jobgrab = Get-AGMJob -filtervalue "appid=$appid&jobclasscode=5&isscheduled=false&targethost=$targethostname" -sort queuedate:desc -limit 1 
            if (!($jobgrab.jobname))
            {
                return
            }
        }
        else
        {   
            $jobgrab| select-object jobname,status,queuedate,startdate,targethost
            
        }
        if (($jobgrab.jobname) -and ($monitor))
        {
            Get-AGMFollowJobStatus $jobgrab.jobname
        }
    }
}