Restaurando backups do SQL Server no System Center Data Protection Manager automaticamente com Powershell – Nova versão

Faz algum tempo postei aqui no blog um script que elaborei para automatizar restores de backups do SQL Server feitos com o System Center Data Protection Manager de forma automática utilizando Powershell.

A medida que o tempo foi passando fui sentindo necessidade de novas funcionalidades e o script foi sendo melhorado/incrementado.

Abaixo explico as novas funcionalidades do script:

  • #mail_subject – Mensagem que será utilizada no assunto do e-mail enviado ao final da execução do script. – É opcional, caso não seja definida será utilizada a mensagem padrão.
  • #DBnotToRestore – Banco de dados que será ignorado (não será restaurado)
  • #LogFileName – Nome e caminho do arquivo de log que será gerado. Caso não seja informado será utilizado o nome padrão C:\DPM.TXT
  • #DBtoStartAT – Nome do primeiro banco de dados a ser restaurado. Qualquer outro banco de dados cujo nome for menor que o informado nessa variável não será restaurado.
  • #DBtoFinishAT – Nome do último banco de dados a ser restaurado. Qualquer outro banco de dados cujo nome for maior que o informado nessa variável não será restaurado.
  • #ResetLogFile – Define se o arquivo de log deverá ser “resetado” ou não, sendo útil principalmente quando utilizamos as opções DBtoStartAT e DBtoFinishAT para realizar uma operação de Restore em momentos diferentes, mas que precisam do mesmo log.
  • #Inactive – Informa que os backups a serem restaurados estao em um Protection Group inativo, sem proteção, mas cujos arquivos cotinuam disponíveis. Caso ativado, o parametro sqlProtectionGroupName sera ignorado

E aqui está a nova versão do script:

–Pequena atualização no script feita no dia 25/04/2013


#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
#Account that is going to run the script/restore must be administrator on the server where the restore happens

#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",
 [string] $mail_subject = $null,
 [string] $DBnotToRestore,
 [string] $LogFileName = "C:\DPM.txt",
 [string] $DBtoStartAT = $null,
 [string] $DBtoFinishAT = "ZZZZZZZZZ", #doing this so when the parameter is not informed it will list ALL databases
 [bool] $ResetLogFile = $true,
 [bool] $Inactive = $false
 )

$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
 if ($ResetLogFile -eq $true) {
 out-file $LogFileName -encoding ASCII

if ($idiom -eq "english") {
 "Database,Source Server,Destination Server,Backup Date/Time,Status" | out-file $LogFileName -encoding ASCII -append
 }
 else {
 "Banco,Servidor de Origem,Servidor de Destino,Data/Hora do Backup,Status" | out-file $LogFileName -encoding ASCII -append
 }
 }
 if($Inactive -eq $false) #if restoring from an inactive Data Source, there is no Protection Group information, the PG is NULL
 {
 $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";
 "$null,$ServerName,$restoreToSQLInstance,$null,The protection group $sqlProtectionGroupName was not found on the server $dpmServerName" | out-file $LogFileName -encoding ASCII -append
 }
 else {
 Write-Host "`nO protection group $sqlProtectionGroupName nao foi encontrado no servidor $dpmServerName";
 "$null,$ServerName,$restoreToSQLInstance,$null,O protection group $sqlProtectionGroupName nao foi encontrado no servidor $dpmServerName" | out-file $LogFileName -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
 if($Inactive -eq $false) {
 $databaseList = Get-DataSource -ProtectionGroup $sqlProtectionGroup | foreach {$_.name} | sort-object | get-unique
 }
 else
 {
 $databaseListTemp = Get-DataSource -DPMServerName $dpmServerName -Inactive #gets all inactive data sources
$databaseList = @()

 foreach($databaseInstanceTemp in $databaseListTemp) {
 if($databaseInstanceTemp.Instance -eq $serverName) { #filter data sources from desired instance
 $databaseList += $databaseInstanceTemp.Name
 }
 }
 }

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" -and $databaseInstance -ne "tempdb")
 {
 if (($databaseInstance -ge $DBtoStartAT) -and ($databaseInstance -le $DBtoFinishAT)) {
 if ($DBnotToRestore -ne $databaseInstance) {
 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 -Inactive $Inactive
 }
 else {
 if ($idiom -eq "english") {
 write-host "`nSkipping database $databaseInstance since it was informed on the '-DBnotToRestore' variable`n"
 "$databaseInstance,$serverName,$restoreToSQLInstance,$null,Ignored" | out-file $LogFileName -encoding ASCII -append
 }
 else {
 write-host "`nIgnorando o banco de dados $databaseInstance já que este foi informado na variável '-DBnotToRestore'`n"
 "$databaseInstance,$serverName,$restoreToSQLInstance,$null,Ignored" | out-file $LogFileName -encoding ASCII -append
 }
 }
 }
 else {
 if ($idiom -eq "english") {
 write-host "`nIgnoring database $databaseInstance since its name is not in the range between '-DBToStartAT' and '-DBtoFinishAT'`n"
 }
 else {
 write-host "`nIgnorando o banco de dados $databaseInstance já que seu nome não está entre os valores de '-DBToStartAT' e '-DBtoFinishAT'`n"
 }
 }
 }
 else
 {
 if ($DBnotToRestore -ne $databaseInstance) {
 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 ($idiom -eq "english") {
 write-host "`nSkipping database $databaseInstance since it was informed on the '-DBnotToRestore' variable`n"
 "$databaseInstance,$serverName,$restoreToSQLInstance,$null,Ignored" | out-file $LogFileName -encoding ASCII -append
 }
 else {
 write-host "`nIgnorando o banco de dados $databaseInstance já que este foi informado na variável '-DBnotToRestore'`n"
 "$databaseInstance,$serverName,$restoreToSQLInstance,$null,Ignored" | out-file $LogFileName -encoding ASCII -append
 }
 }
 }
 }
 else
 {
 if($databaseInstance -eq "master" -or $databaseInstance -eq "model" -or $databaseInstance -eq "msdb" -or $databaseInstance -eq "distribution" -or $databaseInstance -eq "tempdb") {
 $NewdatabaseName = $databaseInstance + "_temp"
 }
 else {
 $NewdatabaseName = $databaseInstance
 }

 if (($databaseInstance -ge $DBtoStartAT) -and ($databaseInstance -le $DBtoFinishAT)) {
 if ($DBnotToRestore -ne $databaseInstance) {
 if ($idiom -eq "english") {
 write-host "`nBeginning the process of restoring the database $databaseInstance as $NewdatabaseName`n"
 }
 else {
 write-host "`nIniciando o processo de restauração do banco de dados $databaseInstance como $NewdatabaseName`n"
 }

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

 }
 else {
 if ($idiom -eq "english") {
 write-host "`nSkipping database $databaseInstance since it was informed on the '-DBnotToRestore' variable`n"
 "$databaseInstance,$serverName,$restoreToSQLInstance,$null,Ignored" | out-file $LogFileName -encoding ASCII -append
 }
 else {
 write-host "`nIgnorando o banco de dados $databaseInstance já que este foi informado na variável '-DBnotToRestore'`n"
 "$databaseInstance,$serverName,$restoreToSQLInstance,$null,Ignored" | out-file $LogFileName -encoding ASCII -append
 }
 }
 }
 else {
 if ($idiom -eq "english") {
 write-host "`nIgnoring database $databaseInstance since its name is not in the range between '-DBToStartAT' and '-DBtoFinishAT'`n"
 }
 else {
 write-host "`nIgnorando o banco de dados $databaseInstance já que seu nome não está entre os valores de '-DBToStartAT' e '-DBtoFinishAT'`n"
 }

#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 ($DBnotToRestore -ne $databaseName) {
 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 -Inactive $Inactive

 }
 else {
 if ($idiom -eq "english") {
 write-host "`nSkipping database $databaseName since it was informed on the '-DBnotToRestore' variable`n"
 "$databaseName,$serverName,$restoreToSQLInstance,$null,Ignored" | out-file $LogFileName -encoding ASCII -append
 }
 else {
 write-host "`nIgnorando o banco de dados $databaseName já que este foi informado na variável '-DBnotToRestore'`n"
 "$databaseName,$serverName,$restoreToSQLInstance,$null,Ignored" | out-file $LogFileName -encoding ASCII -append
 }
 }
 }

