Restoring SQL Server backups in System Center Data Protection Manager in your test environment automaticly with powershell

After some time with System Center Data Protection Manager (DPM) in production, I had the need to find a way to automate SQL Server restores in my test environment.

My first try was to use System Center Orchestrator since, theoretically, automate process is one of its main functions.

After some research I found this article, but after a few tests I noticed that using  Orchestrator would not be that simple and that problably I would have to do something using powershell.

A nice starting point was this article, that explains the basics of powershell for DPM, but it wasn’t enough.

After reading a few more articles I finally found an interesting post in a forum with a script that was the starting point for my solution. The script in question helped a lot and after that I found another article with a script based on the previous one, so I managed to adapt it to my needs and also to make it compatible with DPM 2012.

The ideia is to allow automatic restore of ALL the databases from production server, made by a DPM server, in a test environment, but you can use it to many other things.

The script code is bellow, with a few explanations inside it. It has, among some other features, the option to show messages in brazilian portuguese or english! Just save the code in a .ps1 file and execute it in your DPM server, after following some recommended steps mentioned on the beggining of it.

I hope it helps a lot of people, since by my own experience, there isn’t many documentation about this subject:

#this script is based on Raj Rao’s work as described at
#http://blog.aggregatedintelligence.com/2012/01/dpmrestoring-protected-sqlserver.html
#which 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
#and is compatible with SCDPM 2012

#pre-requisites

#you must run Enable-PSRemoting on Restore Server

#you must also run the commands bellow
#source for commands below
#http://social.technet.microsoft.com/Forums/en-US/dpmpowershell/thread/e34a5413-89a7-475a-833b-ec9030e2f0cb

#Run Get-WSManCredSSP on server from where .ps1 will run
#if you receive
#The machine is configured to allow delegating fresh credentials to the following target(s): wsman/*
#This computer is not configured to receive credentials from a remote client computer
#you are fine
#if not, you should run:
#Enable-WSManCredSSP -role client -DelegateComputer *

