DPM–Restoring a protected SqlServer database to a different server

Reason: I needed to restore a SqlServer database from production to a dev server.

Unfortunately the help documentation for the DPM cmdlets are pretty much useless as they don’t tell you how a cmdlet should be used or what parameters (or combination of parameters are allowed). In addition, most online posts report that you cannot directly perform a restore of SqlServer database to a different server (and that you need to restore the files and then run scripts to reattach the database). I was lucky to happen upon Wilson Souza’s post and modified his source-code to do exactly what I needed it to do: restore a protected production database to a development server. Here is the script that I came up with.

Notes:

• It has been tested against DPM 2010. (not sure if it will work against DPM 2007).
• An example of how to call the function is provided at the bottom
• As part of the restore process, the script will drop the database on the destination server if it already exists
• The source database must be part of a protection group (duh! obvious)
• The destination database server must have the protection agent installed on it and the agent should be running
• the params required are pretty self explanatory – but look at the bottom of the script for an example
• Need to install DPM management shell on the machine from where DPM scripts will be run (if its not the DPM server)

function RestoreDbFromDPM #this script is based on Wilson Souza's work as described at #http://social.technet.microsoft.com/Forums/en-US/dpmsqlbackup/thread/e5e50339-5707-4e72-bb9a-56f6d60ba926 {     Param([string] $dpmServerName, [string] $sqlProtectionGroupName,     [string] $serverName, [string] $databaseName, [string] $restoreToServerName,     [string] $restorePathMdf, [string] $restorePathLog, [bool] $doTheRestore = $false,     [string] $dateOfRestoreToUse = $null,     [string] $dpmServerUsedToProtectClient = $null)     $startDate = $null;     $endDate = $null;     if ($dateOfRestoreToUse -ne $null -and $dateOfRestoreToUse.Length -gt 0) #if a date was provided then setup a date-range     {         $startDate = Get-Date $dateOfRestoreToUse;         $endDate = $startDate.AddDays(1).AddSeconds(-1); #one day     }     if ( (Get-PSSnapin -Name 'Microsoft.DataProtectionManager.PowerShell' -ErrorAction SilentlyContinue) -eq $null )     {         Add-PSSnapin -Name 'Microsoft.DataProtectionManager.PowerShell'         Write-Host "Completed loading DPM powershell snapin"     }     else     {         Write-Host "DPM powershell snapin is already loaded"     }         RepointDpmAgent $dpmServerName $restoreToServerName             Connect-DPMServer $dpmServerName; #lets connect     $sqlProtectionGroup=Get-ProtectionGroup $dpmServerName | where-object { $_.FriendlyName -eq $sqlProtectionGroupName}     if($sqlProtectionGroup -eq $null)     {         Write-Host "The protection group $sqlProtectionGroupName was not found on the server $dpmServerName";         return;     }         #find the data-source for the database-name on the server requested     $sqlDataSource = Get-DataSource -ProtectionGroup $sqlProtectionGroup | where-object { $_.name -eq $databaseName -and $_.Instance -eq $serverName}     if ($sqlDataSource -ne $null)     {         $sqlDs = [Microsoft.Internal.EnterpriseStorage.Dls.UI.ObjectModel.SQL.SQLDataSource]$sqlDataSource;         #find a recoverypoint that is not incremental - not sure why incremenatl restores bomb!         $recoveryPoints = Get-Recoverypoint -DataSource $sqlDs | where-object { $_.HasFastRecoveryMarker -eq "Fast" -and $_.IsRecoverable}         if ($recoveryPoints -eq $null)         {             Write-Host "A recovery point for the database: $databaseName on server: $serverName was not found in the protection group $sqlProtectionGroup" -ForegroundColor Red             return         }         if ($startDate -ne $null) #range has been provided - lets find a recovery point within the date range         {             $recoveryPoints = $recoveryPoints | `                         Where-Object {`                             $_.RepresentedPointInTime -ge $startDate -and `                             $_.RepresentedPointInTime -lt $endDate`                         };                         if ($recoveryPoints -ne $null)             {                 Write-Host "A recovery point for the specified date: $startDate was found. Recovery Point date: $($recoveryPoints.RepresentedPointInTime)"             }             else             {                 Write-Host "A recovery point for the database: $databaseName on server: $serverName was not found "`                                 "in the protection group $sqlProtectionGroupName "`                                 "within the daterange: $startDate to $endDate. "`                                 "Restore cannot proceed!" `                                 -ForegroundColor Red                 return             }         }                 if ($recoveryPoints.Count) #check if we got back an array         {             $recoveryPointToUse = $recoveryPoints[-1]; #array - select the latest         }         else         {             $recoveryPointToUse = $recoveryPoints;         }                 if ($recoveryPointToUse -eq $null)#what! fail!         {             Write-Host "A recovery point for the database: $databaseName on server: $serverName was not found in the protection group $sqlProtectionGroup" -ForegroundColor Red             return         }                         $length = $recoveryPointToUse.PhysicalPath.Length; #Length = num files (eg: mdf and log = 2)                 #lets setup the alt.database details.         $alternateDatabaseDetails = New-Object -TypeName Microsoft.Internal.EnterpriseStorage.Dls.UI.ObjectModel.SQL.AlternateDatabaseDetailsType;         $LocationMapping = New-Object Microsoft.Internal.EnterpriseStorage.Dls.UI.ObjectModel.SQL.FileLocationMapping[] $length;          $alternateDatabaseDetails.LocationMapping = $LocationMapping                 $i = 0;         $a = $null;         while($i -lt $length)         {                    $alternateDatabaseDetails.LocationMapping[$i] = New-Object -TypeName Microsoft.Internal.EnterpriseStorage.Dls.UI.ObjectModel.SQL.FileLocationMapping;             $alternateDatabaseDetails.LocationMapping[$i].FileName = $recoveryPointToUse.FileSpecifications[$i].FileSpecification;             $alternateDatabaseDetails.LocationMapping[$i].SourceLocation = [Microsoft.Internal.EnterpriseStorage.Dls.UI.ObjectModel.OMCommon.PathHelper]::GetParentDirectory($recoveryPointToUse.PhysicalPath[$i]);             if ($alternateDatabaseDetails.LocationMapping[$i].FileName.ToLower().EndsWith(".ldf"))             {                 $alternateDatabaseDetails.LocationMapping[$i].DestinationLocation = $restorePathLog                 #= $alternateDatabaseDetails.LocationMapping[$i].SourceLocation;             }             else             {                 $alternateDatabaseDetails.LocationMapping[$i].DestinationLocation = $restorePathMdf             }                    $i++;         }         $alternateDatabaseDetails.InstanceName = $restoreToServerName;         $alternateDatabaseDetails.DatabaseName = $databaseName;                 $recoveryOption = New-RecoveryOption `             -TargetServer $restoreToServerName `             -RecoveryLocation OriginalServerWithDBRename `             -SQL `             -RecoveryType Recover `             -AlternateDatabaseDetails $alternateDatabaseDetails;                     #drop the database if it already exists - else DPM fails on the restore         DropDatabase $restoreToServerName $databaseName;          $dbsize = ($recoveryPointToUse.Size / (1024*1024*1024)).ToString(".###");         Write-Host "restoring database: $($alternateDatabaseDetails.DatabaseName)`n"`                     " with backup from $($recoveryPointToUse.RepresentedPointInTime)`n"`                     "  to Sql-Server: $($alternateDatabaseDetails.InstanceName)`n"`                     "   DB size:  $dbsize GB .....`n"                             if ($doTheRestore)         {             $restoreJob = Recover-RecoverableItem -RecoverableItem $recoveryPointToUse -RecoveryOption $recoveryOption;             Write-Host "Restore Status: $($restoreJob.Status)`n HasCompleted: $($restoreJob.HasCompleted)`n  Start: $($restoreJob.StartTime)"             $waitTime = 2; #initial wait time             while ($restoreJob -ne $null -and $restoreJob.HasCompleted -eq $false)             {                 Write-Host "." -NoNewline;                 Start-Sleep -Seconds $waitTime;                 $waitTime = 20;             }                         Write-Host ""             if($restoreJob.Status -ne "Succeeded")             {                 Write-Host "Restore Status: $($restoreJob.Status)`n Start: $($restoreJob.StartTime)`n  End: $($restoreJob.EndTime)" -ForeGroundColor Red             }             else             {                 Write-Host "Restore Status: $($restoreJob.Status)`n Start: $($restoreJob.StartTime)`n  End: $($restoreJob.EndTime)" -ForeGroundColor DarkGreen             }                         $td = (New-Timespan -Start $restoreJob.StartTime -end $restoreJob.EndTime)             Write-Host "Elapsed time: Hours: $($td.Hours) Minutes:$($td.Minutes) Seconds:$($td.Seconds) MSecs:$($td.Milliseconds)"         }         else         {             Write-Host "DoTheRestore is set to false - restore is not being performed" -BackgroundColor Red         }     }     else     {         Write-Host "Database $databaseName on $serverName was not found" -ForeGroundColor Red     }         Disconnect-DPMServer $dpmServerName         if ($dpmServerUsedToProtectClient.Length -gt 0)#we have been provided the dpmserver for protection - so repoint     {         RepointDpmAgent $dpmServerUsedToProtectClient $restoreToServerName        }    } function DropDatabase([string] $restoreToServerName, [string] $databaseName) {     Write-Host "Checking if database $databaseName needs to be dropped"     [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null     $sqlServerSmo = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server ($restoreToServerName)         if ($sqlServerSmo.databases[$databaseName] -ne $null)     {         Write-Host "Dropping database $databaseName on server $restoreToServerName"         $sqlServerSmo.KillAllProcesses($databaseName)         $sqlServerSmo.databases[$databaseName].drop()         Write-Host "Database $databaseName on server $restoreToServerName has been dropped"     }     else     {         Write-Host "Database $databaseName does not exist on server $restoreToServerName"     }         Write-Host "" } function RepointDpmAgent([string] $dpmServerName, [string] $dpmClient) {     Write-Host "Setting the DPMServer for $dpmClient to $dpmServerName"     Invoke-Command -ComputerName $dpmClient `                   -ArgumentList $dpmServerName `                   -ScriptBlock {param($serverName) $cmd = "C:\Program Files\Microsoft Data Protection Manager\DPM\bin\SetDpmServer.exe"; & $cmd -dpmServerName $serverName;} } ####example of how to call the above functions to perform a restore cls; #dateOfRestoreToUse is optional. If not provided – it will use the latest available restorepoint #doTheRestore is optional. if not true – the script will be run in demo mode and will not perform the restore RestoreDbFromDPM `     -dpmServerName "dpmServerName" `     -sqlProtectionGroupName "ProtectionGroupName" `     -serverName "DatabaseServerOnWhichTheDatabaseResides" `     -databaseName "NameOfTheDatabaseToBeRecovered" `     -restoreToServerName "DatabaseServerToWhichTheRestoreShouldBePerformed" `     -restorePathMdf "D:\MSSQLData\" `     -restorePathLog "L:\MSSQLLog\" `     -dateOfRestoreToUse "2012/01/19" `      -dpmServerUsedToProtectClient "dpmServerThat ServerName should point to for day to day protection" ` #(not required)     -doTheRestore $true;

More info:

Another restore sample: http://social.msdn.microsoft.com/Forums/en-SG/sqldisasterrecovery/thread/6c011fcd-765d-4f02-adb0-1975bd5c2f94
Yet another restore sample: http://the-network-guy.blogspot.com/2009/01/automating-dpm-recovery.html