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