| 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;
| 
0 comments:
Post a Comment