Scripts/Restore/Restore-CohesityRemoteMSSQLObject.ps1

function Restore-CohesityRemoteMSSQLObject {
    <#
        .SYNOPSIS
        From remote cluster restores the specified MS SQL object from a previous backup.
        .DESCRIPTION
        From remote cluster restores the specified MS SQL object from a previous backup.
        .NOTES
        Published by Cohesity
        .LINK
        https://cohesity.github.io/cohesity-powershell-module/#/README
        .EXAMPLE
        Restore-CohesityRemoteMSSQLObject -SourceId 1279 -HostSourceId 1277 -JobId 31520 -TargetHostId 770 -CaptureTailLogs:$false -NewDatabaseName CohesityDB_r1 -NewInstanceName MSSQLSERVER -TargetDataFilesDirectory "C:\temp" -TargetLogFilesDirectory "C:\temp" -DbRestoreOverwritePolicy:$true
        Restore MSSQL database from remote cluster with database id 1279 , database instance id 1277 and job id as 31520
        $mssqlObjects = Find-CohesityObjectsForRestore -Environments KSQL
        Get the source id, $mssqlObjects[0].SnapshottedSource.Id
        Get the source instance id, $mssqlObjects[0].SnapshottedSource.SqlProtectionSource.OwnerId
        Use the DbRestoreOverwritePolicy:$true for overriding the existing database
        .EXAMPLE
        Restore-CohesityRemoteMSSQLObject -SqlHost x.x.x.x -JobId 31520 -SqlObjectName instance/databse_1 -TargetHost y.y.y.y -CaptureTailLogs:$false -NewDatabaseName CohesityDB_r1 -NewInstanceName MSSQLSERVER -TargetDataFilesDirectory "C:\temp" -TargetLogFilesDirectory "C:\temp" -DbRestoreOverwritePolicy:$true
        Restore MSSQL database from remote cluster with database name database_1 from the sql host x.x.x.x, and job id as 31520 to the target host y.y.y.y
        .EXAMPLE
        Restore-CohesityRemoteMSSQLObject -SourceId 3101 -HostSourceId 3099 -JobId 51275 -TargetHostId 3098 -CaptureTailLogs:$false -NewDatabaseName ReportServer_r26 -NewInstanceName MSSQLSERVER -TargetDataFilesDirectory "C:\temp" -TargetLogFilesDirectory "C:\temp" -StartTime 1616956306627994 -JobRunId 60832 -RestoreTimeSecs 1616958037
        Request for restore MSSQL object with RestoreTimeSecs (point in time) parameter, StartTime and JobRunId.
        .EXAMPLE
        Restore-CohesityRemoteMSSQLObject -SourceId 3101 -HostSourceId 3099 -JobId 51275 -TargetHostId 3098 -CaptureTailLogs:$false -NewDatabaseName ReportServer_r20 -NewInstanceName MSSQLSERVER -TargetDataFilesDirectory "C:\temp" -TargetLogFilesDirectory "C:\temp" -Confirm:$false -TargetSecondaryDataFilesDirectoryList $patternList
        For secondary data files, construct the $patternList as follows
        $patternList = @()
        $pattern1 = @{filePattern = "*.mdf"; targetDirectory = "c:\test"}
        $pattern2 = @{filePattern = "*.ldf"; targetDirectory = "c:\test1"}
        $patternList += $pattern1
        $patternList += $pattern2
    #>


    [CmdletBinding(DefaultParameterSetName = "Default", SupportsShouldProcess = $True, ConfirmImpact = "High")]
    Param(
        [Parameter(Mandatory = $false)]
        # Specifies the name of the restore task.
        [string]$TaskName = "Restore-MSSQL-Object-" + (Get-Date -Format "dddd-MM-dd-yyyy-HH-mm-ss").ToString(),
        [Parameter(Mandatory = $false)]
        [ValidateRange(1, [long]::MaxValue)]
        # Specifies the source id of the MS SQL database to restore. This can be obtained using Find-CohesityObjectsForRestore -Environments KSQL.
        [long]$SourceId,
        [Parameter(Mandatory = $false)]
        [ValidateRange(1, [long]::MaxValue)]
        # Specifies the id of MSSQL database instance.
        [long]$HostSourceId,
        [Parameter(Mandatory = $true)]
        [ValidateRange(1, [long]::MaxValue)]
        # Specifies the job id that backed up this MS SQL instance and will be used for this restore.
        [long]$JobId,
        [Parameter(Mandatory = $false, ParameterSetName = "Jobrun")]
        [ValidateRange(1, [long]::MaxValue)]
        # Specifies the job run id that captured the snapshot for this MS SQL instance. If not specified the latest run is used.
        # This field must be set if restoring to a different target host.
        [long]$JobRunId,
        [Parameter(Mandatory = $false, ParameterSetName = "Jobrun")]
        # Specifies the time when the Job Run starts capturing a snapshot.
        # Specified as a Unix epoch Timestamp (in microseconds).
        # This must be specified if job run id is specified.
        [long]$StartTime,
        [Parameter(Mandatory = $false)]
        # Specifies if the tail logs are to be captured before the restore operation.
        # This is only applicable if restoring the SQL database to its hosting Protection Source and the database is not being renamed.
        [switch]$CaptureTailLogs,
        [Parameter(Mandatory = $false)]
        # This field prevents "change data capture" settings from being reomved.
        # When a database or log backup is restored on another server and database is recovered.
        [switch]$KeepCDC,
        [Parameter(Mandatory = $false)]
        # Specifies a new name for the restored database.
        [string]$NewDatabaseName,
        [Parameter(Mandatory = $false)]
        # Specifies the instance name of the SQL Server that should be restored.
        [string]$NewInstanceName,
        [Parameter(Mandatory = $false)]
        # Specifies the time in the past to which the SQL database needs to be restored.
        # This allows for granular recovery of SQL databases.
        # If not specified, the SQL database will be restored from the full/incremental snapshot.
        [long]$RestoreTimeSecs = 0,
        [Parameter(Mandatory = $false)]
        # Specifies the directory where to put the database data files.
        # Missing directory will be automatically created.
        # This field must be set if restoring to a different target host.
        [string]$TargetDataFilesDirectory,
        [Parameter(Mandatory = $false)]
        # Specifies the directory where to put the database log files.
        # Missing directory will be automatically created.
        # This field must be set if restoring to a different target host.
        [string]$TargetLogFilesDirectory,
        [Parameter(Mandatory = $false)]
        # Specifies the secondary data filename pattern and corresponding directories of the DB. Secondary data
        # files are optional and are user defined. The recommended file extension for secondary files is
        # ".ndf". If this option is specified and the destination folders do not exist they will be
        # automatically created.
        # This field can be set only if restoring to a different target host.
        [Object[]]$TargetSecondaryDataFilesDirectoryList,
        [Parameter(Mandatory = $false)]
        # This field will overwrite the existing db contents if it sets to true
        # By default the db overwrite policy is false
        [switch]$DbRestoreOverwritePolicy,
        [Parameter(Mandatory = $false)]
        [ValidateRange(1, [long]::MaxValue)]
        # Specifies the target host if the application is to be restored to a different host.
        # If not specified, then the application is restored to the original host (physical or virtual) that hosted this application.
        [long]$TargetHostId,
        [Parameter(Mandatory = $false)]
        # Specifies the SQL Host information
        [string]$SqlHost,
        [Parameter(Mandatory = $false)]
        # Specifies the SQL Object Name
        [string]$SqlObjectName,
        [Parameter(Mandatory = $false)]
        # Specifies the target host to restore
        [string]$TargetHost
    )
    Begin {
    }

    Process {
        if ($PSCmdlet.ShouldProcess($SourceId)) {
            $job = Get-CohesityProtectionJob -Ids $JobId
            if (-not $job) {
                Write-Output "Cannot proceed, the job id '$JobId' is invalid"
                return
            }

            if ($job.IsActive -eq $false) {
                if ($TargetHost) {
                    # Find the Id of specified target host
                    $protectionSources = Get-CohesityProtectionSource -Environments KSQL
                    foreach ($record in $protectionSources) {
                        if ($record.protectionSource.Name -eq $TargetHost) {
                            $TargetHostId = $record.protectionSource.id
                            break;
                        }
                    }

                    if (!$TargetHostId){
                        Write-Output "Unable to find the id of target host '$TargetHost'."
                        return
                    }
                } elseif ($TargetHostId){
                    $protectionSourceObject = Get-CohesityProtectionSource -Id $TargetHostId
                    if ($protectionSourceObject.id -ne $TargetHostId) {
                        Write-Output "Cannot proceed, the target host id '$TargetHostId' is invalid"
                        return
                    }
                }

                $searchedVMDetails = $null
                $searchIndex = 0
                $continuePagination = $true
                $searchTotalCount = 0

                while ($continuePagination) {
                    $searchURL = '/irisservices/api/v1/searchvms?from=' + $searchIndex + '&environment=SQL&entityTypes=kSQL&showAll=false&onlyLatestVersion=true&jobIds=' + $JobId
                    $searchResult = Invoke-RestApi -Method Get -Uri $searchURL

                    if ($Global:CohesityAPIStatus.StatusCode -ne 200) {
                        Write-Output "Could not search MSSQL objects with the job id $JobId"
                        return
                    }

                    if ($SqlHost) {
                        # Get the list of SQL objects that can be restored and fetch the id of the specified SQL host
                        $sqlRecords = Find-CohesityObjectsForRestore -Environments KSQL -JobIds $JobId |  Where-Object { $_.ObjectName -eq $SqlObjectName }

                        $vmList = $searchVMResult.vms
                        foreach ($vm in $vmList) {
                            if ($vm.vmDocument.objectAliases[0] -eq $SqlHost) {
                                foreach ($record in $sqlRecords) {
                                    if ($vm.vmDocument.objectId.entity.sqlEntity.ownerId -eq $record.SnapshottedSource.ParentId) {
                                        $SourceId = $record.SnapshottedSource.Id
                                        $HostSourceId = $record.SnapshottedSource.ParentId
                                        break
                                    }
                                }
                            }
                        }
                    }

                    if (!$SourceId) {
                        Write-Output "Please provide source information."
                        return
                    }

                    $searchedVMDetails = $searchResult.vms | Where-Object { ($_.vmDocument.objectId.jobId -eq $JobId) -and ($_.vmDocument.objectId.entity.id -eq $SourceId) }

                    if ($searchTotalCount -eq 0) {
                        # find the expected number of search result items
                        $searchTotalCount = $searchResult.count
                    }

                    if ($searchedVMDetails) {
                        $infoMsg = "Found database with search index " + $searchIndex + ", and total item count " + $searchTotalCount
                        CSLog -Message $infoMsg
                        $continuePagination = $false
                    }

                    # the number of items skimmed
                    $searchIndex += $searchResult.vms.Count


                    if ($searchIndex -ge $searchTotalCount) {
                        $continuePagination = $false
                    }
                    if ($continuePagination -eq $false) {
                        break
                    }
                }
                if ($null -eq $searchedVMDetails) {
                    Write-Output "Could not find details for MSSQL source id = $SourceId , and Job id = $JobId"
                    return
                }

                if (-not $JobRunId) {
                    # Identifying the JobRunId and StartTime based on the last known unexpired snapshot
                    # here the curent system time should be less than the recent successful snapshot expiry time
                    $runs = Get-CohesityProtectionJobRun -JobId $JobId -ExcludeErrorRuns:$true
                    foreach ($record in $runs) {

                        $expiryEpocTime = $record.copyRun[0].expiryTimeUsecs
                        $currentTime = Get-Date
                        $currentEpocTime = Get-Date $currentTime -UFormat %s
                        if ($currentEpocTime -le $expiryEpocTime) {

                            $JobRunId = $record[0].backupRun.jobRunId
                            $StartTime = $record.copyRun[0].runStartTimeUsecs
                            break
                        }
                    }
                }

                if (-not $NewDatabaseName) {
                    $NewDatabaseName = $searchedVMDetails.vmDocument.objectId.entity.sqlEntity.databaseName
                }
                $jobUid = [PSCustomObject]$searchedVMDetails.vmDocument.objectId.jobUid

                if ($RestoreTimeSecs -gt 0) {
                    # validate the point in time value
                    if (-not $StartTime) {
                        Write-Output "Please add start time to validate point in time restore."
                        return
                    }
                    [System.DateTime] $startDate = Convert-CohesityUsecsToDateTime -Usecs $StartTime
                    $startDate = $startDate.AddDays(-1);
                    [long] $startTimeInUsec = Convert-CohesityDateTimeToUsecs -DateTime $startDate
                    $pitJobId = @{
                        clusterId            = $jobUid.clusterId
                        clusterIncarnationId = $jobUid.clusterIncarnationId
                        id                   = $jobUid.objectId
                    }
                    $pointsInTimeRange = @{
                        endTimeUsecs       = Convert-CohesityDateTimeToUsecs -DateTime ([System.DateTime]::now)
                        environment        = "kSQL"
                        jobUids            = @($pitJobId)
                        protectionSourceId = $SourceId
                        startTimeUsecs     = $startTimeInUsec
                    }
                    $pointsForTimeRangeUrl = "/irisservices/api/v1/public/restore/pointsForTimeRange"
                    $payloadJson = $pointsInTimeRange | ConvertTo-Json -Depth 100

                    $timeRangeResult = Invoke-RestApi -Method Post -Uri $pointsForTimeRangeUrl -Body $payloadJson
                    if ($Global:CohesityAPIStatus.StatusCode -eq 201) {
                        [bool]$foundPointInTime = $false;
                        if ($timeRangeResult.TimeRanges) {
                            foreach ($item in $timeRangeResult.TimeRanges) {
                                $restoreTimeUsecs = $RestoreTimeSecs * 1000 * 1000;
                                if (($item.StartTimeUsecs -lt $restoreTimeUsecs) -and ($restoreTimeUsecs -lt $item.EndTimeUsecs)) {
                                    $foundPointInTime = $true;
                                    break;
                                }
                            }
                        }
                    }
                    else {
                        $errorMsg = $Global:CohesityAPIStatus.ErrorMessage + ", Point in time : Failed to query."
                        Write-Output $errorMsg
                        CSLog -Message $errorMsg
                    }
                    if ($false -eq $foundPointInTime) {
                        Write-Output "Invalid point in time value '$RestoreTimeSecs'."
                        return
                    }
                }

                $MSSQL_TARGET_HOST_TYPE = 1
                $MSSQL_OBJECT_RESTORE_TYPE = 3
                $MSSQL_TARGET_PHYSICAL_ENTITY_HOST_TYPE = 1
                $MSSQL_TARGET_PHYSICAL_ENTITY_TYPE = 1
                $targetHostObject = [PSCustomObject]@{
                    id = $TargetHostId
                }
                $targetHostParentSource = $null
                if ($protectionSourceObject.environment -eq "kVMware") {
                    $MSSQL_TARGET_VMWARE_ENTITY_TYPE = 8
                    $vmwareEntity = [PSCustomObject]@{
                        type = $MSSQL_TARGET_VMWARE_ENTITY_TYPE
                        name = $protectionSourceObject.vmWareProtectionSource.name
                    }
                    $targetHostObject | Add-Member -NotePropertyName parentId -NotePropertyValue $protectionSourceObject.parentId
                    $targetHostObject | Add-Member -NotePropertyName vmwareEntity -NotePropertyValue $vmwareEntity
                    $targetHostParentSource = @{
                        id = $protectionSourceObject.parentId
                    }
                }
                else {
                    $MSSQL_TARGET_HOST_TYPE = 6
                    $MSSQL_TARGET_PHYSICAL_ENTITY_HOST_TYPE = 1
                    $MSSQL_TARGET_PHYSICAL_ENTITY_TYPE = 1
                    $physicalEntity = [PSCustomObject]@{
                        type     = $MSSQL_TARGET_PHYSICAL_ENTITY_TYPE
                        name     = $protectionSourceObject.physicalProtectionSource.name
                        hostType = $MSSQL_TARGET_PHYSICAL_ENTITY_HOST_TYPE
                        osName   = $protectionSourceObject.physicalProtectionSource.osName
                    }
                    $targetHostObject | Add-Member -NotePropertyName physicalEntity -NotePropertyValue $physicalEntity
                }
                $targetHostObject | Add-Member -NotePropertyName type -NotePropertyValue $MSSQL_TARGET_HOST_TYPE

                $sqlRestoreParams = [PSCustomObject]@{
                    captureTailLogs                 = $CaptureTailLogs.IsPresent
                    keepCdc                         = $KeepCDC.IsPresent
                    dataFileDestination             = $TargetDataFilesDirectory
                    instanceName                    = $NewInstanceName
                    logFileDestination              = $TargetLogFilesDirectory
                    newDatabaseName                 = $NewDatabaseName
                    isMultiStageRestore             = $false
                    secondaryDataFileDestinationVec = $TargetSecondaryDataFilesDirectoryList
                    alternateLocationParams         = @{}
                }

                if ($DbRestoreOverwritePolicy -eq $true) {

                    $sqlRestoreParams | Add-Member -NotePropertyName dbRestoreOverwritePolicy -NotePropertyValue "kOverwrite"
                }

                if ($RestoreTimeSecs -gt 0) {
                    $sqlRestoreParams | Add-Member -NotePropertyName restoreTimeSecs -NotePropertyValue $RestoreTimeSecs
                }

                $restoreAppObject = @{
                    appEntity     = $searchedVMDetails.vmDocument.objectId.entity
                    restoreParams = @{
                        sqlRestoreParams       = $sqlRestoreParams
                        targetHost             = $targetHostObject
                        targetHostParentSource = $targetHostParentSource
                    }
                }
                $payload = @{
                    action           = "kRecoverApp"
                    name             = $TaskName
                    restoreAppParams = @{
                        type                = $MSSQL_OBJECT_RESTORE_TYPE
                        ownerRestoreInfo    = @{
                            ownerObject        = @{
                                jobUid         = $jobUid
                                jobId          = $JobId
                                jobInstanceId  = $JobRunId
                                startTimeUsecs = $StartTime
                                entity         = @{
                                    id = $HostSourceId
                                }
                            }
                            ownerRestoreParams = @{
                                action           = "kRecoverVMs"
                                powerStateConfig = @{}
                            }
                            performRestore     = $false
                        }
                        restoreAppObjectVec = @($restoreAppObject)
                    }
                }
                $url = '/irisservices/api/v1/recoverApplication'
                $payloadJson = $payload | ConvertTo-Json -Depth 100

                $resp = Invoke-RestApi -Method 'Post' -Uri $url -Body $payloadJson
                if ($Global:CohesityAPIStatus.StatusCode -eq 200) {
                    $taskId = $resp.restoreTask.performRestoreTaskState.base.taskId
                    if ($taskId) {
                        $resp = Get-CohesityRestoreTask -Ids $taskId
                        $resp
                    }
                    else {
                        $resp
                    }
                }
                else {
                    $errorMsg = $Global:CohesityAPIStatus.ErrorMessage + ", MSSQLObject : Failed to recover."
                    Write-Output $errorMsg
                    CSLog -Message $errorMsg
                }
            }
            else {
                Write-Output "Please use cmdlet Restore-CohesityMSSQLObject to restore from active job."
            }
        }
    }
    End {
    }
}