CLI Script: Recover to latest point in time for a SQL DB

Published Feb 14 2019 07:55 PM 55 Views
First published on TECHNET on Sep 04, 2007

This script restores the latest recovery point of the specified SQL databases. Save this script as a .ps1 file and run it. Usage and examples of scripts can be found by calling them with ‘-?’ or ‘-help’ from inside DPM Management Shell.

-------------------------------------  Start of Script ---------------------------------

param ([string] $DPMServerName, [string[]] $DatabaseList, [Switch] $RollForwardRecovery)

if(("-?","-help") -contains $args[0])
{
Write-Host "Description: This script restores the latest recovery point of the specified SQL databases."
Write-Host "Usage: Recover-SqlDatabase.ps1 [-DPMServerName] <Name of the DPM server> [-DatabaseList] <Array of SQL databases to recover> [-RollForwardRecovery]"
Write-Host "Example: Recover-SqlDatabase.ps1 mohitc02 `"mohitc04* db`", `"mohitc04reportservertempdb`""

exit 0
}

if (!$DPMServerName)
{
$DPMServerName = Read-Host "DPM server name"

if (!$DPMServerName)
{
Write-Error "Dpm server name not specified."
exit 1
}
}

if (!$DatabaseList)
{
$DatabaseList = Read-Host "SQL database to recover"

if (!$DatabaseList)
{
Write-Error "SQL database(s) not specified."
exit 1
}
}

if (!(Connect-DPMServer $DPMServerName))
{
Write-Error "Failed to connect To DPM server $DPMServerName"
exit 1
}

$datasourceList = @()
Get-ProtectionGroup $DPMServerName | % {Get-Datasource $_} | % {if ($DatabaseList -contains $_.LogicalPath) {$datasourceList += $_}}

# Show all the SQL databases that could not be found as protected datasources.
foreach ($datasourceName in $DatabaseList)
{
if (@($datasourceList | ? {$_.LogicalPath -ieq $datasourceName}).Length -eq 0)
{
Write-Error "Could not find datasource $datasourceName"
}
}

# Recover each SQL datasource from the latest recovery point.
foreach ($datasource in $datasourceList)
{
# Select the latest recovery point that exists on disk and trigger the recovery job.
foreach ($rp in @(Get-RecoveryPoint -Datasource $datasource | sort -Property RepresentedPointInTime -Descending))
{
foreach ($rsl in $rp.RecoverySourceLocations)
{
if ($rsl -is [Microsoft.Internal.EnterpriseStorage.Dls.UI.ObjectModel.OMCommon.ReplicaDataset])
{
$recoveryOption = New-RecoveryOption -TargetServer $datasource.ProductionServerName -RecoveryLocation OriginalServer -SQL -RecoveryType Recover -RollForwardRecovery:$RollForwardRecovery
$recoveryJob = Recover-RecoverableItem -RecoverableItem $rp -RecoveryOption $recoveryOption -RecoveryPointLocation $rsl

break
}
}

if ($recoveryJob)
{
break
}
}

if ($recoveryJob)
{
Write-Host "`nRunning recovery for $($datasource.LogicalPath) from $($rp.RepresentedPointInTime)"

# Comment out the next 7 lines to not wait for one recovery job to finish before triggering the next one.
while (!$recoveryJob.HasCompleted)
{
Write-Host "." -NoNewLine
sleep 3
}

Write-Host "`nJob status: $($recoveryJob.Status)"
}
else
{
Write-Error "Could not find a recovery point on disk for $($datasource.LogicalPath)"
}
}

--------------------------------- End of Script ---------------------------

- Mohit Chakraborty


Version history
Last update:
‎Mar 11 2019 07:57 AM
Updated by: