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

Published Feb 14 2019 07:55 PM 41 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


%3CLINGO-SUB%20id%3D%22lingo-sub-340273%22%20slang%3D%22en-US%22%3ECLI%20Script%3A%20Recover%20to%20latest%20point%20in%20time%20for%20a%20SQL%20DB%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-340273%22%20slang%3D%22en-US%22%3E%0A%20%26lt%3Bmeta%20http-equiv%3D%22Content-Type%22%20content%3D%22text%2Fhtml%3B%20charset%3DUTF-8%22%20%2F%26gt%3B%3CSTRONG%3E%20First%20published%20on%20TECHNET%20on%20Sep%2004%2C%202007%20%3C%2FSTRONG%3E%20%3CBR%20%2F%3E%3CP%3EThis%20script%20restores%20the%20latest%20recovery%20point%20of%20the%20specified%20SQL%20databases.%20Save%20this%20script%20as%20a%20.ps1%20file%20and%20run%20it.%20Usage%20and%20examples%20of%20scripts%20can%20be%20found%20by%20calling%20them%20with%20%E2%80%98-%3F%E2%80%99%20or%20%E2%80%98-help%E2%80%99%20from%20inside%20DPM%20Management%20Shell.%3C%2FP%3E%0A%20%20%3CP%3E-------------------------------------%26nbsp%3B%20Start%20of%20Script%20---------------------------------%3C%2FP%3E%0A%20%20%3CP%3Eparam%20(%5Bstring%5D%20%24DPMServerName%2C%20%5Bstring%5B%5D%5D%20%24DatabaseList%2C%20%5BSwitch%5D%20%24RollForwardRecovery)%3C%2FP%3E%0A%20%20%3CP%3Eif((%22-%3F%22%2C%22-help%22)%20-contains%20%24args%5B0%5D)%20%3CBR%20%2F%3E%20%7B%20%3CBR%20%2F%3E%20Write-Host%20%22Description%3A%20This%20script%20restores%20the%20latest%20recovery%20point%20of%20the%20specified%20SQL%20databases.%22%20%3CBR%20%2F%3E%20Write-Host%20%22Usage%3A%20Recover-SqlDatabase.ps1%20%5B-DPMServerName%5D%20%3CNAME%20of%3D%22%22%20the%3D%22%22%20dpm%3D%22%22%20server%3D%22%22%3E%20%5B-DatabaseList%5D%20%3CARRAY%20of%3D%22%22%20sql%3D%22%22%20databases%3D%22%22%20to%3D%22%22%20recover%3D%22%22%3E%20%5B-RollForwardRecovery%5D%22%20%3CBR%20%2F%3E%20Write-Host%20%22Example%3A%20Recover-SqlDatabase.ps1%20mohitc02%20%60%22mohitc04*%20db%60%22%2C%20%60%22mohitc04reportservertempdb%60%22%22%3C%2FARRAY%3E%3C%2FNAME%3E%3C%2FP%3E%0A%20%20%3CP%3Eexit%200%20%3CBR%20%2F%3E%20%7D%3C%2FP%3E%0A%20%20%3CP%3Eif%20(!%24DPMServerName)%20%3CBR%20%2F%3E%20%7B%20%3CBR%20%2F%3E%20%24DPMServerName%20%3D%20Read-Host%20%22DPM%20server%20name%22%3C%2FP%3E%0A%20%20%3CP%3Eif%20(!%24DPMServerName)%20%3CBR%20%2F%3E%20%7B%20%3CBR%20%2F%3E%20Write-Error%20%22Dpm%20server%20name%20not%20specified.%22%20%3CBR%20%2F%3E%20exit%201%20%3CBR%20%2F%3E%20%7D%20%3CBR%20%2F%3E%20%7D%3C%2FP%3E%0A%20%20%3CP%3Eif%20(!%24DatabaseList)%20%3CBR%20%2F%3E%20%7B%20%3CBR%20%2F%3E%20%24DatabaseList%20%3D%20Read-Host%20%22SQL%20database%20to%20recover%22%3C%2FP%3E%0A%20%20%3CP%3Eif%20(!%24DatabaseList)%20%3CBR%20%2F%3E%20%7B%20%3CBR%20%2F%3E%20Write-Error%20%22SQL%20database(s)%20not%20specified.%22%20%3CBR%20%2F%3E%20exit%201%20%3CBR%20%2F%3E%20%7D%20%3CBR%20%2F%3E%20%7D%3C%2FP%3E%0A%20%20%3CP%3Eif%20(!(Connect-DPMServer%20%24DPMServerName))%20%3CBR%20%2F%3E%20%7B%20%3CBR%20%2F%3E%20Write-Error%20%22Failed%20to%20connect%20To%20DPM%20server%20%24DPMServerName%22%20%3CBR%20%2F%3E%20exit%201%20%3CBR%20%2F%3E%20%7D%3C%2FP%3E%0A%20%20%3CP%3E%24datasourceList%20%3D%20%40()%20%3CBR%20%2F%3E%20Get-ProtectionGroup%20%24DPMServerName%20%7C%20%25%20%7BGet-Datasource%20%24_%7D%20%7C%20%25%20%7Bif%20(%24DatabaseList%20-contains%20%24_.LogicalPath)%20%7B%24datasourceList%20%2B%3D%20%24_%7D%7D%3C%2FP%3E%0A%20%20%3CP%3E%23%20Show%20all%20the%20SQL%20databases%20that%20could%20not%20be%20found%20as%20protected%20datasources.%20%3CBR%20%2F%3E%20foreach%20(%24datasourceName%20in%20%24DatabaseList)%20%3CBR%20%2F%3E%20%7B%20%3CBR%20%2F%3E%20if%20(%40(%24datasourceList%20%7C%20%3F%20%7B%24_.LogicalPath%20-ieq%20%24datasourceName%7D).Length%20-eq%200)%20%3CBR%20%2F%3E%20%7B%20%3CBR%20%2F%3E%20Write-Error%20%22Could%20not%20find%20datasource%20%24datasourceName%22%20%3CBR%20%2F%3E%20%7D%20%3CBR%20%2F%3E%20%7D%3C%2FP%3E%0A%20%20%3CP%3E%23%20Recover%20each%20SQL%20datasource%20from%20the%20latest%20recovery%20point.%20%3CBR%20%2F%3E%20foreach%20(%24datasource%20in%20%24datasourceList)%20%3CBR%20%2F%3E%20%7B%20%3CBR%20%2F%3E%20%23%20Select%20the%20latest%20recovery%20point%20that%20exists%20on%20disk%20and%20trigger%20the%20recovery%20job.%20%3CBR%20%2F%3E%20foreach%20(%24rp%20in%20%40(Get-RecoveryPoint%20-Datasource%20%24datasource%20%7C%20sort%20-Property%20RepresentedPointInTime%20-Descending))%20%3CBR%20%2F%3E%20%7B%20%3CBR%20%2F%3E%20foreach%20(%24rsl%20in%20%24rp.RecoverySourceLocations)%20%3CBR%20%2F%3E%20%7B%20%3CBR%20%2F%3E%20if%20(%24rsl%20-is%20%5BMicrosoft.Internal.EnterpriseStorage.Dls.UI.ObjectModel.OMCommon.ReplicaDataset%5D)%20%3CBR%20%2F%3E%20%7B%20%3CBR%20%2F%3E%20%24recoveryOption%20%3D%20New-RecoveryOption%20-TargetServer%20%24datasource.ProductionServerName%20-RecoveryLocation%20OriginalServer%20-SQL%20-RecoveryType%20Recover%20-RollForwardRecovery%3A%24RollForwardRecovery%20%3CBR%20%2F%3E%20%24recoveryJob%20%3D%20Recover-RecoverableItem%20-RecoverableItem%20%24rp%20-RecoveryOption%20%24recoveryOption%20-RecoveryPointLocation%20%24rsl%3C%2FP%3E%0A%20%20%3CP%3Ebreak%20%3CBR%20%2F%3E%20%7D%20%3CBR%20%2F%3E%20%7D%3C%2FP%3E%0A%20%20%3CP%3Eif%20(%24recoveryJob)%20%3CBR%20%2F%3E%20%7B%20%3CBR%20%2F%3E%20break%20%3CBR%20%2F%3E%20%7D%20%3CBR%20%2F%3E%20%7D%3C%2FP%3E%0A%20%20%3CP%3Eif%20(%24recoveryJob)%20%3CBR%20%2F%3E%20%7B%20%3CBR%20%2F%3E%20Write-Host%20%22%60nRunning%20recovery%20for%20%24(%24datasource.LogicalPath)%20from%20%24(%24rp.RepresentedPointInTime)%22%3C%2FP%3E%0A%20%20%3CP%3E%23%20Comment%20out%20the%20next%207%20lines%20to%20not%20wait%20for%20one%20recovery%20job%20to%20finish%20before%20triggering%20the%20next%20one.%20%3CBR%20%2F%3E%20while%20(!%24recoveryJob.HasCompleted)%20%3CBR%20%2F%3E%20%7B%20%3CBR%20%2F%3E%20Write-Host%20%22.%22%20-NoNewLine%20%3CBR%20%2F%3E%20sleep%203%20%3CBR%20%2F%3E%20%7D%3C%2FP%3E%0A%20%20%3CP%3EWrite-Host%20%22%60nJob%20status%3A%20%24(%24recoveryJob.Status)%22%20%3CBR%20%2F%3E%20%7D%20%3CBR%20%2F%3E%20else%20%3CBR%20%2F%3E%20%7B%20%3CBR%20%2F%3E%20Write-Error%20%22Could%20not%20find%20a%20recovery%20point%20on%20disk%20for%20%24(%24datasource.LogicalPath)%22%20%3CBR%20%2F%3E%20%7D%20%3CBR%20%2F%3E%20%7D%20%3CBR%20%2F%3E%3C%2FP%3E%0A%20%20%3CP%3E---------------------------------%20End%20of%20Script%20---------------------------%3C%2FP%3E%0A%20%20%3CP%3E-%20%3CA%20href%3D%22http%3A%2F%2Fblogs.technet.com%2Fdpm%2Fpages%2Fmohit-chakraborty-s-bio.aspx%22%20mce_href%3D%22http%3A%2F%2Fblogs.technet.com%2Fdpm%2Fpages%2Fmohit-chakraborty-s-bio.aspx%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3E%20Mohit%20Chakraborty%20%3C%2FA%3E%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-340273%22%20slang%3D%22en-US%22%3EFirst%20published%20on%20TECHNET%20on%20Sep%2004%2C%202007%20This%20script%20restores%20the%20latest%20recovery%20point%20of%20the%20specified%20SQL%20databases.%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-340273%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ESystem%20Center%20Data%20Protection%20Manager%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Version history
Last update:
‎Mar 11 2019 07:57 AM
Updated by: