Problem:

Recently I had a need to upgrade our VMware vCenter server from 4.0 to 4.1 and also as the current server was on a 32-bit OS so I also needed to migrate to a new server. I started following the below VMware KB article.

http://kb.vmware.com/selfservice/microsites/search.do?language=en_US&cmd=displayKC&externalId=1021635

I was not far into the process when I received the message “Error converting data type bigint to int” and I found the below in the log file;

[INFO] Msg 8114, Level 16, State 1, Server XXXXXX\SQLEXP_VIM, Line 1
Error converting data type bigint to int.
Msg 3013, Level 16, State 1, Server XXXXXX\SQLEXP_VIM, Line 1
RESTORE FILELIST is terminating abnormally.

[ERROR] Error: vCenter Server DB backup failed
[ERROR] Exiting..

Cause:

After a bit of digging I found the cause of the problem seemed to be the data in the VPX_HIST_STATx tables within the VIM_VCDB database.

These tables hold your performance history for VM’s and Hosts, and for some reason the migration script was having problems with the data in theses tables.

Workaround:

At this point I noticed that the migration process had modified my original DB which I was not too happy about, luckily the migration process does seem to create a backup first.

This workaround assumes you have some SQL knowledge

If you are using a local SQL express database you will need to use SQL Server Management Studio to connect to SERVER\ SQLEXP_VIM

  1. Restore you VIM_VCDB database from the c:\migration\data\vc_source_orig_db or another backup if you prefer
  2. Clear out the contents of all of the  VPX_HIST_STATx tables from the VIM_VCDB database, I used the below SQL
use VIM_VCDB

begin transaction
delete from dbo.VPX_HIST_STAT
delete from dbo.VPX_HIST_STAT1
delete from dbo.VPX_HIST_STAT2
delete from dbo.VPX_HIST_STAT3
delete from dbo.VPX_HIST_STAT4
commit
  1. Move C:\datamigration\data off to a different location just in case you need it again, the data folder must be either moved or deleted before we can retry
  2. Close SQL Management because the migration process requires exclusive access to the database
  3. Re-run backup script and hopefully you should get the below after a bit of a wait
[INFO] vSphere configuration backup script completed successfully
  1. Copy the migration folder to you new 64-bit server and continue the process on the VMware KB

In my case I was not too worried about my historic performance data not being migrated, if you need your historic performance data I suggest you talk to VMware or maybe you could use SQL tools after the workaround to manually import the data, if you find a way please comment and share your method.