if (($idiom -eq "english") -and ($mail_subject -eq $null)){
 Sendmail -subject "Restore of databases on server $Servername" -idiom $idiom
 }
 else {
 if (($idiom -ne "english") -and ($mail_subject -eq $null)){
 Sendmail -subject "Restore de bancos de dados no servidor $Servername" -idiom $idiom
 }
 else {
 Sendmail -subject $mail_subject -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,
 [bool] $Inactive = $false
 )

#find the data-source for the database-name on the server requested
 if($Inactive -eq $false) {
 $sqlDataSource = Get-DataSource -ProtectionGroup $sqlProtectionGroup | where-object { $_.name -eq $databaseName -and $_.Instance -eq $serverName}
 }
 else
 {
 $sqlDataSourceTemp = Get-DataSource -DPMServerName $dpmServerName -Inactive

foreach($databaseInstanceTemp in $sqlDataSourceTemp) {
 if($databaseInstanceTemp.Instance -eq $serverName -and $databaseInstanceTemp.Name -eq $databaseName) {
 $sqlDataSource = $databaseInstanceTemp
 }
 }
 }

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
 "$databaseName,$serverName,$restoreToSQLInstance,$null,A recovery point was not found in the protection group $($sqlProtectionGroup.FriendlyName)" | out-file $LogFileName -encoding ASCII -append
 }
 else {
 Write-Host "nao foi encontrado um recovery point para o banco de dados: $databaseName no servidor: $serverName no protection group $($sqlProtectionGroup.FriendlyName)`n" -ForegroundColor Red
 "$databaseName,$serverName,$restoreToSQLInstance,$null,nao foi encontrado um recovery point no protection group $($sqlProtectionGroup.FriendlyName)" | out-file $LogFileName -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 no 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
 "$databaseName,$serverName,$restoreToSQLInstance,$null,A recovery point was not found in the protection group $sqlProtectionGroupName within the daterange: $startDate to $endDate." | out-file $LogFileName -encoding ASCII -append
 }
 else {

[string] $Data_Inicio = $StartDate.ToString("dd/MM/yyyy hh:mm:ss")
 [string] $Data_Fim = $EndDate.ToString("dd/MM/yyyy hh:mm:ss")

Write-Host "Um recovery point para o banco de dados: $databaseName no servidor: $serverName nao foi encontrado no protection group $sqlProtectionGroupName dentro do periodo: $Data_Inicio ate $Data_Fim. O Restore nao pode ser realizado!" -ForegroundColor Red
 "$databaseName,$serverName,$restoreToSQLInstance,$null,Um recovery point nao foi encontrado no protection group $sqlProtectionGroupName dentro do periodo: $Data_Inicio ate $Data_Fim." | out-file $LogFileName -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
 "$databaseName,$serverName,$restoreToSQLInstance,$null,A recovery point was not found in the protection group $($sqlProtectionGroup.FriendlyName)" | out-file $LogFileName -encoding ASCII -append
 }
 else {
 Write-Host "nao foi encontrado um recovery point para o banco de dados $databaseName no servidor: $serverName no protection group $($sqlProtectionGroup.FriendlyName)" -ForegroundColor Red
 "$databaseName,$serverName,$restoreToSQLInstance,$null,nao foi encontrado um recovery point no protection group $($sqlProtectionGroup.FriendlyName)" | out-file $LogFileName -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 no 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" -and $NewdatabaseName -ne "tempdb") {
 #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 nao 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" -and $databaseName -ne "tempdb") {
 #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" -and $databaseName -ne "tempdb") {
 #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!"
 Write-Host "`nAn attempt to restore the system database $databaseName will be done, but it may fail if the database already exists!"
 }
 else {
 #Write-Host "`nImpossível remover banco de dados original $databaseName já que ele é um banco de dados de sistema! Banco de dados nao será restaurado!"
 Write-Host "`nUma tentativa de restore do banco de dados de sistema $databasename será feita, mas ela pode falhar caso o banco de dados já exista!"
 }
 }
 }

#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") {
 "$databaseName,$serverName,$restoreToSQLInstance,$($recoveryPointToUse.RepresentedPointInTime),$($restoreJob.Status)" | out-file $LogFileName -encoding ASCII -append
 }
 else {
 "$databaseName,$serverName,$restoreToSQLInstance,$($recoveryPointToUse.RepresentedPointInTime.ToString("dd/MM/yyyy hh:mm:ss")),$($restoreJob.Status)" | out-file $LogFileName -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 nao está sendo executado!" -BackgroundColor Red
 }
 }
 }
 else {
 if ($idiom -eq "english") {
 Write-Host "Database $databaseName on $serverName was not found" -ForeGroundColor Red
 "$databaseName,$serverName,$restoreToSQLInstance,$null,Database $databaseName on $serverName was not found" | out-file $LogFileName -encoding ASCII -append
 }
 else {
 Write-Host "O Banco de dados $databaseName nao foi encontrado no servidor $serverName" -ForeGroundColor Red
 "$databaseName,$serverName,$restoreToSQLInstance,$null,O Banco de dados $databaseName nao foi encontrado no servidor $serverName" | out-file $LogFileName -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 nao existe no servidor $restoreToServerName`n"
 }
 }
}

function RepointDpmAgent([string] $dpmServerName, [string] $dpmClient)
{
 #add this to avoid manual entry of password
 $Username = 'domain\user'
 $Password = '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"
 }

$a = "<style>"
 $a = $a + "BODY{background-color:white;}"
 $a = $a + "TABLE{border-width: 2px;border-style: solid;border-color: black;border-collapse: collapse;}"
 $a = $a + "TH{border-width: 2px;padding: 5px;border-style: solid;border-color: black;background-color:gray}"
 $a = $a + "TD{border-width: 2px;padding: 5px;border-style: solid;border-color: black;}"
 $a = $a + "</style>"

#SMTP server name
 $smtpServer = "smtp.domain.com"

#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 = "dpm@domain.com"
 $msg.ReplyTo = "your_email@domain.com"
 $msg.To.Add("your_email@domain.com")
 $msg.subject = "$Subject"
 $msg.isBodyHTML = $true;
 $msg.Body = (Import-csv $LogFileName | convertTo-HTML -head $a)

#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 nao - É 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 nao - É 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 nao - É 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
#mail_subject - Mensagem que será utilizada no assunto do e-mail enviado ao final da execução do script. - É opcional, caso não seja definida será utilizada a mensagem padrão.
#DBnotToRestore - Banco de dados que será ignorado (não será restaurado)
#LogFileName - Nome e caminho do arquivo de log que será gerado. Caso não seja informado será utilizado o nome padrão C:\DPM.TXT
#DBtoStartAT - Nome do primeiro banco de dados a ser restaurado. Qualquer outro banco de dados cujo nome for menor que o informado nessa variável não será restaurado.
#DBtoFinishAT - Nome do último banco de dados a ser restaurado. Qualquer outro banco de dados cujo nome for maior que o informado nessa variável não será restaurado.
#ResetLogFile - Define se o arquivo de log deverá ser "resetado" ou não, sendo útil principalmente quando utilizamos as opções DBtoStartAT e DBtoFinishAT para realizar uma operação de Restore em momentos diferentes, mas que precisam do mesmo log.
#Inactive - Informa que os backups a serem restaurados estao em um Protection Group inativo, sem proteção, mas cujos arquivos cotinuam disponíveis. Caso ativado, o parametro sqlProtectionGroupName sera ignorado

####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
#mail_subject - Message that will be used on the e-mail's subject after the script execution has finished. - Optional, default message will be used in this case.
#DBnotToRestore - Database that will be ignored (will not be restored)
#LogFileName - Name and path for the log file. If this is not informed the default value of C:\DPM.TXT wil be used
#DBtoStartAT - Name of the first database to be restored. Any other database whose name is smaller than the value of this variable will not be restored.
#DBtoFinishAT - Name of the last database to be restored. Any other database whose name is bigger than the value of this variable will not be restored.
#ResetLogFile - Defines if the log file will be reseted or not, being useful mainly when we use the options DBtoStartAT and DBtoFinishAT to execute a restore in different moments, but having the same log.
#Inactive - Used when restoring a backup from an inactive Protection Group, no longer protected, but with its files still available. When used, makes the option sqlProtectionGroupName to be ignored

####calling the function

#cls;
Esse post foi publicado em Artigos, Powershell, Virtual PASS BR. 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