Some time ago I have posted here a powershell script that I have written to automate restores from SQL Server backups made with System Center Data Protection Manager.
As the time passed I had the need for new functionalities and the script has been changed/incremented.
Here are the new features of the script:
- #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
And here is the new version of the script:
–Updated on 25/04/2013, minor issue corrected in the code
#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;
Vladimir M. B. Magalhães - Learn and Share 