#Run Get-WSManCredSSP on remote server
#if you receive
#The machine is not configured to allow delegating fresh credentials.
#This computer is configured to receive credentials from a remote client computer
#you are fine
#if not, you should run:
#Enable-WSManCredSSP -role server
function RestoreDbFromDPM
{
#Parâmetros de entrada
Param(
[string] $dpmServerName, [string] $sqlProtectionGroupName,
[string] $serverName, [string] $databaseName, [string] $restoreToServerName, [string] $restorePathMdf,
[string] $restorePathLog, [bool] $doTheRestore = $false,
[string] $dateOfRestoreToUse = $null,
[string] $dpmServerUsedToProtectClient = $null,
[string] $restoreToSQLInstance, [string] $NewdatabaseName = $null,
[bool] $DropOriginalDatabase = $false,
[String] $Source = $null, [bool] $allDatabases = $false,
[bool] $SystemDatabases = $false,
[string] $idiom = “english”
)

$startDate = $null;
$endDate = $null;

#making sure there is no variables with values that would confuse the code
if($allDatabases -eq $true)
{
$databaseName = $null
$NewdatabaseName = $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
}

#load DPM snapin
if ( (Get-PSSnapin -Name ‘Microsoft.DataProtectionManager.PowerShell’ -ErrorAction SilentlyContinue) -eq $null )
{
#Add-PSSnapin -Name ‘Microsoft.DataProtectionManager.PowerShell’ –does not work for DPM2012
Import-Module DataProtectionManager

if ($idiom -eq “english”) {
Write-Host “Completed loading DPM powershell snapin”
}
else {
Write-Host “O snapin do powershell foi carregado com sucesso!”
}
}
else
{
if ($idiom -eq “english”) {
Write-Host “DPM powershell snapin is already loaded”
}
else {
Write-Host “O snapin do powershell jã está carregado na memória!”
}
}

RepointDpmAgent $dpmServerName $restoreToServerName

Connect-DPMServer $dpmServerName; #lets connect

#create or reset the file
out-file c:\DPM.txt -encoding ASCII

$sqlProtectionGroup=Get-ProtectionGroup $dpmServerName | where-object { $_.FriendlyName -eq $sqlProtectionGroupName}
if($sqlProtectionGroup -eq $null)
{
if ($idiom -eq “english”) {
Write-Host “`nThe protection group $sqlProtectionGroupName was not found on the server
$dpmServerName”;
“The protection group $sqlProtectionGroupName was not found on the server $dpmServerName`n” | out-
file c:\DPM.txt -encoding ASCII -append
}
else {
Write-Host “`nO protection group $sqlProtectionGroupName não foi encontrado no servidor
$dpmServerName”;
“O protection group $sqlProtectionGroupName não foi encontrado no servidor $dpmServerName`n” | out-
file c:\DPM.txt -encoding ASCII -append
}

return;
}
else
{
if ($idiom -eq “english”) {
Write-Host “`nThe protection group $sqlProtectionGroupName was found on the server $dpmServerName”;
}
else {
Write-Host “`nO protection group $sqlProtectionGroupName foi encontrado no servidor $dpmServerName”;
}
}

#get the list of the libraries
$LIB = Get-DPMLibrary –DPMServerName $dpmServerName

#find out what is the library so we can use it on $recoveryOption for Recover-RecoverableItem
#for now I am not considering the possibility there is more than one library, so I am using only the first one listed
($LIB[0])

if($allDatabases -eq $true)
{

#Get the list of databases in the protection group
$databaseList = Get-DataSource -ProtectionGroup $sqlProtectionGroup | foreach {$_.name} | sort-object | get-
unique

if ($idiom -eq “english”) {
write-host “`nList of Databases:`n”
}
else {
write-host “`nLIsta de bancos de dados:`n”
}

-split $databaseList

foreach($databaseInstance in $databaseList) {

if($SystemDatabases -ne $true)
{
if($databaseInstance -ne “master” -and $databaseInstance -ne “model” -and $databaseInstance
-ne “msdb” -and $databaseInstance -ne “distribution”)
{

if ($idiom -eq “english”) {
write-host “`nBeginning the process of restoring the database
$databaseInstance`n”
}
else {
write-host “`nIniciando o processo de restauração do banco de dados
$databaseInstance`n”
}

RestoreSingleDatabase -databaseName $databaseInstance -restoreToServerName
$restoreToServerName -restorePathMdf $restorePathMdf -restorePathLog $restorePathLog -doTheRestore $doTheRestore –
dateOfRestoreToUse $dateOfRestoreToUse -restoreToSQLInstance $restoreToSQLInstance -NewdatabaseName $Newdatabasename –
DropOriginalDatabase $DropOriginalDatabase -Source $Source
}
else
{
if ($idiom -eq “english”) {
write-host “`nIgnoring database $databaseInstance since ‘-systemDatabases’ is
set to FALSE`n”
}
else {
write-host “`nIgnorando banco de dados $databaseInstance já que a opção ‘-
systemDatabases’ está configurada como FALSE`n”
}
}
}
else
{
if($databaseInstance -eq “master” -or $databaseInstance -eq “model” -or $databaseInstance -eq
“msdb” -or $databaseInstance -eq “distribution”)
{
$NewdatabaseName = $databaseInstance + “_temp”
}
if ($idiom -eq “english”) {
write-host “`nBeginning the process of restoring the database $NewdatabaseName`n”
}
else {
write-host “`nIniciando o processo de restauração do banco de dados
$NewdatabaseName`n”
}

RestoreSingleDatabase -databaseName $databaseInstance -restoreToServerName
$restoreToServerName -restorePathMdf $restorePathMdf -restorePathLog $restorePathLog -doTheRestore $doTheRestore –
dateOfRestoreToUse $dateOfRestoreToUse -restoreToSQLInstance $restoreToSQLInstance -NewdatabaseName $NewdatabaseName –
DropOriginalDatabase $DropOriginalDatabase -Source $Source

#reset variable that was used to set the suffix to the database name, so it may use this name
to the next database
$NewdatabaseName = $null;
}

}
}
else
{

if ($idiom -eq “english”) {
write-host “`nBeginning the process of restoring the database $databaseName`n”
}
else {
write-host “`nIniciando processo de restauração do banco de dados $databaseName`n”
}

RestoreSingleDatabase -databaseName $databaseName -restoreToServerName $restoreToServerName -restorePathMdf
$restorePathMdf -restorePathLog $restorePathLog -doTheRestore $doTheRestore -dateOfRestoreToUse $dateOfRestoreToUse –
restoreToSQLInstance $restoreToSQLInstance -NewdatabaseName $NewdatabaseName -DropOriginalDatabase $DropOriginalDatabase –
Source $Source

}
if ($idiom -eq “english”) {
Sendmail -subject “Restore of databases on server $Servername” -idiom $idiom
}
else {
Sendmail -subject “Restore de bancos de dados no servidor $Servername” -idiom $idiom
}
if ($idiom -eq “english”) {
write-host “Disconnecting from server $dpmServerName”
}
else {
write-host “Desconectando do servidor $dpmServerName”
}
Disconnect-DPMServer $dpmServerName

if ($dpmServerUsedToProtectClient.Length -gt 0)#we have been provided the dpmserver for protection – so repoint
{
RepointDpmAgent $dpmServerUsedToProtectClient $restoreToServerName
}
}
function RestoreSingleDatabase
{
Param(
[string] $databaseName,
[string] $restoreToServerName,
[string] $restorePathMdf,
[string] $restorePathLog,
[bool] $doTheRestore = $false,
[string] $dateOfRestoreToUse = $null,
[string] $restoreToSQLInstance,
[string] $NewdatabaseName = $null,
[bool] $DropOriginalDatabase = $false,
[String] $Source = $null
)

#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)
{
if ($idiom -eq “english”) {
Write-Host “`nThe Data Source: $sqlDataSource was found!`n”
}
else {
Write-Host “`nO Data Source: $sqlDataSource foi encontrado!`n”
}

$sqlDs = [Microsoft.Internal.EnterpriseStorage.Dls.UI.ObjectModel.SQL.SQLDataSource]$sqlDataSource;
#find a recoverypoint that is not incremental – not sure why incremental restores bomb! (full express backups)
#allowed searching for backups in media and disk since
$recoveryPoints = Get-Recoverypoint -DataSource $sqlDs | where-object { $_.HasFastRecoveryMarker -eq “Fast” -and
$_.IsRecoverable -and $Source -match $_.datalocation} | sort-object $_.datalocation #list disk backups first

if ($recoveryPoints -eq $null)
{
if ($idiom -eq “english”) {
Write-Host “A recovery point for the database: $databaseName on server: $serverName was not found in
the protection group $($sqlProtectionGroup.FriendlyName)`n” -ForegroundColor Red
“A recovery point for the database: $databaseName on server: $serverName was not found in the
protection group $($sqlProtectionGroup.FriendlyName)`n” | out-file c:\DPM.txt -encoding ASCII -append
}
else {
Write-Host “Não foi encontrado um recovery point para o banco de dados: $databaseName no servidor:
$serverName dentro do protection group $($sqlProtectionGroup.FriendlyName)`n” -ForegroundColor Red
“Não foi encontrado um recovery point para o banco de dados: $databaseName no servidor: $serverName
dentro do protection group $($sqlProtectionGroup.FriendlyName)`n” | out-file c:\DPM.txt -encoding ASCII -append
}

return
}
else
{
if ($idiom -eq “english”) {
Write-Host “A recovery point for the database: $databaseName on server: $serverName was found in the
protection group $($sqlProtectionGroup.FriendlyName)`n”
}
else {
Write-Host “Foi encontrado um recovery point para o banco de dados: $databaseName no servidor:
$serverName dentro do protection group $($sqlProtectionGroup.FriendlyName)`n”
}
}

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)
{
if ($idiom -eq “english”) {
Write-Host “A recovery point for the specified date: $startDate was found. Recovery Point date:
$($recoveryPoints.RepresentedPointInTime)”
}
else {
Write-Host “Um recovery point para a data especificada: $startDate foi encontrado. Data do Recovery
Point: $($recoveryPoints.RepresentedPointInTime)”
}
}
else
{
if ($idiom -eq “english”) {
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
“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!`n” | out-file
c:\DPM.txt -encoding ASCII -append
}
else {
Write-Host “Um recovery point para o banco de dados: $databaseName no servidor: $serverName não foi
encontrado no protection group $sqlProtectionGroupName dentro do período: $startDate até $endDate. O Restore não pode ser
realizado!” -ForegroundColor Red
“Um recovery point para o banco de dados: $databaseName no servidor: $serverName não foi encontrado
no protection group $sqlProtectionGroupName dentro do período: $startDate até $endDate. O Restore não pode ser realizado!`n”
| out-file c:\DPM.txt -encoding ASCII -append
}

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)
{

if ($idiom -eq “english”) {
Write-Host “A recovery point for the database: $databaseName on server: $serverName was not found in the
protection group” $($sqlProtectionGroup.FriendlyName) -ForegroundColor Red
“A recovery point for the database: $databaseName on server: $serverName was not found in the protection
group`n” | out-file c:\DPM.txt -encoding ASCII -append
}
else {
Write-Host “Não foi encontrado um recovery point para o banco de dadosÇ $databaseName no servidor:
$serverName dentro do protection group $($sqlProtectionGroup.FriendlyName)” -ForegroundColor Red
“Não foi encontrado um recovery point para o banco de dadosÇ $databaseName no servidor: $serverName
dentro do protection group $($sqlProtectionGroup.FriendlyName)`n” | out-file c:\DPM.txt -encoding ASCII -append
}

return
}
else
{

if ($idiom -eq “english”) {
write-host “`nIt was possible to select a recovery point for the database: $databaseName on server:
$serverName in the protection group $($sqlProtectionGroup.FriendlyName)”
}
else {
write-host “`nFoi encontrado um recovery point para o banco de dados: $databaseName no servidor:
$serverName dentro do protection group $($sqlProtectionGroup.FriendlyName)”
}
}

$length = $recoveryPointToUse.PhysicalPath.Length; #Length = num files (eg: mdf and log = 2)

if ($idiom -eq “english”) {
Write-Host “`nThe number of files to recover on the database: $databaseName is $Length”
}
else {
Write-Host “`nO número de arquivos a recuperar do banco de dados: $databaseName é $Length”
}
#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
}
else
{
$alternateDatabaseDetails.LocationMapping[$i].DestinationLocation = $restorePathMdf
}
$i++;
}
$alternateDatabaseDetails.InstanceName = $restoreToSQLInstance;

