****************************************
Disclaimer: Keep in mind, this is the process that worked for me, and I don\u2019t recommend that anyone perform this on live data, but rather to test data. <\/p>\n
I am not responsible for any changes to your database as a result of this process.<\/p>\n If you choose to use this method, please backup your database beforehand.<\/p>\n All VC 2.0.x database references are called VirtualCenter and all VC 1.x references are called VirtualCenterOLD.<\/p>\n Also, this properly works with dbo as the owner of all tables. If you have an alternate owner (as I do) you will have to modify the below scripts to match.<\/p>\n ****************************************<\/p>\n After performing a VC 1.x to VC 2.0.x upgrade, custom attributes are lost.<\/p>\n This is because the VC 1.x and VC 2.0.x databases handle custom attributes a little different. <\/span>VC 1.x has a custom attributes table called VPX_FIELDDEF, and VMs and HOSTs have separate tables (VPX_VM_FIELDS and VPX_HOST_FIELDS respectively.<\/p>\n In VC 2.0 there are only 2 tables, VPX_FIELD_DEF and VPX_FIELD_VAL. <\/span><\/p>\n The VC 1.0 table VPX_FIELDDEF is replaced by VPX_FIELD_DEF in VC 2.0.x. <\/span><\/p>\n The VC 1.0 tables, VPX_VM_FIELDS and VPX_HOST_FIELDS are replaced by VPX_FIELD_VAL in VC 2.0.<\/p>\n To import the old values into the new database, a couple steps will have to be taken.<\/p>\n Let\u2019s say I have a SQL 2000 database with my VC 2.0.x database named VirtualCenter.<\/p>\n I need to get to the tables from my old VC 1.x installation. <\/span>So I need to restore the old VC 1.x database where I can get to the data I need. <\/span>So I restore the VC 1.x database to a database (on the same server), with the name VirtualCenterOLD.<\/p>\n Now run the following script from Query Analyzer, while attached to the new VirtualCenter database:<\/p>\n USE VirtualCenter<\/p>\n GO<\/p>\n INSERT INTO dbo.VPX_FIELD_DEF SELECT <\/span>FIELD_ID AS ID, NAME FROM <\/span>VirtualCenterOLD.dbo.VPX_FIELDDEF<\/p>\n GO<\/span><\/p>\n This will pull all values from the old VirtualCenterOLD table named VPX_FIELDDEF into the new VirtualCenter database table named VPX_FIELD_DEF, thus defining our custom attribute fields.<\/p>\n Now, We need to add the data from the Hosts and VMs.<\/p>\n Now run the following script from Query Analyzer, while attached to the new VirtualCenter database:<\/p>\n USE VirtualCenter<\/p>\n GO<\/p>\n INSERT INTO dbo.VPX_FIELD_VAL SELECT <\/span>VirtualCenterOLD.dbo.VPX_HOST_FIELDS.FIELD_ID AS FIELD_ID, dbo.VPXV_HOSTS.HOSTID AS ENTITY_ID, <\/span>VirtualCenterOLD.dbo.VPX_HOST_FIELDS.FIELD_VALUE AS [VALUE] FROM <\/span>VirtualCenterOLD.dbo.VPX_HOST LEFT OUTER JOIN <\/span> <\/span>dbo.VPXV_HOSTS ON VirtualCenterOLD.dbo.VPX_HOST.HOSTNAME = dbo.VPXV_HOSTS.NAME LEFT OUTER JOIN <\/span>VirtualCenterOLD.dbo.VPX_HOST_FIELDS ON VirtualCenterOLD.dbo.VPX_HOST.HOST_ID = VirtualCenterOLD.dbo.VPX_HOST_FIELDS.HOST_ID WHERE <\/span>(NOT (VirtualCenterOLD.dbo.VPX_HOST_FIELDS.FIELD_VALUE IS NULL)) AND (NOT (VirtualCenterOLD.dbo.VPX_HOST_FIELDS.FIELD_ID IS NULL))<\/p>\n GO<\/span><\/p>\n This will insert HOST attributes into the VPX_FIELD_VAL, where the FIELD_ID and VALUE come from the old data, and the ENTITY_ID comes from the new Entity value for the ESX Hosts in the new database, making sure that there are no nulls.<\/p>\n Now run the following script from Query Analyzer, while attached to the new VirtualCenter database:<\/p>\n USE VirtualCenter<\/p>\n GO<\/p>\n INSERT INTO dbo.VPX_FIELD_VAL SELECT <\/span>VirtualCenterOLD.dbo.VPX_VM_FIELDS.FIELD_ID AS FIELD_ID, dbo.VPXV_VMS.VMID AS ENTITY_ID, <\/span>VirtualCenterOLD.dbo.VPX_VM_FIELDS.FIELD_VALUE AS [VALUE] FROM <\/span>VirtualCenterOLD.dbo.VPX_VM LEFT OUTER JOIN <\/span>dbo.VPXV_VMS ON VirtualCenterOLD.dbo.VPX_VM.NAME = dbo.VPXV_VMS.NAME LEFT OUTER JOIN <\/span>VirtualCenterOLD.dbo.VPX_VM_FIELDS ON VirtualCenterOLD.dbo.VPX_VM.VM_ID = VirtualCenterOLD.dbo.VPX_VM_FIELDS.VM_ID WHERE <\/span>(NOT (VirtualCenterOLD.dbo.VPX_VM_FIELDS.FIELD_VALUE IS NULL)) AND (NOT (VirtualCenterOLD.dbo.VPX_VM_FIELDS.FIELD_ID IS NULL))<\/p>\n GO<\/span><\/p>\n This will insert VM attributes into the VPX_FIELD_VAL, where the FIELD_ID and VALUE come from the old data, and the ENTITY_ID comes from the new Entity value for the VM\u2019s in the new database, making sure that there are no nulls.<\/p>\n This worked well for me, and I didn’t lose any custom attributes for my 300+ VM’s or my ESX Hosts.<\/p>\n","protected":false},"excerpt":{"rendered":" ****************************************Disclaimer: Keep in mind, this is the process that worked for me, and I don\u2019t recommend that anyone perform this on live data, but rather … <\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[12],"tags":[],"class_list":["post-98","post","type-post","status-publish","format-standard","hentry","category-virtualization"],"_links":{"self":[{"href":"https:\/\/www.jasemccarty.com\/blog\/wp-json\/wp\/v2\/posts\/98","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.jasemccarty.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.jasemccarty.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.jasemccarty.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.jasemccarty.com\/blog\/wp-json\/wp\/v2\/comments?post=98"}],"version-history":[{"count":0,"href":"https:\/\/www.jasemccarty.com\/blog\/wp-json\/wp\/v2\/posts\/98\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.jasemccarty.com\/blog\/wp-json\/wp\/v2\/media?parent=98"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.jasemccarty.com\/blog\/wp-json\/wp\/v2\/categories?post=98"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.jasemccarty.com\/blog\/wp-json\/wp\/v2\/tags?post=98"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}