A couple of roadblocks we are currently experiencing:
* Source server isn't always selecting server that has the most recent backups ( DC Move, referencing old system not new )
* VeeamAgent Process is not closing out on MediaAgent currently from this powershell script.
Code: Select all
#parameters:
#
param (
[string]$sourceVM =""
, [string]$sourceDB =""
, [string]$targetVM =""
, [string]$targetInstance =""
, [string]$targetDB =""
, [string]$dataPath =""
, [string]$logPath =""
)
Write-Host "`n sourceVM = $sourceVM"
Write-Host "`n sourceDB = $sourceDB"
Write-Host "`n targetVM = $targetVM"
Write-Host "`n targetInstance = $targetInstance"
Write-Host "`n targetDB = $targetDB"
Write-Host "`n dataPath = $dataPath"
Write-Host "`n logPath = $logPath"
#*****CREATES LOG FILE WITH CURRENT DATE*****
#$DistantLogPathAsLocal = "log-{0}-{1}.txt" -f $sourceDB,(Get-Date -Format yyyMMdd);
$CurrentDate=Get-Date -Format "MM_dd_yyyy_HHmm"
$DistantLogPathAsLocal = "$sourceVM-$sourceDB-$targetInstance-targetDB-log-{0}-{1}.txt" -f $sourceDB2,$CurrentDate;
#Stop-Transcript
$LogFilePath="D:\VEEAM\JobLogs\$DistantLogPathAsLocal"
$ErrorActionPreference="SilentlyContinue"
[string]$global:sourceVM2 =$sourceVM
[string]$global:sourceDB2 =$sourceDB
[string]$global:targetVM2 =$targetVM
[string]$global:targetInstance2 =$targetInstance
[string]$global:targetDB2 =$targetDB
[string]$global:dataPath2 =$dataPath
[string]$global:logPath2 =$logPath
[int]$global:restoreStatus=0
Write-HOST "Updating Metadata Table"
Write-Host sourceVM2 = $sourceVM2
Write-Host sourceDB2 = $sourceDB2
Write-Host targetVM2 = $targetVM2
Write-Host targetInstance2 = $targetInstance2
Write-Host targetDB2 = $targetDB2
Write-Host dataPath2 = $dataPath2
Write-Host logPath2 = $logPath2
[string]$global:sqlInstance="ADMIN server name"
[string]$global:sqlDatabase="DBCC_DB"
[string]$FromDBAMailAddress='from@domain.com'
[string[]]$ToDBAMailAddress="email address to report when there’s a faiure"
[string]$ExchgSrver='mailserver’
[string]$EmailSubject="Veeam DB Restore Job db-restore-veeam-params on $sqlInstance Failed"
[string]$EmailBody="Testing Restore PowerSell Scripts on Admin server"
[string]$global:sqlQuery01 = "
INSERT INTO [DBCC_DB].[dbo].[VeemDBRestoreHistory]
([restore_start_date]
,[sourceVM]
,[sourceDB]
,[targetVM]
,[targetInstance]
,[targetDB]
,[dataPath]
,[logPath]
,[restore_finish_date]
,[restore_status]
,[restore_error])
VALUES (
GetDate()
,'$sourceVM'
,'$sourceDB'
,'$targetVM'
,'$targetInstance'
, '$targetDB'
,'$dataPath'
,'$logPath'
,NULL
,0
,'')
;"
Write-Host $sqlQuery01
[string]$global:sqlQuery02= "SELECT [restore_ID] FROM [DBCC_DB].[dbo].[VeemDBRestoreHistory]
WHERE [sourceVM] = '$sourceVM'
AND [sourceDB] = '$sourceDB'
AND [targetVM] = '$targetVM'
AND [targetInstance] = '$targetInstance'
AND [targetDB] = '$targetDB'
AND [dataPath] = '$dataPath'
AND [logPath] = '$logPath';"
<#
[string]$global:sqlQuery02= "SELECT TOP 1 [restore_ID]
FROM [DBCC_DB].[dbo].[VeemDBRestoreHistory]
WHERE [sourceVM] = '$sourceVM'
AND [sourceDB] = '$sourceDB'
AND [targetVM] = '$targetVM'
AND [targetInstance] = '$targetInstance'
AND [targetDB] = '$targetDB'
AND [dataPath] = '$dataPath'
AND [logPath] = '$logPath'
ORDER BY [restore_ID] DESC;"
#>
Write-Host $sqlQuery02
<#
[string]$global:sqlQuery04 = "UPDATE [DBCC_DB].[dbo].[VeemDBRestoreHistory]
SET [restore_status] = 1 --'$restoreStatus'
WHERE [sourceVM] = '$sourceVM'
AND [sourceDB] ='$sourceDB'
AND [targetVM] = '$targetVM'
AND [targetDB] = '$targetDB'
AND [targetInstance] = '$targetInstance'
AND [dataPath] = '$dataPath'
AND [logPath] = '$logPath'
;"
#>
[string]$global:sqlQuery04 = "UPDATE [DBCC_DB].[dbo].[VeemDBRestoreHistory]
SET [restore_status] = 1 --'$restoreStatus'
WHERE [restore_ID] = '$restoreID'
;"
Write-Host $sqlQuery04
try{
Start-Transcript -path $LogFilePath -ErrorAction Stop
}
catch{ Start-Transcript -path $LogFilePath
}
Write-Host "Metadata SQL Server $sqlInstance"
try{
Write-Host "My Update query $sqlQuery01"
Write-Host "Invoke-SQLCMD To INSERT New METADATA ROW DB $sqlInstance"
Invoke-Sqlcmd -ServerInstance $sqlInstance -Database "DBCC_DB" -Query $sqlQuery01 -ConnectionTimeout 5 -QueryTimeOut 600 -Verbose
Write-Host "Invoke-SQLCMD To INSERT New METADATA ROW IS COMPLETE."
##### Query Metadata table to get the restore ID ##########
Write-Host "Query Metadata table to get restore ID - DB $sqlInstance"
Write-Host "Select Query $sqlQuery02"
Write-Host "Invoke-SQLCMD To SELECT METADATA ROW"
$Tables=Invoke-Sqlcmd -ServerInstance $sqlInstance -Database "DBCC_DB" -Query $sqlQuery02 -ConnectionTimeout 5 -QueryTimeOut 600 -Verbose
Write-Host $Table.Rows
Write-Host "Query Metadata table to get the restore ID Metadata DB $sqlInstance is complete."
}catch{ [Exception]
Write-Host $_.Exception.Message
Write-Host "Invoke-Sqlcmd SQL Query Metadata table failed...."
$EmailBody= "Invoke-Sqlcmd SQL Query Metadata table failed....: $_.Exception.Message "
Stop-Transcript;
Send-MailMessage -From $FromDBAMailAddress -To $ToDBAMailAddress -Subject $EmailSubject -Body $EmailBody -Attachments $LogFilePath -SmtpServer $ExchgSrver
#break
}
Try{
#Start the connection
$ServerName = "management server";
$username = "Account having full access to management server ";
$psCredential = new-object -typename System.Management.Automation.PSCredential -argumentlist ($username, $secstr)
Write-Host "Connecting to server $ServerName..."
#Import-Module -Name Veeam.Backup.PowerShell -WarningAction:SilentlyContinue
try {
Write-Host "Connecting to Veeam Backup Server : $ServerName, please wait..."
( ( Get-VBRServer -name $ServerName -ErrorAction SilentlyContinue ) -eq $null ) *> $null
Write-Host "Connection to $ServerName is established."
}
catch { [Exception]
Write-Host $_.Exception.Message
(Connect-VBRServer -server $ServerName -Credential $psCredential)
$EmailBody= "Failed to connect to $ServerName ....: $_.Exception.Message "
Stop-Transcript;
Send-MailMessage -From $FromDBAMailAddress -To $ToDBAMailAddress -Subject $EmailSubject -Attachments $LogFilePath -Body $EmailBody -SmtpServer $ExchgSrver
#break
}
Write-Host "Creating new Session Object"
$s = New-PSSession -Computername $ServerName -Credential $psCredential ;
Write-Host "Openning Session $s to Server $ServerName"
Invoke-Command -Session $s -ScriptBlock {
Write-Host "SourceVM inside script block:" $args[0];
Write-Host "SourceDB inside script block:" $args[1];
Write-Host "TargetVM inside script block:" $args[2];
Write-Host "TargetDB inside script block:" $args[3];
Write-Host "TargetInstance inside script block:" $args[4];
[String]$SourceVM3=$args[0]
[String]$SourceDB3=$args[1]
[String]$TargetVM3=$args[2]
[String]$TargetDB3=$args[3]
[String]$TargetInstance3=$args[4]
#Import-Module -Name Veeam.Backup.PowerShell -WarningAction:SilentlyContinue
try{
Write-Host "Getting restore point..."
$restorepoint = Get-VBRApplicationRestorePoint -SQL -Name $args[0];
Write-Host "Restore Point is $restorepoint"
Write-Host "Restore $restorepoint point is Found..."
}catch{
Write-Host $_.Exception.Message
Write-Host "Restore restore point does not exist "
$EmailBody= "Restore restore point does not exist : $_.Exception.Message "
Stop-Transcript;
Send-MailMessage -From $FromDBAMailAddress -To $ToDBAMailAddress -Subject $EmailSubject -Attachments $LogFilePath -Body $EmailBody -SmtpServer $ExchgSrver
#break
}
try{
Write-Host "Getting session from latest restore point..."
Start-VESQLRestoreSession -RestorePoint $restorepoint[-1];
$session = Get-VESQLRestoreSession;
Write-Host "Session is $session"
}catch{[Exception]
Write-Host $_.Exception.Message
Write-Host "Restore session did not start not "
$EmailBody= "Restore session did not start not : $_.Exception.Message "
Stop-Transcript;
Send-MailMessage -From $FromDBAMailAddress -To $ToDBAMailAddress -Subject $EmailSubject -Attachments $LogFilePath -Body $EmailBody -SmtpServer $ExchgSrver
#break
}
try{
Write-Host "Getting database..."
$database = Get-VESQLDatabase -Session $session[0] -Name $args[1];
Write-Host "Database: " $database;
}catch{[Exception]
Write-Host $_.Exception.Message
Write-Host "Could not find database"
$EmailBody= "Could not find database : $_.Exception.Message "
Stop-Transcript;
Send-MailMessage -From $FromDBAMailAddress -To $ToDBAMailAddress -Subject $EmailSubject -Body $EmailBody -SmtpServer $ExchgSrver
#break
}
try{
$files = Get-VESQLDatabaseFile -Database $database;
Write-Host "Number of database files: " $files.Length;
}catch{ [Exception]
Write-Host $_.Exception.Message
Write-Host "Could not get the database files"
$EmailBody= "Could not get the database files"
Stop-Transcript;
Send-MailMessage -From $FromDBAMailAddress -To $ToDBAMailAddress -Subject $EmailSubject -Attachments $LogFilePath -Body $EmailBody -SmtpServer $ExchgSrver
#break
}
$targetPath = New-Object string[] $files.Length;
Write-Host "Target Path pre: " $targetPath;
for ( $i = 0; $i -lt $files.Length; $i++ )
{
Write-Host "Loop iteration: " $i;
$files[$i].Path;
$sourceFile = $files[$i].Path.Split("\")[($files[$i].Path.Length - $files[$i].Path.Replace("\","").Length)];
Write-Host "`n Source File is $sourceFile"
#if ( $i -lt ($files.Length - 1) ) { $targetPath[$i] = $args[6] + $sourceFile }
#else { $targetPath[$i] = $args[7] + $sourceFile }
if ( $sourceFile.Substring($sourceFile.Length - 4) -ne ".ldf" ) { $targetPath[$i] = $args[6] + $sourceFile }
else { $targetPath[$i] = $args[7] + $sourceFile }
}
Write-Host "Target Path post: " $targetPath;
Write-Host "Number of database files on target: " $targetPath.Length;
#Performs restore to another server/instance of SQL
Write-Host "Ready to restore database..."
Write-Host "`n $database , `n $args[3] , `n $args[2] ,`n $args[4] ,`n $args[5] ,`n $args[5] ,`n $files , `n $targetPath"
Restore-VESQLDatabase -Database $database -DatabaseName $args[3] -ServerName $args[2] -InstanceName $args[4] -GuestCredentials $args[5] -SqlCredentials $args[5] -File $files -TargetPath $targetPath -Force;
if(!$?)
{
Write-Host "Restore Operation Failed."
$restoreStatus=0
Write-Host "restoreStatus is = $restoreStatus"
$ErrorMessage = $_.Exception.Message
$FailedItem = $_.Exception.ItemName
$StackTrace = $_.Exception.StackTrace
Write-Host "Database Restore Failedd, check Log file"
$EmailBody= "Database Restore Failed, check Log file"
Stop-Transcript;
Send-MailMessage -From $FromDBAMailAddress -To $ToDBAMailAddress -Subject $EmailSubject -Attachments $LogFilePath -Body $EmailBody -Attachments D:\VEEAM\jobLogs\$LogFilePath -SmtpServer $ExchgSrver
}
#}catch{ [Exception]
Write-Host "Stopping session..."
Stop-VESQLRestoreSession -Session $session[0];
Write-Host $session;
} -ArgumentList $sourceVM, $sourceDB, $targetVM, $targetDB, $targetInstance, $psCredential, $dataPath, $logPath
Remove-PSSession $s;
Write-Host "Metadata SQL Instance $sqlInstance4"
Write-Host "Metadata SQL DB $sqlDatabase4"
Write-Host "Metadata SQL Update Query : `n $sqlQuery04"
Write-Host "Update query $sqlQuery04 is complete."
Write-Host "restoreStatus is = $restoreStatus"
Write-Host "Invoke-SQLCMD To Update New METADATA ROW DB $sqlInstance"
Invoke-Sqlcmd -ServerInstance $sqlInstance -Database "DBCC_DB" -Query $sqlQuery04 -ConnectionTimeout 5 -QueryTimeOut 600 -Verbose
Write-Host "Invoke-SQLCMD To Update New METADATA ROW IS COMPLETE."
}
Catch {
$ErrorMessage = $_.Exception.Message
$FailedItem = $_.Exception.ItemName
$StackTrace = $_.Exception.StackTrace
$EmailBody= "An error occurred that could not be resolved: `nError message: $ErrorMessage`nFailed item: $StackTrace`nStack trace: $StackTrace : _.Exception.Message"
Stop-Transcript;
Send-MailMessage -From $FromDBAMailAddress -To $ToDBAMailAddress -Subject $EmailSubject -Attachments $LogFilePath -Body $EmailBody -SmtpServer $ExchgSrver
#Break
}
Finally {
#Stop the connection
"Disconnect-VBRServer..."
Disconnect-VBRServer;
}
## Run DBCC CHECKDB COMMAND
<#
#function runDBCCcheckCB($UserDB,$LofFileName, $sqlInstance)
#End logs
Stop-Transcript;
#$EmailBody= "Database Restore : `nError message: $ErrorMessage`nFailed item: $StackTrace`nStack trace: $StackTrace : _.Exception.Message"
if($?)
{
Write-Host "Database Restore Job Complete.. Sending Email message"
Send-MailMessage -From $FromDBAMailAddress -To $ToDBAMailAddress -Subject $EmailSubject -Attachments $LogFilePath -Body $EmailBody -SmtpServer $ExchgSrver
Write-Host "Email is sent"
}
#Ends script
#"End script restore-proper"
Exit