Saturday, January 21, 2012

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

8 comments:

M0nsterMagnet said...

Hi Friend,

I´m use script but result error:

My DPM is PT-BR

Analyzing the lines not found errors.

Problem my config?

É necessário fornecer uma expressão de valor no lado direito do operador '-and'
.
Em C:\Program Files\Microsoft DPM\DPM\bin\restore-sql.ps1:57 caractere:74
+ $_.RepresentedPointInTime -ge $startDate -and <<<
< `
+ CategoryInfo : ParserError: (:) [], ParseException
+ FullyQualifiedErrorId : ExpectedValueExpression

JCtech said...

Raj,

Will your script allow for a restore from tape in DPM and Allow the datbase to be restored to a differnet name?

I need to restore PP to PT on the same server from a tape backup.
Differnet directories for each database and log file.
Thanks
Bill

Raj Rao said...

JCTech, I dont know if it will allow to restore from tape. But I also dont see why not. You could use it as a starting point and tweak as needed.
You certainly can rename the database. But I dont know about different directories. Its been a while since I used the script and forget all its capabilities.

Anonymous said...

Raj,

the params

serverName
restoreToServerName

are the name of the servers, not the instances, right?

Is there a way to define the name of the Restore instance?

Thanks,
Vladimir

Raj Rao said...

Vladimir, serverName would be the server name + instance (eg: dbServer\instance).

Try it out and let me know if it works.

Ian said...

i a completely oblivious when it comes to deciphering powershell on my own.

i copied this PS to my dpm server. i saw the the flag for $doTheRestore was set to $false. i am taking this as this will run but will not actually restore. So i ran the script to see what put put i get. it returns to the next time. where do i put in my data to get results?

I have bee trying to figure this out on my own, but I am at the point where i need to ask for some guidance. please assist if you can.

Thank you
Ian

Raj Rao said...

Ian,

You need to change the values right at the bottom of the entire script. So it would be the part that looks like this: (Also note that doTheRestore is set to true and so it will run the restore. Set it to false to just test it out)

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;

Raj Rao said...

Get the code from: http://pastebin.com/XgphrdB8