#verify if the database should be restored with a different name
if ($NewdatabaseName -ne $null -and $NewdatabaseName -ne “”)
{

$alternateDatabaseDetails.DatabaseName = $NewdatabaseName

if($NewdatabaseName -ne “master” -and $NewdatabaseName -ne “model” -and $NewdatabaseName -ne “msdb” -and
$NewdatabaseName -ne “distribution”)
{
#drop the database if it already exists – else DPM fails on the restore
DropDatabase $restoreToSQLInstance $NewdatabaseName;
}
else
{

if ($idiom -eq “english”) {
Write-Host “`nCan’t drop system database $NewdatabaseName! Database won’t be restored!”
}
else {
Write-Host “`nImpossível remover banco de dados de sistema $NewdatabaseName! Banco de dados
não será restaurado!”
}

return;

}

if ($DropOriginalDatabase -eq $true -and $databaseName -ne $NewdatabaseName)
{

if($databaseName -ne “master” -and $databaseName -ne “model” -and $databaseName -ne “msdb” -and
$databaseName -ne “distribution”)
{
#drop the database if it already exists – else DPM fails on the restore
DropDatabase $restoreToSQLInstance $databaseName;
}
else
{

if ($idiom -eq “english”) {
Write-Host “`nCan’t drop original database $databaseName since it is a system
database! Proceeding to restore!”
}
else {
Write-Host “`nImpossível remover banco de dados original $databaseName já que ele é
um banco de dados de sistema! Continuando com o restore!”
}

}

}
}
else
{
$alternateDatabaseDetails.DatabaseName = $databaseName
if($databaseName -ne “master” -and $databaseName -ne “model” -and $databaseName -ne “msdb” -and $databaseName
-ne “distribution”)
{
#drop the database if it already exists – else DPM fails on the restore
DropDatabase $restoreToSQLInstance $databaseName;
}
else
{
if ($idiom -eq “english”) {
Write-Host “`nCan’t drop original database $databaseName since it is a system database!
Database won’t be restored!”
}
else {
Write-Host “`nImpossível remover banco de dados original $databaseName já que ele é um banco
de dados de sistema! Banco de dados não será restaurado!”
}

return;
}

}

