HOWTO: Backing up vCenter host issues

[Sethbartlett]

I'm going to cover something that has been covered before, but with a little twist, an UNSUPPORTED twist. (Which is mostly my powershell stuff ) When backing up your vCenter(Virtual Center Server) and trying to use VSS(Guest-Processing in Veeam), you will run into an issue where you will get "Freeze operation timed out, wait 900 seconds". The issue is actually the database being frozen for the Virtual Center server and it cannot update the database/Veeam because it's in a frozen state, so it times out.

The work around for this is to add your ESX/ESXi host into Veeam aside from your VC. So for example:

vSphere Client to the VC Shows:

esx1.host.local
esx2.host.local
esx3.host.local

You would want to add in the host that manages your VC by IP address into Veeam.

vSphere Client to the VC Shows:

192.168.1.100
192.168.1.101
192.168.1.102

You would want to add in the host that manages your VC by netname. If it does not have a netname, you can add an entry into your hosts file on the Veeam box. After you add your host into Veeam, you will need to backup your VC by picking your host directly, rather than drilling down through your VC. The issue that comes with this is that if you vMotion your VC or have to move/re-add to inventory, the reference ID of the VC VM changes. This will in turn, break the job and you will get something like "Object reference not set to an instance of an object" or "Object not found", something along these lines.

So the real question is, how do you fix this without doing a brand new full every time.

The requirements:

- vPower CLI
- Connection to Veeam Database

So the first part, vPower CLI:

Code: Select all

#First, we need to connect to the VC to get information about the host that manages it
#User/Password are optional and a prompt will come up for you to type credentials, you can also supply 

PSCredential
$VC = Connect-VIServer "VC" -User "MyUser" -Password "MyPassword"
$myHost = (Get-VM "vCenter Name" -Server $VC).VMHost
if($myHost.name -match "((\d{1,3})\.?){4}")
{
	$ServerName = [System.Net.DNS]::GetHostbyAddress(matches[0])
}
else
{
	$ServerName = [System.Net.DNS]::GetHostAddresses($myHost.name)[0].ipaddresstostring
}

# Now we need to connect to the host that the VC is on
$tempHost = Connect-VIServer $myHost.name -user "MyUser" -Password "MyPassword"
$vm = Get-VM "vCenter Name" -Server $tempHost
$id = $vm.id -replace '[a-z]+-'

asnp "VeeamPSSnapIn"
$Server = Get-VBRServer | ?{$_.name -eq $ServerName }

So from here, we have two choices on how we want to update the DB(You may have more if you so choose)

The script we are going to run is:

Code: Select all

UPDATE [BObjects] set host_id = $Server.id, object_id=$id WHERE object_name = $vm.name AND object_id NOT 

LIKE 'vm%'

You can use 'sqlcmd' and the syntax would be:

Code: Select all

sqlcmd -s SERVERNAME\INSTANCE -d Database -q "The query above"

For powershell, you would do the following:

Code: Select all

$Connection = New-Object System.Data.SQLClient.SQLConnection
$Connection.ConnectionString = "server=servername;database=VeeamBackup;trusted_connection=true;"
$Connection.Open()
$Command = New-Object System.Data.SQLClient.SQLCommand
$Command.Connection = $Connection
$Command.CommandText = "UPDATE [BObjects] set host_id = $Server.id, object_id=$id WHERE object_name = 

$vm.name AND object_id NOT LIKE 'vm%'"
$Command.ExecuteNonQuery()

This will update your job to the correct host and update the reference ID. Your job will continue change block tracking and will continue like nothing is broke

[ThomasMc]

Great work Seth, you where spot on about killing my script

[ThomasMc]

Would this get you what you need without the PowerCLI stuff?

Code: Select all

$VCVMName = "vcenter.lab.local"
$esxihost = Get-VBRServer | ?{$_.Type -eq "ESX" -or "ESXi" -and $_.Reference -eq "ha-host" }

foreach ( $esxiserver in $esxihost ) {
	$foundvc = Find-VBRObject -Server $esxiserver | ?{$_.Name -eq $VCVMName } -WarningAction SilentlyContinue
	if ( $foundvc -ne $null ) {
		$newvchost = $esxiserver.RealName
	}
}

$Server = Get-VBRServer | ?{$_.name -eq $newvchost }
$id = $foundvc.Ref

[Sethbartlett]

That actually works PERFECT! I prefer that method and never even thought of doing it that way. You would need to do $id = $foundvc.Ref inside your foreach loop after setting $newvchost but that would definitely work and provide the exact same results without the need for Power-CLI and some extra DNS crap I am doing!

