PowerShell script exchange
ThomasMc
Veteran
Posts: 293
Liked: 19 times
Joined: Apr 13, 2011 12:45 pm
Full Name: Thomas McConnell
Contact:

Re: HOWTO: Backing up vCenter host issues

Post by ThomasMc »

Ian Cook wrote: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
Hi Ian, I too are sorry for the slow response, I'll setup a small lab and see if I can reproduce the error and get back to you :)
ThomasMc
Veteran
Posts: 293
Liked: 19 times
Joined: Apr 13, 2011 12:45 pm
Full Name: Thomas McConnell
Contact:

Re: HOWTO: Backing up vCenter host issues

Post by ThomasMc »

Tested it on 2005 and 2008(OS Win2008) both working as expected Ian

2005

Code: Select all

StatisticsEnabled                : False
ConnectionString                 : Server=COOR01\VEEAM;Database=VeeamBackup;Integrated Security=SSPI;
ConnectionTimeout                : 15
Database                         : VeeamBackup
DataSource                       : COOR01\VEEAM
PacketSize                       : 8000
ServerVersion                    : 09.00.5000
WorkstationId                    : COOR01
FireInfoMessageEventOnUserErrors : False
State                            : Open
Site                             :
Container                        :
2008

Code: Select all

StatisticsEnabled                : False
ConnectionString                 : Server=COOR01\SQLExpress;Database=VeeamBackup;Integrated Security=SSPI;
ConnectionTimeout                : 15
Database                         : VeeamBackup
DataSource                       : COOR01\SQLExpress
PacketSize                       : 8000
ServerVersion                    : 10.00.1600
WorkstationId                    : COOR01
FireInfoMessageEventOnUserErrors : False
State                            : Open
Site                             :
Container                        :
tc.gan
Influencer
Posts: 10
Liked: never
Joined: Nov 04, 2011 11:05 am
Full Name: Gan Tien Chor
Contact:

Re: HOWTO: Backing up vCenter host issues

Post by tc.gan »

Ian Cook wrote: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
i having the same error msg . my Veeam B & R is win2k8 R2 SP1 with latest patches, SQL2005 Express Bundle with Veeam( Default setting) , Enabled TCP/IP & Names Pipes Protocol At SQL Server configuration Manger. My custom scripts as below , even i change server name to ip address also same result. Please help. TQVM

asnp "VeeamPSSnapIn" -ErrorAction SilentlyContinue

$VCVMName = "vcenter"
$serverName = "$env:VMSVEEAM10\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
{
"Error: $_"
break
}

try
{
$cmd.ExecuteNonQuery() | Out-Null
}
catch
{
Write-Warning "Update failed"
}
finally
{
$conn.Close()
}
ThomasMc
Veteran
Posts: 293
Liked: 19 times
Joined: Apr 13, 2011 12:45 pm
Full Name: Thomas McConnell
Contact:

Re: HOWTO: Backing up vCenter host issues

Post by ThomasMc » 1 person likes this post

I have updated the comments on my site, the issues is with the server name part

Code: Select all

$serverName = "$env:VMSVEEAM10\VEEAM"
$env:COMPUTERNAME is a environment variable in PS that will get the local computer name and normally you wouldn't have to change it.

Code: Select all

#acceptable values
$serverName = "$env:COMPUTERNAME\VEEAM"
#or
$serverName = "VMSVEEAM10\VEEAM"
#but not
$serverName = "$env:VMSVEEAM10\VEEAM"
tc.gan
Influencer
Posts: 10
Liked: never
Joined: Nov 04, 2011 11:05 am
Full Name: Gan Tien Chor
Contact:

Re: HOWTO: Backing up vCenter host issues

Post by tc.gan »

Hi Thomas ,

i did change the server name to VMSVEEAM10\VEEAM , but now the error message is "Warning : Updated failed" , anything that i did wrong? . Thank you very much for your assistant.
ThomasMc
Veteran
Posts: 293
Liked: 19 times
Joined: Apr 13, 2011 12:45 pm
Full Name: Thomas McConnell
Contact:

Re: HOWTO: Backing up vCenter host issues

Post by ThomasMc »

It will probably be a duplicate entry that's causing your issue now, to confirm this can your change

Code: Select all

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

Code: Select all