#made changes to the end of this command, adding information about the library
#$recoveryOption = New-RecoveryOption -TargetServer $restoreToServerName -RecoveryLocation OriginalServerWithDBRename
-SQL -RecoveryType Recover -AlternateDatabaseDetails $alternateDatabaseDetails -DPMLibrary $LIB[0]
$recoveryOption = New-DPMRecoveryOption -TargetServer $restoreToServerName -RecoveryLocation
OriginalServerWithDBRename -SQL -RecoveryType Recover -AlternateDatabaseDetails $alternateDatabaseDetails -DPMLibrary $LIB[0]

$dbsize = ($recoveryPointToUse.Size / (1gb)).ToString(“.##”);

if ($idiom -eq “english”) {
Write-Host “restoring database: $($alternateDatabaseDetails.DatabaseName)`n”`
” with backup from $($recoveryPointToUse.RepresentedPointInTime)`n to SQL Server:
$($alternateDatabaseDetails.InstanceName)`n DB size: $dbsize GB …..`n”
}
else {
Write-Host “Restaurando o banco de dados: $($alternateDatabaseDetails.DatabaseName)`n”`
” com o backup de $($recoveryPointToUse.RepresentedPointInTime)`n para o SQL Server:
$($alternateDatabaseDetails.InstanceName)`n Tamanho do BD: $dbsize GB …..`n”
}
if ($doTheRestore)
{
$restoreJob = Recover-RecoverableItem -RecoverableItem $recoveryPointToUse -RecoveryOption $recoveryOption;

if ($idiom -eq “english”) {
Write-Host “Restore Status: $($restoreJob.Status)`n HasCompleted: $($restoreJob.HasCompleted)`n
Start: $($restoreJob.StartTime)”
}
else {
Write-Host “Status do Restore: $($restoreJob.Status)`n Concluído: $($restoreJob.HasCompleted)`n
Início: $($restoreJob.StartTime)”
}

$waitTime = 3; #initial wait time
while ($restoreJob -ne $null -and $restoreJob.HasCompleted -eq $false)
{
Write-Host “.” -NoNewline;
Start-Sleep -Seconds $waitTime;
$waitTime = 3;
}

Write-Host “”
if($restoreJob.Status -ne “Succeeded”)
{
if ($idiom -eq “english”) {
Write-Host “Restore Status: $($restoreJob.Status)`n Start: $($restoreJob.StartTime)`n End:
$($restoreJob.EndTime)” -ForeGroundColor Red
}
else {
Write-Host “Status do Restore: $($restoreJob.Status)`n Início: $($restoreJob.StartTime)`n Fim:
$($restoreJob.EndTime)” -ForeGroundColor Red
}
}
else
{
if ($idiom -eq “english”) {
Write-Host “Restore Status: $($restoreJob.Status)`n Start: $($restoreJob.StartTime)`n End:
$($restoreJob.EndTime)” -ForeGroundColor DarkGreen
}
else {
Write-Host “Status do Restore: $($restoreJob.Status)`n início: $($restoreJob.StartTime)`n Fim:
$($restoreJob.EndTime)” -ForeGroundColor DarkGreen
}
}

if ($idiom -eq “english”) {
“Restore Status for database $databaseName on $serverName was: $($restoreJob.Status)`n” | out-file
c:\DPM.txt -encoding ASCII -append
}
else {
“O restore do banco de dados $databaseName no servidor $serverName foi concluido com status:
$($restoreJob.Status)`n” | out-file c:\DPM.txt -encoding ASCII -append
}

$td = (New-Timespan -Start $restoreJob.StartTime -end $restoreJob.EndTime)

if ($idiom -eq “english”) {
Write-Host “Elapsed time: Hours: $($td.Hours) Minutes:$($td.Minutes) Seconds:$($td.Seconds) MSecs:
$($td.Milliseconds)”
}
else {
Write-Host “Duração do Restore: Horas: $($td.Hours) Minutos:$($td.Minutes) Segundos:$($td.Seconds)
MSecs:$($td.Milliseconds)”
}

}
else
{

if ($idiom -eq “english”) {
Write-Host “DoTheRestore is set to false – restore is not being performed!” -BackgroundColor Red
}
else {
Write-Host “DoTheRestore está configurado como false – restore não está sendo executado!” –
BackgroundColor Red
}
}
}
else
{

if ($idiom -eq “english”) {
Write-Host “Database $databaseName on $serverName was not found” -ForeGroundColor Red
“Database $databaseName on $serverName was not found`n” | out-file c:\DPM.txt -encoding ASCII -append
}
else {
Write-Host “O Banco de dados $databaseName não foi encontrado no servidor $serverName” -ForeGroundColor Red
“O Banco de dados $databaseName não foi encontrado no servidor $serverName`n” | out-file c:\DPM.txt -encoding
ASCII -append
}
}

}
function DropDatabase([string] $restoreToServerName, [string] $databaseName)
{

if ($idiom -eq “english”) {
Write-Host “`nChecking if database $databaseName needs to be dropped”
}
else {
Write-Host “`nVerificando se o banco de dados $databaseName precisa ser removido”
}

[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)
{
if ($idiom -eq “english”) {
Write-Host “Dropping database $databaseName on server $restoreToServerName”
}
else {
Write-Host “Removendo o banco de dados $databaseName no servidor $restoreToServerName”
}

$sqlServerSmo.KillAllProcesses($databaseName)
$sqlServerSmo.databases[$databaseName].drop()

if ($idiom -eq “english”) {
Write-Host “Database $databaseName on server $restoreToServerName has been dropped`n”
}
else {
Write-Host “O Banco de dados $databaseName foi removido do servidor $restoreToServerName`n”
}
}
else
{
if ($idiom -eq “english”) {
Write-Host “Database $databaseName does not exist on server $restoreToServerName`n”
}
else {
Write-Host “Banco de dados $databaseName não existe no servidor $restoreToServerName`n”
}
}
}

