I hope I can get some good replies and feedback on a current issue I am having when trying to spin up a virtual SureBackup lab and run a custom PowerShell script I wrote to execute on the VM in the virtual lab. Maybe someone has done this and I am going down the same path as them and I hope that's the case!
We all know how the SureBackup lab works, it spins up the lab environment, runs any scripts, tears down the lab and then reports whether or not it was successful. I am having an issue including my custom PowerShell Script to run inside the SureBkacup lab process without having to manually intervene. Currently, if I setup the SureBackup lab to run with the "SqlChecker" the jobs runs unattended and works perfectly. Once I introduce the custom PowerShell script, it fails to run the PowerShell script. If there is a way to get the PowerShell script to run successfully without having to manually intervene, it'd change the way maintenance can be done with SQL!
I have opened up a support ticket and they encouraged me to reach out the forums and contact this awesome community i've heard alot about!
Support ticket #s:
SureBackup - Custom Script Issues and Output - Case # 02461833 [ [ ref:_00D30RWR._5000e1HmBDr:re [ ref:_00D30RWR._5000e1HmBDr:ref ] and Veeam Support - Case # 02461833 [ ref:_00D30RWR._5000e1HmBDr:ref ]
Goal:
1. Spin up a SQL VM in a SureBackup Lab successfully *Completed*
2. Run the VEEAM "Sqlchecker" vbs script successfully remotely/unattended *Completed*
3. Run the custom powershell script inside the VM in the Surebackup lab remotely/unattended when the surebackup lab starts *Currently having issues!*
4. Once scripts from step 2 and 3 complete, report back script output
5. Tear down the SureBackup lab
Issues:
1. I am unable to "kickoff" or "execute" the custom PowerShell script stored on the lab VM when the Surebackup job runs. The Surebackup job runs successfully with the VEEAM "Sqlchecker" vbs script but never completes when I input the argument to run the custom PowerShell script.
1a. The PowerShell script i've created is currently on the VM in the SureBackup Lab when the restore point was created.
2a. The script works locally on any SQL server and runs CheckDB on all the SQL databases on the local server.
I've tried multiple arguments to try and kick off the PowerShell script but have yet to get it executing when the lab spins up. Below are some examples of the arguments i've given in the application group:
Ex1:
Name: CheckDB
Path: C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe
Arguments: Invoke-Command -Computername %vm_ip% -FilePath E:\AppAdmin\Alex\VEEAM-Database_Integrity_Check.ps1
Output:
Ex2: - This example i am trying to run the batch file to kick off the PowerShell script, below is the batch file code:2018 10:25:18] <01> Info [SureBackup] [omitvm] [ScriptTests] Begin 'Custom script 2: name CheckDB, starting...'
[15.01.2018 10:25:18] <01> Info [StdOutReader] Preparing
[15.01.2018 10:25:18] <01> Info [StdOutReader] Waiting for thread to start
[15.01.2018 10:25:18] <36> Info [StdOutReader] Reader thread started
[15.01.2018 10:25:18] <01> Info [StdOutReader] Preparing complete
[15.01.2018 10:25:18] <01> Info [CreateProcessWLogon] Starting CreateProcess thread. Timeout: 60 sec
[15.01.2018 10:25:18] <01> Info [CreateProcessWLogon] Building StartupInfo
[15.01.2018 10:25:18] <01> Info [CreateProcessWLogon] CreateProcess thread started. Waiting for complete
[15.01.2018 10:25:18] <37> Info [CreateProcessWLogon] Calling CreateProcessWithLogonW. User: *******. Domain: *******. CmdLine: C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe Invoke-Command -Computername 10.255.250.125 -FilePath E:\AppAdmin\Alex\VEEAM-Database_Integrity_Check.ps1. WorkingDir: E:\Program Files\Veeam\Backup and Replication\Backup
[15.01.2018 10:25:18] <37> Info [CreateProcessWLogon] CreateProcessWithLogonW call success
[15.01.2018 10:25:18] <01> Info [CreateProcessWLogon] CreateProcess thread completed
[15.01.2018 10:25:18] <36> Info [SureBackup] [omitvm] [ScriptTests] [Console] [10.255.250.125] Connecting to remote server 10.255.250.125 failed with the following error message : The WinRM client
[15.01.2018 10:25:18] <36> Info [SureBackup] [omitvm] [ScriptTests] [Console] cannot process the request. Default authentication may be used with an IP address under the following conditions: the
[15.01.2018 10:25:18] <36> Info [SureBackup] [omitvm] [ScriptTests] [Console] transport is HTTPS or the destination is in the TrustedHosts list, and explicit credentials are provided. Use
[15.01.2018 10:25:18] <36> Info [SureBackup] [omitvm] [ScriptTests] [Console] winrm.cmd to configure TrustedHosts. Note that computers in the TrustedHosts list might not be authenticated. For more
[15.01.2018 10:25:18] <36> Info [SureBackup] [omitvm] [ScriptTests] [Console] information on how to set TrustedHosts run the following command: winrm help config. For more information, see the
[15.01.2018 10:25:18] <36> Info [SureBackup] [omitvm] [ScriptTests] [Console] about_Remote_Troubleshooting Help topic.
[15.01.2018 10:25:18] <36> Info [SureBackup] [omitvm] [ScriptTests] [Console] + CategoryInfo : OpenError: (10.255.250.125:String) [], PSRemotingTransportException
[15.01.2018 10:25:18] <36> Info [SureBackup] [omitvm] [ScriptTests] [Console] + FullyQualifiedErrorId : CannotUseIPAddress,PSSessionStateBroken
[15.01.2018 10:25:18] <01> Info [StdOutReader] Disposing
[15.01.2018 10:25:18] <01> Info [StdOutReader] Disposing pipe handle
[15.01.2018 10:25:18] <01> Info [StdOutReader] Disposing pipe
[15.01.2018 10:25:18] <36> Info [StdOutReader] Reader thread terminated
[15.01.2018 10:25:18] <01> Info [StdOutReader] Stopping reader thread
[15.01.2018 10:25:18] <01> Info [StdOutReader] Disposed
[15.01.2018 10:25:18] <01> Error [SureBackup] [omitvm] [ScriptTests] End 'Custom script 2: name CheckDB, error code 1'
[15.01.2018 10:25:18] <01> Info [SureBackup] [omitvm] [ScriptTests] Dump collector
[15.01.2018 10:25:18] <01> Info [SureBackup] [omitvm] [ScriptTests] ========================================================
[15.01.2018 10:25:18] <01> Info [SureBackup] [omitvm] [ScriptTests] | # | Script Name | Exit Code | State | Fail Reason |
[15.01.2018 10:25:18] <01> Info [SureBackup] [omitvm] [ScriptTests] ========================================================
[15.01.2018 10:25:18] <01> Info [SureBackup] [omitvm] [ScriptTests] | 1 | Sql Server | 0 | Passed | |
[15.01.2018 10:25:18] <01> Info [SureBackup] [omitvm] [ScriptTests] | 2 | CheckDB | 1 | Failed | |
[15.01.2018 10:25:18] <01> Info [SureBackup] [omitvm] [ScriptTests] ========================================================
[15.01.2018 10:25:18] <01> Error [SureBackup] [omitvm] [ScriptTests] Results: 1/2 test(s) passed, 1 failed, 0 skipped
[15.01.2018 10:25:18] <01> Error [SureBackup] [omitvm] [ScriptTests] Summary: 50% total pass rate
[15.01.2018 10:25:18] <01> Error [SureBackup] [omitvm] [ScriptTests] End 'Running test scripts'
[15.01.2018 10:25:18] <01> Info Task state 'Started'
[15.01.2018 10:25:18] <01> Error Application group failed to start (System.Exception)
Code: Select all
REM Run-CheckDB_PS.bat
@ECHO OFF
powershell.exe -noninteractive -noprofile -command "& {E:\AppAdmin\Alex\VEEAM-Database_Integrity_Check.ps1 %1 %2 }"
EXIT /B %errorlevel%Path: E:\AppAdmin\Alex\Run-CheckDB_PS.bat
Arguments: %vm_ip%
Below is the PowerShell script code you may use for running CheckDB. I dont mind if you use it for other things rather than this issue i am troubleshooting
Code: Select all
<#
.SYNOPSIS
Welcome to the VEEAM Database Integrity Check Script! This script runs the CheckDB job thats referenced by VEEAM in the Database Integrity SureBackup job.
.DESCRIPTION
This script lives on <omit VM name> and is referenced by the VEEAM SureBackup job that executes Database Integrity checks on internal SQL servers. Once ran, this script
will find all SQL instances on the local VM and run CheckDb on each of them, reporting back whether or not the CheckDB finished successfully or failed.
File Name : VEEAM-Database_Integrity_Check.ps1
Author : Alex Trepes
Date: : 1/9/2018
Prerequisite : PowerShell V2 and above , SQL
Copyright 2018 - Alex Trepes
.LINK
Script located at:
<omit VM name>
.PARAMETER
.EXAMPLE
#>
# in case the need for email needs to be tested/used
<#
function sendMail
{
#SMTP server name
$smtpServer = "exchangevmname@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 = "emailID@domain.com"
$msg.ReplyTo = "emailID@domain.com"
#$msg.To.Add("emailID@domain.com")
#$msg.To.Add("emailID@domain.com")
$msg.To.Add("emailID@domain.com")
$msg.subject = "SQL Backup Integrity - Successful! $Date"
$msg.body = "$EmailBody"
$msg.IsBodyHTML = $true
#Sending email
$smtp.Send($msg)
#Add-content $Log "$Stamp ID: $id | Email Sent"
#Add-content $Log "$Stamp ID: $id | $msg"
}#>
# log for the SureBackup job are located at: C:\ProgramData\Veeam\Backup\SQL_Backup_Check
$Date = $null
#$Date = Get-Date
# set the variable(s) to null
$SQLServers = $null
$SQLServers2 = $null
# create an array to hold the SQL instances
$SQLServers2 = @()
# get the local VM name
Param(
[string]$SQLServers
)
#$SQLServers = $env:COMPUTERNAME
# create an array to hold the instance names
$NamedInstance = @()
# query each SQL server via the SQL Server list , the below servers not being included are because AppAdmin doesn't have access or it is a 2005 SQL Server
foreach ($ServerName in $SQLServers)
{
#Set values to null
$ServerSMO = $null
$Aliases = $null
$DNSAlias = $null
$ServerInstance = $null
$nInstances = $null
$nInstances = Get-Service -ComputerName $ServerName | Where-Object {$_.Name -like 'MSSQL$*' -and $_.Status -ne "Stopped"}
if($nInstances -ne $null)
{
foreach($nInstance in $nInstances)
{
$NamedInstance += $ServerName + "\" + $nInstance.Name.Split("$")[1]
}
}
}
$SQLServers2 += $SQLServers
$SQLServers2 += $NamedInstance
# find any SQL servers that don't have the DBCC CheckDB Maintenance plan/SQL agent job setup
foreach($SQLServer in $SQLServers2)
{
# output current SQL server for troubleshooting
$SQLServer
# set values to null
$ServerSMO = $null
$CheckDB = $null
# Using the assembly namespace, this line calls the Microsoft SQL Server Management Objects (SMO) namespace
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null
# Calling the server object within the SMO namespace, this loads an instance of SQL server (input by the user, hence the $SQLServer variable)
$ServerSMO = New-Object ("Microsoft.SqlServer.Management.Smo.Server") $SQLServer
# Check if SQL Agent Job exists
$CheckDB = $ServerSMO.JobServer.Jobs | ? {$_.Name -like "*CheckDB*" -or $_.Name -eq "CheckDB.DataIntegrity"}
# Check if the SQL server is online
$ServerPwr = $ServerSMO
# If the CheckDB job exists, run the below logic, else kill the script
if($CheckDB -ne $null)
{
# output to the console the CheckDB job exists
Write-Output "CheckDB job exists, running CheckDB job..."
# get the CheckDB SQL agent job in order to execute it
$CheckDBJob = Get-SqlAgentJob -ServerInstance $SQLServer -Name "CheckDB.DataIntegrity"
# execute the CheckDB job
$CheckDBJob.Start()
# Pause for 5 seconds to give the SQL API time to get the true status of the CheckDB job
Start-Sleep -Seconds 5
# re-populate the $CheckDBJobStatus value with the new job information
$CheckDBJobStatus = Get-SqlAgentJob -ServerInstance $SQLServer -Name "CheckDB.DataIntegrity"
# get the CheckDB SQL agent job's current executing status
$CheckDBJobStatus.CurrentRunStatus
# while the CheckDB job is currently executing, go into the do/while loop
do
{
# set the CheckDB status job variable to null to get a new value each time
$CheckDBJobStatus = $null
# output to the console the CheckDB job is running
Write-Output "CheckDB is running...."
# Get the current executing status of the CheckDB job. Once the status changes from "Executing", come out of the do/while loop
$CheckDBJobStatus = Get-SqlAgentJob -ServerInstance $SQLServer -Name "CheckDB.DataIntegrity"
}while($CheckDBJobStatus.CurrentRunStatus -eq "Executing")
# get the most recent change in the CheckDB job information
$CheckDBJob = Get-SqlAgentJob -ServerInstance $SQLServer -Name "CheckDB.DataIntegrity"
# Check to see the last run outcome of the CheckDB job and report back the outcome
if($CheckDBJob.LastRunOutcome -eq "Succeeded")
{
Write-Output "CheckDB finished successfully on $SqlServer!"
}
Elseif($CheckDBJob.LastRunOutcome -eq "Failed")
{
Write-Output "CheckDB failed on $SqlServer. Please troubleshoot the corrupted database(s)."
}
Else
{
Write-Output "CheckDB was cancelled on $SqlServer. Please troubleshoot why the job was cancelled."
}
}
elseif($ServerPwr.Status -eq $null)
{
Write-Output "The $SQLServer instance is offline"
# Do nothing, the SQL server or instance isnt online
}
else
{
# Do nothing, the SQL agent job exists
}
}
As I think it would change the meaning of using virtual labs (surebackup jobs) to run SQL related DBA tasks like checkdb to get maintenance done without affecting the Production environment.
-Atrepes