[ThomasMc]

A little tweak to the -or as well, seems I got that bit wrong lol

Code: Select all

?{($_.Type -eq "ESX") -or ($_.Type -eq "ESXi") -and $_.Reference -eq "ha-host" }

or even this

Code: Select all

?{($_.Type -eq "ESX") -or ($_.Type -eq "ESXi") -and $_.Reference -notlike "host*" }

Been testing this most of last night and its working like a dream, hats off to you for coming up with this

[ThomasMc]

add a little zip to it

Code: Select all

asnp "VeeamPSSnapIn" -ErrorAction SilentlyContinue

$VCVMName = "vcenter01.lab.local"
$serverName = "$env:computername\VEEAM"            
$databaseName = "VeeamBackup"


$esxihost = Get-VBRServer | ?{($_.Type -eq "ESX") -or ($_.Type -eq "ESXi") -and $_.Reference -notlike "host*"}

foreach ($esxiserver in $esxihost) {
	$foundvc = Find-VBRObject -Server $esxiserver | ?{$_.Name -eq $VCVMName}
	if ( $foundvc -ne $null ) {
	$newvchost = $esxiserver.RealName
	$id = $foundvc.Ref
	}
}

$Server = Get-VBRServer | ?{$_.name -eq $newvchost}
$hostguid = $Server.id.guid
          
$dataAdapter = New-Object System.Data.SqlClient.SqlDataAdapter            
$query = "SELECT * from [BObjects] WHERE object_name = '$VCVMName' AND object_id NOT LIKE 'vm%'"            
$connString = "Server=$serverName;Database=$databaseName;Integrated Security=SSPI;"            
$dataAdapter.SelectCommand = new-object System.Data.SqlClient.SqlCommand ($query,$connString)            
$commandBuilder = new-object System.Data.SqlClient.SqlCommandBuilder $dataAdapter            
$dt = New-Object System.Data.DataTable            

try
{
	$dataAdapter.fill($dt) 
}
catch
{
	Write-Warning "Error: Could not fill the Datatable"
	break
}

if ($dt.Row.Count -gt 1) {
	Write-Warning "This might fail with duplicate rows error, old data probably exist"
}

$dt  | foreach {($_.host_id = "$hostguid") -and ($_.object_id = "$id") }

try
{
	$dataAdapter.Update($dt)
}
catch
{
	Write-Warning "Error: Could not update the table"
   break
}

[Sethbartlett]

Nicely done. Now you dont have to constantly re-create jobs and such

[ThomasMc]

Yeah the way I was doing it was a bit clunky plus the addition benefits of having CBT and No Re-seeding

10 vMos, 10 Backups, 10 SB and 10 Reps done and not one failed

[Sethbartlett]

Awesome! Thanks for fully putting this through the works. I tested it with a few jobs and few vMotions and it looked pretty solid but it looks like you really beat on it

[velowulf]

Huge thanks should go to ThomasMC and Sethbartlett on this. The script has solved a major issue for a lot of people!! Thank you

[Sethbartlett]

Any time:) You can actually use this same method to fix any VM issue with the object being removed. So if you had to remove a VM from inventory and re-add it back, you can follow the same type of information in the script and DB update to fix the job.

[ThomasMc]

Glad your finding it useful, I was merely Pinky and Seth was the Brain on this one could I ask how your triggering it though, are you manually running it? Scheduled Task? or something else

[Sethbartlett]

Well you could do a scheduled task. Another option would be to have it fire off on the vMotion event in VMWare.

[ThomasMc]

Another option would be to have it fire off on the vMotion event in VMWare.

That's how I'm doing it now, I found 2 ways, one really insecure way(with PsExec firing the script) and another secure way(via encryption and ps remoting). I've got a unpublished blog post that shows you how to use the later with some customisation to the script above but wasn't sure on the etiquette for posting your work on there lol

[Sethbartlett]

You can always re-post any of my stuff I label it as unsupported for a reason, since I'm also a support guy

[velowulf]

I am successfully running it as a scheduled task on the Veeam server every hour. I hadn't even thought about firing it on the vMotion event.... I might give it a go as I like that this is event driven

[smartsys]

To Gostev:

Please add this as a feature in the Veeam Software.
It would absolutely be a great PLUS if Veeam is able to detect the vCenter server itself and automatically update the location the VM is running.

[ThomasMc]