function RepointDpmAgent([string] $dpmServerName, [string] $dpmClient)
{
#add this to avoid manual entry of password
$Username = ‘YOUR_USERNAME’
$Password = ‘YOUR_PASSWORD’
$pass = ConvertTo-SecureString -AsPlainText $Password -Force
$Cred = New-Object System.Management.Automation.PSCredential -ArgumentList $Username,$pass

if ($idiom -eq “english”) {
Write-Host “Setting the DPMServer for $dpmClient to $dpmServerName”
}
else {
Write-Host “Configurando o servidor $dpmServerName como DPMServer para $dpmClient”
}

Invoke-Command -ComputerName $dpmClient -ArgumentList $dpmServerName -authentication credssp -Credential $Cred –
ScriptBlock {param($serverName) $cmd = “C:\Program Files\Microsoft Data Protection Manager\DPM\bin\SetDpmServer.exe”; & $cmd
-dpmServerName $serverName;}
}
function sendMail([string] $subject, [string] $idiom)
{

if ($idiom -eq “english”) {
Write-Host “Sending Email”
}
else {
Write-Host “Enviando Email”
}

#SMTP server name
$smtpServer = “YOUR_SMTP_SERVER”

#Creating a Mail object
$msg = new-object Net.Mail.MailMessage

#Creating SMTP server object
$smtp = new-object Net.Mail.SmtpClient($smtpServer)

#Email structure
$msg.From = “FROM_EMAIL”
$msg.ReplyTo = “REPLY_tO_EMAIL”
$msg.To.Add(“TO_EMAIL”)
$msg.subject = “$Subject”
$msg.body = (Get-Content C:\dpm.txt | out-string)
#Sending email
$smtp.Send($msg)
}