try
{
$cmd.ExecuteNonQuery() | Out-Null
}
catch
{
"Error: $_"
}
finally
{
$conn.Close()
}
tc.gan
Influencer
Posts: 10
Liked: never
Joined: Nov 04, 2011 11:05 am
Full Name: Gan Tien Chor
Contact:

Re: HOWTO: Backing up vCenter host issues

Post by tc.gan »

Hi Thomas,

Thanks for fast reply, but i was busy past few days. i'd just tested the modified script at My lab & it is working fine now. i'll try at customer site next week. Tqvm for your help.By the way, what's your meaning of duplicate entry that's causing my issue? i'm so sorry, i m not good of Powershell command...

Regards,
Gan
ThomasMc
Veteran
Posts: 293
Liked: 19 times
Joined: Apr 13, 2011 12:45 pm
Full Name: Thomas McConnell
Contact:

Re: HOWTO: Backing up vCenter host issues

Post by ThomasMc »

The duplicate entry is created if you've every used the manual way of updating the vCenter job after vMotion

1) vMotion vCenter VM
2) Edit job to remove old VC object and add in the new one via the GUI

Veeam will treat this as a new VM being added to the job and add another entry to its BObjects table, so you will have something like below

Code: Select all

2d800d24-aee1-4f08-9027-56fac01cff4f	1253	vCenter01.vPowerCLI.Local
3f620ec5-1f4b-440a-aa36-fc16601053e2	1234	vCenter01.vPowerCLI.Local
When you run the script it will attempt to update BOTH records with the same data and break the rules so the update will through an exception.
tc.gan
Influencer
Posts: 10
Liked: never
Joined: Nov 04, 2011 11:05 am
Full Name: Gan Tien Chor
Contact:

Re: HOWTO: Backing up vCenter host issues

Post by tc.gan »

Hi Thomas,


i tried on customer site. but failed with below message

Error: Exception calling "ExecuteNonQuery" with "0" argument(s): "Cannot insert duplicate key row in object 'dbo.BObjects' with unique index 'IX_BObjects'.
The statement has been terminated."

I tried delete vcenter backup job & delete from disk , then recreate & perform vCenter backup job . But problem still persist with same error message.

The only different between Lab & customer site are:
1)vcenter's VM name , Veeam's Server name
2)Lab using local disk , customer using SAN storage.

Thank you very much with your assistant.
tc.gan
Influencer
Posts: 10
Liked: never
Joined: Nov 04, 2011 11:05 am
Full Name: Gan Tien Chor
Contact:

Re: HOWTO: Backing up vCenter host issues

Post by tc.gan »

Sorry 2 more different are
1)Lab using vSphere 5.0 & vCenter 5 , customer using vSphere 4.1 with vCenter 4.1
ThomasMc
Veteran
Posts: 293
Liked: 19 times
Joined: Apr 13, 2011 12:45 pm
Full Name: Thomas McConnell
Contact:

Re: HOWTO: Backing up vCenter host issues

Post by ThomasMc »

Like said, the duplicate entry is in the DB itself and apart from removing it from there you cannot fix it from the GUI
tc.gan
Influencer
Posts: 10
Liked: never
Joined: Nov 04, 2011 11:05 am
Full Name: Gan Tien Chor
Contact:

Re: HOWTO: Backing up vCenter host issues

Post by tc.gan »

Dear Thomas,

Is it possible to fix it through SQL 2005 express Management Studio? or must use powershell to fix it? May i know how to do that (procedure)? tqvm.

Regards
Gan
ThomasMc
Veteran
Posts: 293
Liked: 19 times
Joined: Apr 13, 2011 12:45 pm
Full Name: Thomas McConnell
Contact:

Re: HOWTO: Backing up vCenter host issues

Post by ThomasMc »

Yes and Both but is unsupported by Veeam, I'm not going to detail what you should do but have made a short video of how you could do it :D

BACKUP DB
BACKUP DB

Remove duplicate VC Object via SQL Management Express

If the video isn't enough or you don't understand it then please don't attempt it.
Ian Cook
Influencer
Posts: 10
Liked: never
Joined: Oct 06, 2011 8:03 am
Full Name: Ian Cook
Contact:

Re: HOWTO: Backing up vCenter host issues

Post by Ian Cook »