I was putting together a new version of this for v6 and noticed that my new script has issues with CBT on backups after its been ran(Replications don't seem effected) can anyone else confirm this for the old script?

Things to look out for
Next run was a full even though marked as incremental or
Cannot use CBT: Soap fault.

[Gostev]

This is known issue affecting upgraded jobs, it is described in the sticky topic in main forum. It has nothing to deal with this script - will happen even if you run the job manually or on schedule. Thanks!

[ThomasMc]

Ah, Thanks for the info

v6 tidy up

Code: Select all

asnp "VeeamPSSnapIn" -ErrorAction SilentlyContinue

$VCVMName = "vcenter01.lab.local"
$serverName = "$env:computername\VEEAM"            
$databaseName = "VeeamBackup"

$vc = Find-VBRViEntity -Name $VCVMName | ?{$_.Reference -notlike "vm-*"}
      
$conn = New-Object System.Data.SQLClient.SQLConnection
$conn.ConnectionString = "Server=$serverName;Database=$databaseName;Integrated Security=SSPI;"

$cmd = New-Object System.Data.SQLClient.SQLCommand
$cmd.Connection = $conn
$cmd.CommandText = "UPDATE [BObjects] set host_id = '$($vc.ConnHost.Id)', 
object_id = '$($vc.Reference)', 
path = '$($vc.Path)' WHERE object_name = '$VCVMName' AND object_id NOT LIKE 'vm%'"

try 
{
	$conn.Open()
}
catch
{
	Write-Warning "Couldn't open connection"
	break
}

try
{
	$cmd.ExecuteNonQuery() | Out-Null
}
catch
{
	Write-Warning "Update failed"
}
finally
{
	$conn.Close()
}

I went back to Seths original SQL update as I timed it against the Datatable way and the difference was negligible

[Ian Cook]

Hi, thanks for this it looks just the job for the issues we have been experiencing.

Im a bit new to PowerShell with Veeam, and when i run the V6 script above i get a "couldnt open connection" error, the database is SQL 2005 Express (only a small deployment) the account im running the script as is the dbo of the Veeam database, and i am running the script on the Veeam server itself, so DB and VBR6 are all on the same VM. I can run the Find-VBRViEntity command from powershell and get successful results. It looks almost like it cant connect to the SQL instance. The server is Windows 2008 R2 SP1, with all the MS updates applied. Have i missed something obvious ?

Thanks in advance for any assistance you can provide.

Ian

[ThomasMc]

Is your SQL server using Windows authentication?

[Ian Cook]

Hi Thomas, yes it is using Windows Authentication.

Thanks

Ian

[ThomasMc]

I've never tested it on Win 2008 but that should cause us any issues, points of interest on the script are

Code: Select all

$serverName = "$env:computername\VEEAM"            
$databaseName = "VeeamBackup"

[Ian Cook]

I've never tested it on Win 2008 but that should cause us any issues, points of interest on the script are

Code: Select all

$serverName = "$env:computername\VEEAM"            
$databaseName = "VeeamBackup"

Thanks Thomas, yes i had changed the values to match the computername, the database and instance are default so the rest is fine.

Ian

[ThomasMc]

Change

Code: Select all

try 
{
   $conn.Open()
}
catch
{
   Write-Warning "Couldn't open connection"
   break
}

to

Code: Select all

try 
{
   $conn.Open()
}
catch
{
   "Error: $_"
   break
}

and paste the results

[Ian Cook]

Hi Thomas, sorry for the slow reply, been a bit busy this week.

Ran the updated script, and this is the output

Code: Select all

"Error: Exception calling "Open" with "0" argument(s): "A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 25 - Connection string is not valid)

I have already been through the settings to allow remote connections, as its SQL Express 2008

Thankis again for the assistance

Ian

[ThomasMc]

Hi Ian, just so that I'm on the same page

Windows Server 2008 - OS
Veeam Server v6
SQL Express 2008 - Local DB

All on the same machine

[Ian Cook]

Hi Thomas, really sorry for the slow responses, been busy on another project.

Yes

Windows Server 2008 R2 SP1
Veeam V6 HF3
SQL Express 2008 Local DB

Thanks

Ian

[krag]

[merged]

I've hit the issue in this KB article: http://www.veeam.com/kb_articles.html/KB1051. When we run VSS aware backups against our main SQL server the job fails as the VSS snapshot times out, this is because the vCenter database is on this server.

As per the KB article, if I add the IP address of the ESXi host to the backup then it works but we then lose the ability to use VMware DRS or if we manually move the VM to another host for maintenance the job will fail.

So the solution is to run the vCenter database on a separate server but then that's either SQL Express on the vcenter host itself (which is against VMware recommendations as we have over 60 VMs) or we have to pay for another SQL license which isn't cheap especially when we have a reliable production SQL cluster working fine.

Does anyone have any suggestions?