WebSitePanel. How to migrate hosting spaces to the other server

Hello. I was asked to migrate hosting spaces of websitepanel hosting panel to the other server, so that new and old hosting plans would work on the new server. After transferring resources to the new server, you need to update the websitepanel database, otherwise it will continue to use the old servers. This can be done using a TSQL script.

First, we declare all the variables we need:

 --In the wsp there should not to be duplicate server names and virtual servers names.
use websitepanel;
declare @user varchar(60);
declare @virt_server_name varchar(60);
declare @old_virt_server_name varchar(60);
declare @server_name varchar(60);
declare @old_server_name varchar(60);
declare @service_name varchar(60);
declare @old_service_name varchar(60);
declare @package_name varchar(60);

set @user='username'; --Username for which we change hosting space
set @virt_server_name=''; --The name of the new virtual server, if it changes
set @old_virt_server_name=''; --The name of the old virtual server, if it is changing
set @server_name='SrvHost2012'; --The name of the new server
set @old_server_name='SrvHOST2008'; --The name of the old server

set @service_name='IIS 2012'; --Name of new service
set @old_service_name='IIS 2008'; --Old service name

set @package_name='hosting plan'; --Hostin plan name

declare @user_id int;
declare @server_id int;
declare @package_id int;
declare @service_id int
declare @old_server_id int;
declare @old_service_id int;
declare @virt_server_id int;
declare @old_virt_server_id int;

declare @err int;
declare @rc int;

--Below, variables are assigned an ID, depending on the names. 
--If you do not use English characters anywhere, you must manually add names below this block
select @user_id = UserID from Users where Username=@user;
select @old_server_id = Servers.ServerID from Servers where Servers.ServerName=@old_server_name;
select @server_id = Servers.ServerID from Servers where Servers.ServerName=@server_name;
select @virt_server_id = Servers.ServerID from Servers where Servers.ServerName=@virt_server_name;
select @old_virt_server_id = Servers.ServerID from Servers where Servers.ServerName=@virt_server_name;
select @package_id = Packages.PackageID from Packages where (Packages.UserID=@user_id and Packages.PackageName=@package_name);
select @old_service_id = Services.ServiceID from Services where services.ServiceName=@old_service_name and Services.ServerID=@old_server_id;
select @service_id = Services.ServiceID from Services where services.ServiceName=@service_name and Services.ServerID=@server_id;

Thats all with declaration of variables. 2 sets of commands will be shown below. 1 should be used if you are going to transfer hosting space for one user, the second changes the values for all users and all hosting plans

1)

-- To update bindings for one client
BEGIN TRANSACTION t1;

-- Update the server binding in the hosting plan if the virtual server is changing.
          update Packages set ServerID=@virt_server_id where (Packages.UserID=@user_id and Packages.PackageName=@package_name);
          IF (@err <> 0)
          BEGIN
                 ROLLBACK TRANSACTION t1;
                 RAISERROR ('', 16, 1);
          END

-- Update the hosting of the space, that it would refer to the new service.
          update PackageServices set ServiceID=@service_id where (PackageID=@package_id and ServiceID=@old_service_id);
          SELECT @rc = @@ROWCOUNT, @err = @@ERROR;
          IF (@err <> 0)
          BEGIN
                ROLLBACK TRANSACTION t1;
                RAISERROR ('ACHTUNG!!!', 16, 1);
          END
            
-- Update hosting space, that would be the existing service worked on a new server
          update ServiceItems set ServiceID=@service_id where ServiceID=@old_service_id and PackageID=@package_id
          IF (@err <> 0)
          BEGIN
                    ROLLBACK TRANSACTION t1;
                    RAISERROR ('ACHTUNG!!!', 16, 1);
          END
--    
    
 COMMIT TRANSACTION t1;

2)

-- To update the services for all clients:
BEGIN TRANSACTION t1;
-- Updating bindings on a virtual server to the server
       update VirtualServices set ServiceID=@service_id where (ServerID=@virt_server_id and ServiceID=@old_service_id)
       IF (@err <> 0)
       BEGIN
                 ROLLBACK TRANSACTION t1;
                 RAISERROR ('Error in updating virtual services', 16, 1);
       END
        
-- Update bindings on all virtual servers to the server
       update VirtualServices set ServiceID=@service_id where (ServiceID=@old_service_id)
       IF (@err <> 0)
       BEGIN
                 ROLLBACK TRANSACTION t1;
                 RAISERROR ('Error in updating virtual services', 16, 1);
       END
        
-- Update server bindings for all hosting plans
        update Packages set ServerID=@virt_server_id where ServerID=@old_virt_server_id;
        IF (@err <> 0)
        BEGIN
                  ROLLBACK TRANSACTION t1;
                  RAISERROR ('Error in updating hosting plans', 16, 1);
        END

-- Update all hosting spaces, that they would refer to a new service.
        update PackageServices set ServiceID=@service_id where (ServiceID=@old_service_id);
        IF (@err <> 0)
        BEGIN
                  ROLLBACK TRANSACTION t1;
                  RAISERROR ('Error in updating the service', 16, 1);
        END

-- Update hosting space, that would be the existing service worked on a new server
        update ServiceItems set ServiceID=@service_id where ServiceID=@old_service_id
        IF (@err <> 0)
        BEGIN
                  ROLLBACK TRANSACTION t1;
                  RAISERROR ('Error in updating the service items', 16, 1);
        END
 
    COMMIT TRANSACTION t1;

I recommend that you make backups before every application of the script, and after each application check the work of the changed hosting plan.