####Explicando os parâmetros (PT-BR)

#dpmServerName – nome do servidor DPM que está gerenciando os backups
#sqlProtectionGroupName – Nome do protection group que possui os backups
#serverName – nome do servidor\instância original SQL Server, onde os backups foram executados
#databaseName – nome do banco de dados que será restaurado
#restoreToServerName – nome do servidor que hospeda a instância SQL Server onde os backups serao restaurados
#restorePathMdf – caminho dos arquivos MDF/NDF no servidor destino
#restorePathLog – caminho dos arquivos LDF no servidor destino
#doTheRestore = – flag que define se o restore será realmente executado ou se será executada apenas uma simulação. É opcional
e por padrão é falso
#dateOfRestoreToUse – data do backup que desejamos restaurar – É opcional e por padrão será utilizado o backup mais recente
#dpmServerUsedToProtectClient – servidor DPM utilizado para proteger o cliente – É opcional e por padrão será utilizado o
mesmo servidor DPM dos backups
#restoreToSQLInstance – instância SQL Server onde serão restaurados os backups
#NewdatabaseName – Novo nome do banco de dados que será utilizado no restore – É opcional, por padrão é utilizado o nome
original do banco de dados
#DropOriginalDatabase – flag que define se o banco de dados original será removido ou não – É opcional e por padrão FALSE
#Source – define se a rotina deve procurar por backups em disco (Disk), fita (Media) ou ambos
#allDatabases – flag que define se devem ser restaurados TODOS os bancos de dados do protection group ou não – É opcional e
por padrão FALSE
#SystemDatabases – flag que define se os bancos de dados de sistema, caso hajam no protection group, devem ser restaurados ou
não – É opcional e por padrão FALSE
#idiom – idioma no qual serão exibidas as mensagens (english ou null (português) ) – É opcional, por padrão exibe as
mensagens em inglês
####Explaining parameters (EN-US)

