none
DPM PowerShell SQL Restoration on Clusters Named Instance Failing with [0x80990f85] RRS feed

  • Question

  • Dears

    i am in a process of automation of SQL Restore from DPM PowerShell, the target SQL is SQL Cluster with named Instance, i am running the below script

    ################################################################################
    # Name: RestoreSQLDB.ps1                                                       #
    # Version: 1.04                                                                #
    # Description: This Script Will Restore The Latest Express Full Backup Of      #
    # A Database Originating From A Standalone Or Clustered 'SQL Server A' To      #
    # A Standalone 'SQL Server B'.                                                 #
    # The Database Can Be Renamed On The Destination SQL Server.                   #
    # The File Paths Of The MDF & LDF Files Can Be Changed Too.                    #
    # The $PerformRestore Var Must Be Set To $true To Actually Perform The Restore #
    # This Script Requires DPM Remote Administration Tools (CLI) Are Installed.    #
    # Version Modifications:                                                       #
    #                                                                              #
    ################################################################################
    #To Sign This Script Run These Commands Separately From PS After Requesting A CodeSigning Certificate
    #$Certificate = Get-ChildItem cert:\CurrentUser\My -Codesign
    #Set-AuthenticodeSignature ".\RestoreSQLDB.ps1" -Certificate $Certificate
    #Example - Run From CMD Line
    #C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe -NonInteractive -NoLogo -File "<PATHTOFILE>\RestoreSQLDB.ps1"


    #Static Variables
        $DPMServer = "KUBBAR01" #Name Of DPM Server Prtotecting SQL DB
        $ProtectionGroupSQLStr = "(3) SQL PFGT Backup" #Name Of DPM Protection Group To Restore From
        $SourceServerName = "ClusterName\InstanceName"
        $DestinationServerName = "ClusterGroupName.WindowsClusterName.Domain"
        $SQLDatabaseName = "DBASTATS" #Name Of DB To Restore
        $DestinationDatabaseName = "$SQLDatabaseName"+"_Query" #Name Of The Database That Will Be Created On $DestinationServerName
        $DestinationMDFPath = "V:\Data\" #Check - Dependent On The Restore Server
        $DestinationLDFPath = "X:\Logs\" #Check - Dependent On The Restore Server
        $PerformRestore = $true #$true To Actually Do Restore $false
        $LoggingLogName = "Windows PowerShell" #Event Log To Log Errors To
        $LoggingSource = "PowerShell" #Event Source To Log As
        $LoggingEventID = "65535" #Event Log ID To Use
    #It Begins!!!!
    #    Import-Module DataProtectionManager        
        Connect-DPMServer $DPMServer; #Conenct To DPM Server
        
        #Get PG Object Named $ProtectionGroupSQLStr & Store As A Var
       $ProtectionGroupSQLObj = Get-ProtectionGroup $DPMServer | Where-Object { $_.FriendlyName -eq $ProtectionGroupSQLStr}
        
        #Get DataSource With Name $SQLDatabaseName Running On Instance $SourceServerName & Store As A Var
        $DataSourceSQLObj = Get-DataSource -ProtectionGroup $ProtectionGroupSQLObj | Where-Object { $_.Name -eq $SQLDatabaseName -and $_.Instance -eq $SourceServerName}
        If ($DataSourceSQLObj -ne $null) #Only Continue If DB Exists
            {
            #Add $DataSource As A SQLDataSource And Store As A Var
            $SQLDatabases = [Microsoft.Internal.EnterpriseStorage.Dls.UI.ObjectModel.SQL.SQLDataSource]$DataSourceSQLObj;
            #Get The Latest Disk Based (Express Full) Backup - Fails With Incremental Sync.
            $RecoveryPointsSQLObj = Get-Recoverypoint -DataSource $SQLDatabases | Where-Object { $_.HasFastRecoveryMarker -eq "Fast" -and $_.IsRecoverable -and $_.DataLocation -eq "Disk"}
            
                $LatestRecoveryPoint = $RecoveryPointsSQLObj.Count
                $LatestRecoveryPoint = $LatestRecoveryPoint -1
                $RecoveryPointToRestore = $RecoveryPointsSQLObj[$LatestRecoveryPoint] #Get The Latest RP (1st In List)       
            $length = $RecoveryPointToRestore.PhysicalPath.Length; #Return Number Of Files (i.e. LDF And MDF Files) - 2 = 1x LDF and 1x MDF
            
            #Setup The Alternative DB Object Ready For Restore - Create The Objects & Add As Many FileLocationMapping Placeholders As There Are Files To $AlternativeDatabaseObj
            $AlternativeDatabaseObj = New-Object -TypeName Microsoft.Internal.EnterpriseStorage.Dls.UI.ObjectModel.SQL.AlternateDatabaseDetailsType;
            $LocationMapping = New-Object Microsoft.Internal.EnterpriseStorage.Dls.UI.ObjectModel.SQL.FileLocationMapping[] $length;
            $AlternativeDatabaseObj.LocationMapping = $LocationMapping
            
            $i = 0; #Resets The Count (See While Loop Below)
            While($i -lt $length) #Perform The While Loop While $i Is Less Than The Number Of Files To Restore ($length). Add The Crrent File Names And Locations For Each File To Be Restored
            {        
                $AlternativeDatabaseObj.LocationMapping[$i] = New-Object -TypeName Microsoft.Internal.EnterpriseStorage.Dls.UI.ObjectModel.SQL.FileLocationMapping; #Create The Object
                $AlternativeDatabaseObj.LocationMapping[$i].FileName = $RecoveryPointToRestore.FileSpecifications[$i].FileSpecification; #Set File Name For Files
                $AlternativeDatabaseObj.LocationMapping[$i].SourceLocation = [Microsoft.Internal.EnterpriseStorage.Dls.UI.ObjectModel.OMCommon.PathHelper]::GetParentDirectory($RecoveryPointToRestore.PhysicalPath[$i]); #Set Source Location (Path) For Files
                If ($AlternativeDatabaseObj.LocationMapping[$i].FileName.ToLower().EndsWith(".ldf")) #If LDF File Set Destination Location As $DestinationLDFPath
                {
                    $AlternativeDatabaseObj.LocationMapping[$i].DestinationLocation = $DestinationLDFPath
                }
                Else #If MDF File Set Destination Location As $DestinationMDFPath
                {
                    $AlternativeDatabaseObj.LocationMapping[$i].DestinationLocation = $DestinationMDFPath
                }        
                $i++; #Increment Counter (Move Onto Next File)
            }
            $AlternativeDatabaseObj.InstanceName = $DestinationServerName;  #Set Destination Server Name. If Restoring To Named Instance Include The Instance Name
            $AlternativeDatabaseObj.DatabaseName = $DestinationDatabaseName; #Set Destination DB Name
            
            #Create A Recovery Option Variable Targetted To The Destination Server, Set To Rename The DB And Use The $AlternativeDatabaseObj Details Created Earlier
           $ROP = New-RecoveryOption -TargetServer $DestinationServerName -RecoveryLocation OriginalServerWithDBRename -SQL -RecoveryType Recover -AlternateDatabaseDetails $AlternativeDatabaseObj;
                
            #Load SQL SMO Class - Required To Check If DB Exists On $DestinationServerName
            [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | Out-Null
            #Create A New Object (SMO) Pass It The $DestinationServerName Variable And Store As A Var
            $SQLServerManagement = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server ($DestinationServerName)
            #If DB Exists At Destination, Write To Event Log
            If ($SQLServerManagement.databases[$DestinationDatabaseName] -ne $null)
            {
                Write-Host "Error: DB $DestinationDatabaseName Already Exists On $DestinationServerName - Restore Will Fail"
            }
                        
            If ($PerformRestore) #Only Run Restore If $PerformRestore Is $true
            {
                $RestoreJob = Recover-RecoverableItem -RecoverableItem $RecoveryPointToRestore -RecoveryOption $ROP; #Start The Restore Operation Using The $ROP Recovery Option Var
                
            }
            Else #If $PerformRestore Is Set To $false Write To Event Log
            {
                Write-EventLog -LogName $LoggingLogName -Source $LoggingSource -EventID $LoggingEventID -Message "PerformRestore Varible Is $false - Restore Will Not Happen"
            }
        }
        Else #If DB Doesnt Exist As DataSource In DPM Protection Group Write Error To Event Log
        {
            Write-EventLog -LogName $LoggingLogName -Source $LoggingSource -EventID $LoggingEventID -Message "Database $SQLDatabaseName On $SourceServerName Does Not Exist. Nothing To Restore!"
        }
        
        Disconnect-DPMServer $DPMServer #Disconnect DPM Server

    #############################################################################

    but its failing with the below error which i got it from the DPM Agent on the protected Server

    WARNING    SQL - Error Description "[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied."
    0894    17A8    09/11    09:45:20.708    31    sqlqueryhelper.cpp(358)    [00000000044FF160]        WARNING    Failed: Hr: = [0x80004005] SQL - sql code = 17 (Errorlevel - 0)
    0894    17A8    09/11    09:45:20.708    31    sqlqueryhelper.cpp(366)    [00000000044FF160]        WARNING    SQL - Detailed Description "[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied."
    0894    17A8    09/11    09:45:20.708    31    sqlqueryhelper.cpp(121)    [00000000044FF160]        WARNING    Failed: Hr: = [0x80990f85] Unable to connect to SQL Instance : [SQLClusterGroupName.WindowClusterName.Domain] and Database : [master] after [5] retry attempts

    Please Help

    Wednesday, September 11, 2013 10:10 AM

All replies

  • i solved this problem, but i came into another more complicated problem :)

    The problem:

         SQLVDI: Loc=IdentifySQLServer. Desc=MSSQLSERVER. ErrorCode=(1060)The specified service does not exist as an installed service.
    . Process=5416. Thread=6644. Client. Instance=. VD=.

    Sqllib error: Failed to create VDS object. hr = 0x80770007.

    i hope any body can help me.

    Thursday, September 12, 2013 4:38 AM
  • After hours of reading i think i found the root cause of this mysterious problem,

    DPM Powershell is using a non-native SQL application to perform the SQL Restoration -- (SQLVDI) Interface and this DPM Powershell is passing the default instance name to the VDI Interface (Always) even if the default Instance Name (MSSQLSERVER) is not installed on the server, this is happening as per my understanding because of mis-development in the syntax of CreateEx  function.

    its passing a NULL value for the parameter lpInstanceName which should pass the actual instance name.

    http://troubleshootingsql.com/2009/12/30/sql-vdi-backup-fails-with-0x80770007/

    http://social.msdn.microsoft.com/Forums/sqlserver/en-US/791cfd06-37a0-457d-9c5d-ba0ecec41124/sqlvdi-error-code-1060-when-backing-up-mssql-using-vdi-on-default-instance

    so i am waiting for Microsoft to reply me on this issue.

    Regards.





    • Edited by Ahmed Al-Haffar Thursday, September 12, 2013 10:47 AM more links
    Thursday, September 12, 2013 10:37 AM
  • Hi Ahmed,

       Did you ever hear back from Microsoft on this. We are experiencing the same problem on sql 2014 and are about to raise a case

    Regards

      Chris

    Friday, August 21, 2015 1:31 AM
  • Hello 

    Unfortunately no , but what u r trying to do  

    Monday, September 28, 2015 6:26 AM