Hello. I try automate restoring SQL DB for test environment.

I get existed script from Technet Gallery and adapted him for my conditions.

Look it

Param(
[string]$DPMServer ="dpmserver",
[string]$ProtectionGroupName = "GroupName",
[string]$SourceServerName = "SourceServer",
[string]$DestinationServerName = "DestServer",
[string]$DestinationInstanceName = "DestServer\InstanceName",
[string]$SQLDatabaseName = "DBName",
[string]$DestinationPath = "E:\Data\")

Import-Module DataProtectionManager
$conn = Connect-DPMServer $DPMServer

if ($conn) {

    $ProtectionGroupSQLObj = Get-ProtectionGroup $DPMServer | Where-Object { $_.FriendlyName -eq $ProtectionGroupName} 

    $DataSourceSQLObj = Get-DataSource -ProtectionGroup $ProtectionGroupSQLObj | Where-Object { $_.Name -eq $SQLDatabaseName -and $_.Instance -eq $SourceServerName}

    if ($DataSourceSQLObj -ne $null) {

        $SQLDatabases = [Microsoft.Internal.EnterpriseStorage.Dls.UI.ObjectModel.SQL.SQLDataSource]$DataSourceSQLObj;
        $RecoveryPointsSQLObj = Get-Recoverypoint -DataSource $SQLDatabases | Where-Object { $_.HasFastRecoveryMarker -eq "Fast" -and $_.IsRecoverable -and $_.Location -eq "Disk"}  | Sort-Object BackupTime -Desc;

        If ($RecoveryPointsSQLObj.Count) #Check More Than 1 RP Is Returned
        { 
            $RecoveryPointToRestore = $RecoveryPointsSQLObj[0]; #Get The Latest RP (1st In List)
        } 
        Else #If Only 1 RP Is Returned
        { 
            $RecoveryPointToRestore = $RecoveryPointsSQLObj; 
        }

        $length = $RecoveryPointToRestore.PhysicalPath.Length

        $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

        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 = $DestinationPath
            } 
            Else #If MDF File Set Destination Location As $DestinationMDFPath
            { 
                $AlternativeDatabaseObj.LocationMapping[$i].DestinationLocation = $DestinationPath
            }        
            $i++; #Increment Counter (Move Onto Next File)
        }

        $AlternativeDatabaseObj.InstanceName = $DestinationInstanceName;  #Set Destination Server Name. If Restoring To Named Instance Include The Instance Name 
        
        $AlternativeDatabaseObj.DatabaseName = $SQLDatabaseName; #Set Destination DB Name

        $ROP = New-RecoveryOption -TargetServer $DestinationServerName -SQL -RecoveryLocation OriginalServer -RecoveryType Recover -AlternateDatabaseDetails $AlternativeDatabaseObj -Verbose

        $RestoreJob = Restore-DPMRecoverableItem -RecoverableItem $RecoveryPointToRestore -RecoveryOption $ROP -Verbose

        $Wait = 2; #Initial Wait Time
        While ($RestoreJob -ne $null -and $RestoreJob.HasCompleted -eq $false) 
        { 
            Start-Sleep -Seconds $Wait; 
            $Wait = 20; 
        }

        $RestoreJob
    }

}

The problem is that the script only recovers files, but not the DB.

When I recover this DB from DPM Console, the DB is attached to SQL Server, but don't when I run my script. Application Logs and SQL Server Logs empty.

Somebody can tell me what my problem is?

• Edited by iHumster Thursday, February 26, 2015 10:14 AM

Hi,

Here is the steps to manually backup and restore SQL database:

https://technet.microsoft.com/en-us/library/jj244601.aspx#BKMK_Secondary

For the script related topic you post to this forum:

https://social.technet.microsoft.com/Forums/scriptcenter/en-US/home?forum=ITCG