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.
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
- Restore you VIM_VCDB database from the c:\migration\data\vc_source_orig_db or another backup if you prefer
- 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
- 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
- Close SQL Management because the migration process requires exclusive access to the database
- Re-run backup script and hopefully you should get the below after a bit of a wait
[INFO] vSphere configuration backup script completed successfully
- 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.