#dpmServerName – name of the DPM Server that is managing the backups
#sqlProtectionGroupName – Name of the protection group where the backup is at
#serverName – Original SQL Server server\instance where backups were executed
#databaseName – name of the database that is going to be restored
#restoreToServerName – name of the server that hosts the SQL Server instance where backups will be restored
#restorePathMdf – Path where MDF/NDF files will be restored at on destination server
#restorePathLog – Path where LDF files will be restored at on destination server
#doTheRestore = – flag that defines if restore is going to be executed or if it is going to simulate only. Optional, false is
default
#dateOfRestoreToUse – date of the backup that we wish to restore – Optional, default is to restore the most recent backup
#dpmServerUsedToProtectClient – DPM server used to protect the client server where backups will be restored – Optional,
default is to use the same server that protects the server
#restoreToSQLInstance – SQL Server instance where backups are going to be restored to
#NewdatabaseName – New database name that will be used on restore – Optional, default is to use original database name
#DropOriginalDatabase – flag that defines if original database will be removed or not – Optional, default is FALSE
#Source – Defines if the script should look for Disk or Media (Tape) backups or both
#allDatabases – flag that defines if ALL databases should be restored – Optional, default is FALSE
#SystemDatabases – flag that defines if system databases, in case they exist in protection group, should be restored –
Optional, default is FALSE
#idiom – Idiom on which messages will be desplayed (english or null (brazilian portuguese) ) – Optional, default is english

####calling the function

cls;

RestoreDbFromDPM -dpmServerName “DPM_SERVER” -sqlProtectionGroupName “DPM_PROTECTION_GROUP” -serverName
“SQL_SERVER_PRODUCTION_SERVER” -restoreToServerName “SQL_SERVER_TEST_SERVER” -restorePathMdf “C:\” -restorePathLog “C:\” –
dateOfRestoreToUse $null -dpmServerUsedToProtectClient “” -doTheRestore $true -restoreToSQLInstance “SERVER\TEST_INSTANCE” –
DropOriginalDatabase $true -Source “Disk,Media” -allDatabases $true -SystemDatabases $true -idiom “portugues” #-
Newdatabasename “NEW_NAME” -databaseName “DATABASE_NAME”

Esse post foi publicado em Artigos, backup, Powershell, System Center. Bookmark o link permanente.

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s