Thanks for the time and effort you have put in Thomas, again im sorry for the slow repsonses.

I will test this change and get back to you :)

Thanks again :)
Ian Cook
Influencer
Posts: 10
Liked: never
Joined: Oct 06, 2011 8:03 am
Full Name: Ian Cook
Contact:

Re: HOWTO: Backing up vCenter host issues

Post by Ian Cook »

Ah the little computername change seems to have resolved my issues, thanks again Thomas :)
azuegler
Influencer
Posts: 11
Liked: never
Joined: Jun 06, 2012 8:57 am
Contact:

Re: HOWTO: Backing up vCenter host issues

Post by azuegler »

Hi all,

does anyone know if there is a solution for this implemented in B&R Version 6.1?

Best regards,
Andreas
Gostev
Chief Product Officer
Posts: 31459
Liked: 6648 times
Joined: Jan 01, 2006 1:01 am
Location: Baar, Switzerland
Contact:

Re: HOWTO: Backing up vCenter host issues

Post by Gostev »

No changes in 6.1.
azuegler
Influencer
Posts: 11
Liked: never
Joined: Jun 06, 2012 8:57 am
Contact:

Re: HOWTO: Backing up vCenter host issues

Post by azuegler »

Thanks for the quick reply. Is it planned in future releases?

BR,
Andreas
Gostev
Chief Product Officer
Posts: 31459
Liked: 6648 times
Joined: Jan 01, 2006 1:01 am
Location: Baar, Switzerland
Contact:

Re: HOWTO: Backing up vCenter host issues

Post by Gostev »

Why are you keep posting the same questions in two different threads? :D
azuegler
Influencer
Posts: 11
Liked: never
Joined: Jun 06, 2012 8:57 am
Contact:

Re: HOWTO: Backing up vCenter host issues

Post by azuegler »

Because my first post was a new thread and in review state and because I didnt mention a support case ID i thought that it wont be accepted by the Moderators. Sorry for that.
Gostev
Chief Product Officer
Posts: 31459
Liked: 6648 times
Joined: Jan 01, 2006 1:01 am
Location: Baar, Switzerland
Contact:

Re: HOWTO: Backing up vCenter host issues

Post by Gostev »

Oh, OK. Actually, product and feature questions do not require support case ID, only technical issues do (bugs, errors any sort of malfunctions).
itwheel
Novice
Posts: 3
Liked: never
Joined: May 24, 2012 4:25 pm
Full Name: MARCUS D RODGERS
Contact:

Re: HOWTO: Backing up vCenter host issues

Post by itwheel »

I tried your troubleshooting step because I was getting the same type of error -
ThomasMc wrote: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
My code is as follows:

Code: Select all

asnp "VeeamPSSnapIn" -ErrorAction SilentlyContinue

$VCVMName = "DUvCenter"
$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()
}
#>
try
{
$cmd.ExecuteNonQuery() | Out-Null
}
catch
{
"Error: $_"
}
finally
{
$conn.Close()
}
Result:
Error: Exception calling "ExecuteNonQuery" with "0" argument(s): "Conversion failed
when converting from a character string to uniqueidentifier."

Any Advice?
Sethbartlett
Veteran
Posts: 282
Liked: 26 times
Joined: Nov 10, 2010 6:51 pm
Full Name: Seth Bartlett
Contact:

Re: HOWTO: Backing up vCenter host issues

Post by Sethbartlett »

I would check the output of $cmd.CommandText and see if it looks like what you expect the SQL query to look like or if it's messed up. This part is probably messing it up:

object_name = '$VCVMName'

You have everything else as $($variable) and that by itself, it cannot convert properly, just either do $($VCVMName) or object_name = '" + $VCVMName + "'AND...
Skype: Sethbartlett88 - Make sure to label who you are and why you want to add me ;)
Twitter: @sethbartlett
If my post was helpful, please like it. Sometimes twitter is quicker to hit me up if you need me.
ThomasMc
Veteran
Posts: 293
Liked: 19 times
Joined: Apr 13, 2011 12:45 pm
Full Name: Thomas McConnell
Contact:

Re: HOWTO: Backing up vCenter host issues

Post by ThomasMc »


I would check the output of $cmd.CommandText and see if it looks like what you expect the SQL query to look like or if it's messed up. This part is probably messing it up:

object_name = '$VCVMName'

You have everything else as $($variable) and that by itself, it cannot convert properly, just either do $($VCVMName) or object_name = '" + $VCVMName + "'AND...
That's how I wrote it :)

I would say the GUID is either missing or not returning how we would expect it to, so first if you could answer what version of Veeam your using and change the error trap a little to give us more info

Code: Select all

try 
{
   $conn.Open()
}
catch
{
   "GUID: $($vc.Reference)"
   $vc.Reference | Measure-Object -Character
   "Error: $_"
   break
}
ThomasMc
Veteran
Posts: 293
Liked: 19 times
Joined: Apr 13, 2011 12:45 pm
Full Name: Thomas McConnell
Contact:

Re: HOWTO: Backing up vCenter host issues

Post by ThomasMc »

Sorry, I selected the wrong command, try this

Code: Select all

try
{
$cmd.ExecuteNonQuery() | Out-Null
}
catch
{
   "GUID: $($vc.Reference)"
   $vc.Reference | Measure-Object -Character
   "Error: $_"
   break
}
finally
{
$conn.Close()
}
fcastro
Novice
Posts: 8
Liked: never
Joined: Apr 05, 2012 10:52 am
Full Name: Fausto Castro
Contact:

Re: HOWTO: Backing up vCenter host issues

Post by fcastro »

v6.1 sql2k8R2 express

hi many tanks for the script workout. i am not passing the problem below:

Code: Select all

asnp "VeeamPSSnapIn" -ErrorAction SilentlyContinue

$VCVMName = "vcenter"
$serverName ="$env:computername\SQLEXPRESS" 
$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
{
"GUID: $($vc.Reference)"
$vc.Reference | Measure-Object -Character
"Error: $_"
break
}
finally
{
$conn.Close()
}

output:

PS C:\> C:\go.ps1
GUID:

Lines Words Characters Property
----- ----- ---------- --------
0
Error: Exception calling "ExecuteNonQuery" with "0" argument(s): "Conversion failed when converting from
a character string to uniqueidentifier."

_____________________________________________________________________________________________________


found it by cmd:

PS C:\> $teco= Get-VBRJob -name "ALL" | Get-VBRJobObject -NAME "vcenter"
PS C:\> $teco.info.Objectid

Guid
----
dcad4480-9299-48fb-ac1e-893f3ebf5857
ThomasMc
Veteran
Posts: 293
Liked: 19 times
Joined: Apr 13, 2011 12:45 pm
Full Name: Thomas McConnell
Contact:

Re: HOWTO: Backing up vCenter host issues

Post by ThomasMc »

I'll have a look to see what's been changed in the latest version
ThomasMc
Veteran
Posts: 293
Liked: 19 times
Joined: Apr 13, 2011 12:45 pm
Full Name: Thomas McConnell
Contact:

Re: HOWTO: Backing up vCenter host issues

Post by ThomasMc »

This should fix it, some bright spark thought he could make up the rules with this one :S

*6.1 only*

change

Code: Select all

$vc = Find-VBRViEntity -Name $VCVMName | ?{$_.Reference -notlike "vm-*"}
to

Code: Select all

$vc = Find-VBRViEntity -Name $VCVMName | ?{($_.Reference -notlike "vm-*") -AND ($null -ne $_.Reference)}
fcastro
Novice
Posts: 8
Liked: never
Joined: Apr 05, 2012 10:52 am
Full Name: Fausto Castro
Contact:

Re: HOWTO: Backing up vCenter host issues

Post by fcastro »

tanks,
gives the bellow error:

GUID:

Lines Words Characters Property
----- ----- ---------- --------
0
Error: Exception calling "ExecuteNonQuery" with "0" argument(s): "Conversion failed when converting from a character string to uniqueidentifier."
ThomasMc
Veteran
Posts: 293
Liked: 19 times
Joined: Apr 13, 2011 12:45 pm
Full Name: Thomas McConnell
Contact:

Re: HOWTO: Backing up vCenter host issues

Post by ThomasMc »

can you post the output of the below command

Code: Select all

Find-VBRViEntity -Name "your.vcenter.vm"
thanks
Post Reply

Who is online

Users browsing this forum: No registered users and 22 guests