-----------------------------------------------------
--[dbo].[usp_Import_Folder_Host_Changes]
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_Import_Folder_Host_Changes]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp_Import_Folder_Host_Changes]
GO
CREATE PROCEDURE [dbo].[usp_Import_Folder_Host_Changes]
     @dbInstanceId uniqueidentifier,
     @timeZoneShift int,
     @importDoc XML
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @imported_changes table
    (
        db_instance_id uniqueidentifier,
       [id] uniqueidentifier,
       [folder_id] uniqueidentifier,
       [host_id] uniqueidentifier
    )
    
    /*              IMPORT XML DOCUMENT       */
    DECLARE @idoc int
    EXEC sp_xml_preparedocument @idoc OUTPUT, @importDoc
    
    INSERT INTO @imported_changes([id],[folder_id],[host_id])
    SELECT *
    FROM OPENXML( @idoc, '/Root/row', 2)
    WITH
    (
       [id] uniqueidentifier 'id',
       [folder_id] uniqueidentifier 'folder_id',
       [host_id] uniqueidentifier 'host_id'
    )
    EXEC sp_xml_removedocument @idoc
    
    /*             ADD DBID  FIELD     */
    UPDATE @imported_changes
    SET db_instance_id= @dbInstanceId
    
    /*             TIMEZONE UPDATES     */
    /*      UPDATE EXISTING ROWS        */
    UPDATE [C.Folder_Host]
    SET
        [C.Folder_Host].[folder_id] = src.[folder_id],
        [C.Folder_Host].[host_id] = src.[host_id]
    FROM [C.Folder_Host] INNER JOIN @imported_changes src
    ON [C.Folder_Host].id = src.id
    
    /*      INSERT NEW ROWS       */
    INSERT INTO [C.Folder_Host](db_instance_id,[id],[folder_id],[host_id])
    SELECT
        src.db_instance_id,
        src.[id],
        src.[folder_id],
        src.[host_id]
    FROM @imported_changes src LEFT OUTER JOIN [C.Folder_Host] trg
    ON src.id = trg.id
    WHERE trg.id IS NULL
END
GO
-----------------------------------------------------



-----------------------------------------------------
--[dbo].[usp_Import_Folder_Host_Dels]
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_Import_Folder_Host_Dels]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp_Import_Folder_Host_Dels]
GO
CREATE PROCEDURE [dbo].[usp_Import_Folder_Host_Dels] 
	@deleted_objects_xml XML,
	@dbInstanceId uniqueidentifier
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @deleted_objects_table table
    (
       obj_id  uniqueidentifier
    )
    
    /* importing deleted objects set  */
    DECLARE @idoc  int
    EXEC sp_xml_preparedocument @idoc OUTPUT, @deleted_objects_xml
    INSERT INTO @deleted_objects_table( obj_id )
    SELECT *
    FROM OPENXML( @idoc, N'/root/object',1 )
    WITH
    (
       obj_id uniqueidentifier '@id'
    )
    EXEC sp_xml_removedocument @idoc
    
    DELETE FROM [dbo].[C.Folder_Host]
    WHERE id IN ( SELECT obj_id FROM @deleted_objects_table )
END
GO
-----------------------------------------------------



-----------------------------------------------------
--[dbo].[usp_Import_ObjectsInJobs_Changes]
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_Import_ObjectsInJobs_Changes]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp_Import_ObjectsInJobs_Changes]
GO
CREATE PROCEDURE [dbo].[usp_Import_ObjectsInJobs_Changes]
     @dbInstanceId uniqueidentifier,
     @timeZoneShift int,
     @importDoc XML
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @imported_changes table
    (
        db_instance_id uniqueidentifier,
       [id] uniqueidentifier,
       [job_id] uniqueidentifier,
       [object_id] uniqueidentifier,
       [folder_id] uniqueidentifier,
       [vss_options] xml,
       [approx_size] bigint,
       [location] nvarchar(1024),
       [type] int,
	   [order_no] int
    )
    
    /*              IMPORT XML DOCUMENT       */
    DECLARE @idoc int
    EXEC sp_xml_preparedocument @idoc OUTPUT, @importDoc
    
    INSERT INTO @imported_changes([id],[job_id],[object_id],[folder_id],[vss_options],[approx_size],[location],[type],[order_no])
    SELECT *
    FROM OPENXML( @idoc, '/Root/row', 2)
    WITH
    (
       [id] uniqueidentifier 'id',
       [job_id] uniqueidentifier 'job_id',
       [object_id] uniqueidentifier 'object_id',
       [folder_id] uniqueidentifier 'folder_id',
       [vss_options] xml 'vss_options',
       [approx_size] bigint 'approx_size',
       [location] nvarchar(1024) 'location',
       [type] int 'type',
	   [order_no] int 'order_no'
    )
    EXEC sp_xml_removedocument @idoc
    
    /*             ADD DBID  FIELD     */
    UPDATE @imported_changes
    SET db_instance_id= @dbInstanceId
    
    /*             TIMEZONE UPDATES     */
    /*      UPDATE EXISTING ROWS        */
    UPDATE [C.ObjectsInJobs]
    SET
        [C.ObjectsInJobs].[job_id] = src.[job_id],
        [C.ObjectsInJobs].[object_id] = src.[object_id],
        [C.ObjectsInJobs].[folder_id] = src.[folder_id],
        [C.ObjectsInJobs].[vss_options] = src.[vss_options],
        [C.ObjectsInJobs].[approx_size] = src.[approx_size],
        [C.ObjectsInJobs].[location] = src.[location],
        [C.ObjectsInJobs].[type] = src.[type],
		[C.ObjectsInJobs].[order_no] = src.[order_no]
    FROM [C.ObjectsInJobs] INNER JOIN @imported_changes src
    ON [C.ObjectsInJobs].id = src.id
    
    /*      INSERT NEW ROWS       */
    INSERT INTO [C.ObjectsInJobs](db_instance_id,[id],[job_id],[object_id],[folder_id],[vss_options],[approx_size],[location],[type],[order_no])
    SELECT
        src.db_instance_id,
        src.[id],
        src.[job_id],
        src.[object_id],
        src.[folder_id],
        src.[vss_options],
        src.[approx_size],
        src.[location],
        src.[type],
		src.[order_no]
    FROM @imported_changes src LEFT OUTER JOIN [C.ObjectsInJobs] trg
    ON src.id = trg.id
    WHERE trg.id IS NULL
END
GO
-----------------------------------------------------



-----------------------------------------------------
--[dbo].[usp_Import_ObjectsInJobs_Dels]
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_Import_ObjectsInJobs_Dels]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp_Import_ObjectsInJobs_Dels]
GO
CREATE PROCEDURE [dbo].[usp_Import_ObjectsInJobs_Dels]
    @deleted_objects_xml XML,
	@dbInstanceId uniqueidentifier
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @deleted_objects_table table
    (
       obj_id  uniqueidentifier
    )
    
    /*                importing deleted objects set   */
    DECLARE @idoc  int
    EXEC sp_xml_preparedocument @idoc OUTPUT, @deleted_objects_xml
    INSERT INTO @deleted_objects_table( obj_id )
    SELECT *
    FROM OPENXML( @idoc, N'/root/object',1 )
    WITH
    (
       obj_id uniqueidentifier '@id'
    )
    EXEC sp_xml_removedocument @idoc
    
    DELETE FROM [dbo].[C.ObjectsInJobs]
    WHERE id IN ( SELECT obj_id FROM @deleted_objects_table )
END
GO
-----------------------------------------------------



-----------------------------------------------------
--[dbo].[usp_Import_Soap_creds_Changes]
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_Import_Soap_creds_Changes]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp_Import_Soap_creds_Changes]
GO
CREATE PROCEDURE [dbo].[usp_Import_Soap_creds_Changes]
     @dbInstanceId uniqueidentifier,
     @timeZoneShift int,
     @importDoc XML
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @imported_changes table
    (
        db_instance_id uniqueidentifier,
       [id] uniqueidentifier,
       [host_id] uniqueidentifier,
       [port] int,
       [savepassword] bit,
       [useproxy] bit,
       [proxyip] nvarchar(50),
       [proxyport] int,
       [enabled] bit
    )
    
    /*              IMPORT XML DOCUMENT       */
    DECLARE @idoc int
    EXEC sp_xml_preparedocument @idoc OUTPUT, @importDoc
    
    INSERT INTO @imported_changes([id],[host_id],[port],[savepassword],[useproxy],[proxyip],[proxyport],[enabled])
    SELECT *
    FROM OPENXML( @idoc, '/Root/row', 2)
    WITH
    (
       [id] uniqueidentifier 'id',
       [host_id] uniqueidentifier 'host_id',
       [port] int 'port',
       [savepassword] bit 'savepassword',
       [useproxy] bit 'useproxy',
       [proxyip] nvarchar(50) 'proxyip',
       [proxyport] int 'proxyport',
       [enabled] bit 'enabled'
    )
    EXEC sp_xml_removedocument @idoc
    
    /*             ADD DBID  FIELD     */
    UPDATE @imported_changes
    SET db_instance_id= @dbInstanceId
    
    /*             TIMEZONE UPDATES     */
    /*      UPDATE EXISTING ROWS        */
    UPDATE [C.Soap_creds]
    SET
        [C.Soap_creds].[host_id] = src.[host_id],
        [C.Soap_creds].[port] = src.[port],
        [C.Soap_creds].[savepassword] = src.[savepassword],
        [C.Soap_creds].[useproxy] = src.[useproxy],
        [C.Soap_creds].[proxyip] = src.[proxyip],
        [C.Soap_creds].[proxyport] = src.[proxyport],
        [C.Soap_creds].[enabled] = src.[enabled]
    FROM [C.Soap_creds] INNER JOIN @imported_changes src
    ON [C.Soap_creds].id = src.id
    
    /*      INSERT NEW ROWS       */
    INSERT INTO [C.Soap_creds](db_instance_id,[id],[host_id],[port],[savepassword],[useproxy],[proxyip],[proxyport],[enabled])
    SELECT
        src.db_instance_id,
        src.[id],
        src.[host_id],
        src.[port],
        src.[savepassword],
        src.[useproxy],
        src.[proxyip],
        src.[proxyport],
        src.[enabled]
    FROM @imported_changes src LEFT OUTER JOIN [C.Soap_creds] trg
    ON src.id = trg.id
    WHERE trg.id IS NULL
END
GO
-----------------------------------------------------



-----------------------------------------------------
--[dbo].[usp_Import_Soap_creds_Dels]
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_Import_Soap_creds_Dels]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp_Import_Soap_creds_Dels]
GO
CREATE PROCEDURE [dbo].[usp_Import_Soap_creds_Dels]
    @deleted_objects_xml XML,
	@dbInstanceId uniqueidentifier
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @deleted_objects_table table
    (
       obj_id  uniqueidentifier
    )
    
    /*                importing deleted objects set   */
    DECLARE @idoc  int
    EXEC sp_xml_preparedocument @idoc OUTPUT, @deleted_objects_xml
    INSERT INTO @deleted_objects_table( obj_id )
    SELECT *
    FROM OPENXML( @idoc, N'/root/object',1 )
    WITH
    (
       obj_id uniqueidentifier '@id'
    )
    EXEC sp_xml_removedocument @idoc
    
    DELETE FROM [dbo].[C.Soap_creds]
    WHERE id IN ( SELECT obj_id FROM @deleted_objects_table )
END
GO
-----------------------------------------------------



-----------------------------------------------------
--[dbo].[usp_Import_BJobs_Changes]
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_Import_BJobs_Changes]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp_Import_BJobs_Changes]
GO
CREATE PROCEDURE [dbo].[usp_Import_BJobs_Changes]
     @dbInstanceId uniqueidentifier,
     @timeZoneShift int,
     @importDoc XML
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @imported_changes table
    (
        db_instance_id uniqueidentifier,
       [id] uniqueidentifier,
       [type] int,
       [name] nvarchar(255),
       [target_host_id] uniqueidentifier,
       [target_dir] nvarchar(2000),
       [target_file] nvarchar(2000),
       [options] xml,
       [schedule] xml,
       [is_deleted] bit,
       [latest_result] int,
       [post_command_run_count] int,
       [vss_options] xml,
       [vcb_host_id] uniqueidentifier,
       [target_type] int,
       [description] nvarchar(1024),
       [next_run_time] datetime,
       [schedule_enabled] bit,
       [platform] int,
	   [parent_schedule_id] uniqueidentifier,
	   [repository_id] uniqueidentifier
    )
    
    /*              IMPORT XML DOCUMENT       */
    DECLARE @idoc int
    EXEC sp_xml_preparedocument @idoc OUTPUT, @importDoc
    
    INSERT INTO @imported_changes([id],[type],[name],[target_host_id],[target_dir],[target_file],[options],[schedule],
	[is_deleted],[latest_result],[post_command_run_count],[vss_options],[vcb_host_id],[target_type],
	[description], [next_run_time], [schedule_enabled],[platform],[parent_schedule_id], [repository_id])
    SELECT x.*
    FROM OPENXML( @idoc, '/Root/row', 2)
    WITH
    (
       [id] uniqueidentifier 'id',
       [type] int 'type',
       [name] nvarchar(255) 'name',
       [target_host_id] uniqueidentifier 'target_host_id',
       [target_dir] nvarchar(2000) 'target_dir',
       [target_file] nvarchar(2000) 'target_file',
       [options] xml 'options/*',
       [schedule] xml 'schedule/*',
       [is_deleted] bit 'is_deleted',
       [latest_result] int 'latest_result',
       [post_command_run_count] int 'post_command_run_count',
       [vss_options] xml 'vss_options/*',
       [vcb_host_id] uniqueidentifier 'vcb_host_id',
       [target_type] int 'target_type',
       [description] nvarchar(1024) 'description',
       [next_run_time] datetime 'next_run_time',
       [schedule_enabled] bit 'schedule_enabled',
       [platform] int 'platform',
	   [parent_schedule_id] uniqueidentifier 'parent_schedule_id',
	   [repository_id] uniqueidentifier 'repository_id' 
    ) AS x WHERE x.type < 100; /* Do not import util jobs */


    EXEC sp_xml_removedocument @idoc
    
    /*             ADD DBID  FIELD     */
    UPDATE @imported_changes
    SET db_instance_id= @dbInstanceId
    
    /*             TIMEZONE UPDATES     */
    UPDATE @imported_changes
	SET [next_run_time] = DATEADD( hour, -@TimeZoneShift, [next_run_time])
    
    /*      UPDATE EXISTING ROWS        */
    UPDATE [C.BJobs]
    SET
        [C.BJobs].[type] = src.[type],
        [C.BJobs].[name] = src.[name],
        [C.BJobs].[target_host_id] = src.[target_host_id],
        [C.BJobs].[target_dir] = src.[target_dir],
        [C.BJobs].[target_file] = src.[target_file],
        [C.BJobs].[options] = src.[options],
        [C.BJobs].[schedule] = src.[schedule],
        [C.BJobs].[is_deleted] = src.[is_deleted],
        [C.BJobs].[latest_result] = src.[latest_result],
        [C.BJobs].[post_command_run_count] = src.[post_command_run_count],
        [C.BJobs].[vss_options] = src.[vss_options],
        [C.BJobs].[vcb_host_id] = src.[vcb_host_id],
        [C.BJobs].[target_type] = src.[target_type],
        [C.BJobs].[description] = src.[description],
        [C.BJobs].[next_run_time] = src.[next_run_time],
        [C.BJobs].[schedule_enabled] = src.[schedule_enabled],
        [C.BJobs].[platform] = src.[platform],
		[C.BJobs].[parent_schedule_id] = src.[parent_schedule_id],
		[C.BJobs].[repository_id] = src.[repository_id]
    FROM [C.BJobs] INNER JOIN @imported_changes src
    ON [C.BJobs].id = src.id
    
    /*      INSERT NEW ROWS       */
    INSERT INTO [C.BJobs](db_instance_id,[id],[type],[name],[target_host_id],[target_dir],[target_file],[options],[schedule],[is_deleted],
	[latest_result],[post_command_run_count],[vss_options],[vcb_host_id],[target_type],[description],[next_run_time],
	 [schedule_enabled], [platform],[parent_schedule_id], [repository_id])
    SELECT
        src.db_instance_id,
        src.[id],
        src.[type],
        src.[name],
        src.[target_host_id],
        src.[target_dir],
        src.[target_file],
        src.[options],
        src.[schedule],
        src.[is_deleted],
        src.[latest_result],
        src.[post_command_run_count],
        src.[vss_options],
        src.[vcb_host_id],
        src.[target_type],
        src.[description],
        src.[next_run_time],
        src.[schedule_enabled],
        src.[platform],
		src.[parent_schedule_id],
		src.[repository_id]
    FROM @imported_changes src LEFT OUTER JOIN [C.BJobs] trg
    ON src.id = trg.id
    WHERE trg.id IS NULL
END
GO
-----------------------------------------------------
        
        
        
-----------------------------------------------------
--[dbo].[usp_Import_BJobs_Dels]
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_Import_BJobs_Dels]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp_Import_BJobs_Dels]
GO
CREATE PROCEDURE [dbo].[usp_Import_BJobs_Dels]
    @deleted_objects_xml XML,
	@dbInstanceId uniqueidentifier
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @deleted_objects_table table
    (
       obj_id  uniqueidentifier
    )
    
    /*                importing deleted objects set   */
    DECLARE @idoc  int
    EXEC sp_xml_preparedocument @idoc OUTPUT, @deleted_objects_xml
    INSERT INTO @deleted_objects_table( obj_id )
    SELECT *
    FROM OPENXML( @idoc, N'/root/object',1 )
    WITH
    (
       obj_id uniqueidentifier '@id'
    )
    EXEC sp_xml_removedocument @idoc
    
    DELETE FROM [dbo].[C.BJobs]
    WHERE id IN ( SELECT obj_id FROM @deleted_objects_table )
END
GO
-----------------------------------------------------



-----------------------------------------------------
--[dbo].[usp_Import_BObjects_Changes]
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_Import_BObjects_Changes]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp_Import_BObjects_Changes]
GO
CREATE PROCEDURE [dbo].[usp_Import_BObjects_Changes]
     @dbInstanceId uniqueidentifier,
     @timeZoneShift int,
     @importDoc XML
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @imported_changes table
    (
        db_instance_id uniqueidentifier,
       [id] uniqueidentifier,
       [type] int,
       [host_id] uniqueidentifier,
       [object_name] nvarchar(2000),
       [object_id] nvarchar(2000),
       [viobject_type] nvarchar(50),
       [guest_os] xml,
	   [path] nvarchar(400),
	   [platform] int,
	   [uuid] nvarchar(256),
	   [unique_key_hash] varbinary(max),
	   [display_name] nvarchar(255)
    )
    
    /*              IMPORT XML DOCUMENT       */
    DECLARE @idoc int
    EXEC sp_xml_preparedocument @idoc OUTPUT, @importDoc
    
    INSERT INTO @imported_changes([id],[type],[host_id],[object_name],[object_id],[viobject_type],[guest_os],[path],[platform],[uuid],[unique_key_hash],[display_name])
    SELECT 
		T.id,
		T.type,
		T.host_id,
		T.object_name,
		T.object_id,
		T.viobject_type,
		T.guest_os,
		T.path,
		T.platform,
		T.uuid,
		T.unique_key_hash.value('.', 'varbinary(max)'),
		T.display_name
    FROM OPENXML( @idoc, '/Root/row', 2)
    WITH
    (
       [id] uniqueidentifier 'id',
       [type] int 'type',
       [host_id] uniqueidentifier 'host_id',
       [object_name] nvarchar(2000) 'object_name',
       [object_id] nvarchar(2000) 'object_id',
       [viobject_type] nvarchar(50) 'viobject_type',
       [guest_os] xml 'guest_os',
	   [path] nvarchar(400) 'path',
	   [platform] int 'platform',
	   [uuid] nvarchar(256) 'uuid',
	   [unique_key_hash] xml 'unique_key_hash',
	   [display_name] nvarchar(255)
    ) as T

    EXEC sp_xml_removedocument @idoc
    
    /*             ADD DBID  FIELD     */
    UPDATE @imported_changes
    SET db_instance_id= @dbInstanceId

    
    /*             TIMEZONE UPDATES     */
    /*      UPDATE EXISTING ROWS        */
    UPDATE [C.BObjects]
    SET
        [C.BObjects].[type] = src.[type],
        [C.BObjects].[host_id] = src.[host_id],
        [C.BObjects].[object_name] = src.[object_name],
        [C.BObjects].[object_id] = src.[object_id],
        [C.BObjects].[viobject_type] = src.[viobject_type],
        [C.BObjects].[guest_os] = src.[guest_os],
		[C.BObjects].[path] = src.[path],
		[C.BObjects].[platform] = src.[platform],
		[C.BObjects].[uuid] = src.[uuid],
		[C.BObjects].[unique_key_hash] = src.[unique_key_hash],
		[C.BObjects].[display_name] = src.[display_name]
    FROM [C.BObjects] INNER JOIN @imported_changes src
    ON [C.BObjects].id = src.id
    
    /*      INSERT NEW ROWS       */
    INSERT INTO [C.BObjects](db_instance_id,[id],[type],[host_id],[object_name],[object_id],[viobject_type],[guest_os],[path],[platform],[uuid], [unique_key_hash],[display_name])
    SELECT
        src.db_instance_id,
        src.[id],
        src.[type],
        src.[host_id],
        src.[object_name],
        src.[object_id],
        src.[viobject_type],
        src.[guest_os],
		src.[path],
		src.[platform],
		src.[uuid],
		src.[unique_key_hash],
		src.[display_name]
    FROM @imported_changes src LEFT OUTER JOIN [C.BObjects] trg
    ON src.id = trg.id
    WHERE trg.id IS NULL
END
GO
-----------------------------------------------------



-----------------------------------------------------
--[dbo].[usp_Import_BObjects_Dels]
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_Import_BObjects_Dels]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp_Import_BObjects_Dels]
GO
CREATE PROCEDURE [dbo].[usp_Import_BObjects_Dels]
    @deleted_objects_xml XML,
	@dbInstanceId uniqueidentifier
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @deleted_objects_table table
    (
       obj_id  uniqueidentifier
    )
    
    /*  importing deleted objects set   */
    DECLARE @idoc  int
    EXEC sp_xml_preparedocument @idoc OUTPUT, @deleted_objects_xml
    INSERT INTO @deleted_objects_table( obj_id )
    SELECT *
    FROM OPENXML( @idoc, N'/root/object',1 )
    WITH
    (
       obj_id uniqueidentifier '@id'
    )
    EXEC sp_xml_removedocument @idoc
    
    DELETE FROM [dbo].[C.BObjects]
    WHERE id IN ( SELECT obj_id FROM @deleted_objects_table )
END
GO
-----------------------------------------------------



-----------------------------------------------------
--

/*======== REPLICATED TABLE: C.Backup.Model.Backups*/

/* UPDATES IMPORT*/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_Import_Backup_Model_Backups_Changes]') AND type in (N'P'))
   BEGIN
       DROP PROCEDURE [dbo].[usp_Import_Backup_Model_Backups_Changes]
   END
GO
    CREATE PROCEDURE [dbo].[usp_Import_Backup_Model_Backups_Changes]
         @dbInstanceId uniqueidentifier,
         @timeZoneShift int,
         @importDoc XML
    AS
    BEGIN
        SET NOCOUNT ON;
        DECLARE @imported_changes table
        (
           [id] uniqueidentifier,
           [job_id] uniqueidentifier,
           [job_name] nvarchar(255),
           [job_source_type] int,
           [job_target_type] int,
           [job_target_host_id] uniqueidentifier,
           [job_target_host_protocol] int,
           [db_instance_id] uniqueidentifier,
		   [platform] int,
		   [repository_id] uniqueidentifier
        )
        
        /*              IMPORT XML DOCUMENT       */
        DECLARE @idoc int
        EXEC sp_xml_preparedocument @idoc OUTPUT, @importDoc
        
        INSERT INTO @imported_changes([id],[job_id],[job_name],[job_source_type],[job_target_type],[job_target_host_id],[job_target_host_protocol],[db_instance_id], [platform], [repository_id])
        SELECT *
        FROM OPENXML( @idoc, '/Root/row', 2)
        WITH
        (
           [id] uniqueidentifier 'id',
           [job_id] uniqueidentifier 'job_id',
           [job_name] nvarchar(255) 'job_name',
           [job_source_type] int 'job_source_type',
           [job_target_type] int 'job_target_type',
           [job_target_host_id] uniqueidentifier 'job_target_host_id',
           [job_target_host_protocol] int 'job_target_host_protocol',
           [db_instance_id] uniqueidentifier 'db_instance_id',
		   [platform] int 'platform',
		   [repository_id] uniqueidentifier 'repository_id'
        )
        EXEC sp_xml_removedocument @idoc
        
        /*             ADD DBID  FIELD     */
        UPDATE @imported_changes
        SET db_instance_id= @dbInstanceId
        
        /*             TIMEZONE UPDATES     */
        /*      UPDATE EXISTING ROWS        */
        UPDATE [C.Backup.Model.Backups]
        SET
            [C.Backup.Model.Backups].[job_id] = src.[job_id],
            [C.Backup.Model.Backups].[job_name] = src.[job_name],
            [C.Backup.Model.Backups].[job_source_type] = src.[job_source_type],
            [C.Backup.Model.Backups].[job_target_type] = src.[job_target_type],
            [C.Backup.Model.Backups].[job_target_host_id] = src.[job_target_host_id],
            [C.Backup.Model.Backups].[job_target_host_protocol] = src.[job_target_host_protocol],
            [C.Backup.Model.Backups].[db_instance_id] = src.[db_instance_id],
			[C.Backup.Model.Backups].[platform] = src.[platform],
			[C.Backup.Model.Backups].[repository_id] = src.[repository_id]
        FROM [C.Backup.Model.Backups] INNER JOIN @imported_changes src
        ON [C.Backup.Model.Backups].id = src.id
        
        /*      INSERT NEW ROWS       */
        INSERT INTO [C.Backup.Model.Backups]([id],[job_id],[job_name],[job_source_type],[job_target_type],[job_target_host_id],[job_target_host_protocol],[db_instance_id],[platform],[repository_id])
        SELECT
            src.[id],
            src.[job_id],
            src.[job_name],
            src.[job_source_type],
            src.[job_target_type],
            src.[job_target_host_id],
            src.[job_target_host_protocol],
            src.[db_instance_id],
			src.[platform],
			src.[repository_id]
        FROM @imported_changes src LEFT OUTER JOIN [C.Backup.Model.Backups] trg
        ON src.id = trg.id
        WHERE trg.id IS NULL
    END
GO
--
-----------------------------------------------------

-----------------------------------------------------
--
/* DELS IMPORT*/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_Import_Backup_Model_Backups_Dels]') AND type in (N'P'))
   BEGIN
       DROP PROCEDURE [dbo].[usp_Import_Backup_Model_Backups_Dels]
   END
GO
    CREATE PROCEDURE [dbo].[usp_Import_Backup_Model_Backups_Dels]
        @deleted_objects_xml XML,
	@dbInstanceId uniqueidentifier
    AS
    BEGIN
        SET NOCOUNT ON;
        DECLARE @deleted_objects_table table
        (
           obj_id  uniqueidentifier
        )
        
        /*                importing deleted objects set   */
        DECLARE @idoc  int
        EXEC sp_xml_preparedocument @idoc OUTPUT, @deleted_objects_xml
        INSERT INTO @deleted_objects_table( obj_id )
        SELECT *
        FROM OPENXML( @idoc, N'/root/object',1 )
        WITH
        (
           obj_id uniqueidentifier '@id'
        )
        EXEC sp_xml_removedocument @idoc
        
        DELETE FROM [dbo].[C.Backup.Model.Backups]
        WHERE id IN ( SELECT obj_id FROM @deleted_objects_table )
    END
GO
--
-----------------------------------------------------



-----------------------------------------------------
--

/*======== REPLICATED TABLE: C.Backup.Model.Storages*/

/* UPDATES IMPORT*/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_Import_Backup_Model_Storages_Changes]') AND type in (N'P'))
   BEGIN
       DROP PROCEDURE [dbo].[usp_Import_Backup_Model_Storages_Changes]
   END
GO
    CREATE PROCEDURE [dbo].[usp_Import_Backup_Model_Storages_Changes]
         @dbInstanceId uniqueidentifier,
         @timeZoneShift int,
         @importDoc XML
    AS
    BEGIN
        SET NOCOUNT ON;
        DECLARE @imported_changes table
        (
           [id] uniqueidentifier,
           [file_path] nvarchar(1000),
           [backup_id] uniqueidentifier,
           [host_id] uniqueidentifier,
           [creation_time] datetime,
           [modification_time] datetime,
           [stats] xml,
           [version] int,
           [block_size] int,
           [db_instance_id] uniqueidentifier,
	   [full_size] bigint
        )
        
        /*              IMPORT XML DOCUMENT       */
        DECLARE @idoc int
        EXEC sp_xml_preparedocument @idoc OUTPUT, @importDoc
        
        INSERT INTO @imported_changes([id],[file_path],[backup_id],[host_id],[creation_time],[modification_time],[stats],[version],[block_size],[db_instance_id])
        SELECT *
        FROM OPENXML( @idoc, '/Root/row', 2)
        WITH
        (
           [id] uniqueidentifier 'id',
           [file_path] nvarchar(1000) 'file_path',
           [backup_id] uniqueidentifier 'backup_id',
           [host_id] uniqueidentifier 'host_id',
           [creation_time] datetime 'creation_time',
           [modification_time] datetime 'modification_time',
           [stats] xml 'stats',
           [version] int 'version',
           [block_size] int 'block_size',
           [db_instance_id] uniqueidentifier 'db_instance_id'
        )
        EXEC sp_xml_removedocument @idoc
        
    DECLARE @cur_backup_id uniqueidentifier
    SET @cur_backup_id = (SELECT TOP(1) id FROM @imported_changes WHERE full_size IS NULL)
    WHILE @cur_backup_id IS NOT NULL
    BEGIN
		DECLARE @xml XML
		SET @xml = (SELECT TOP(1) stats FROM @imported_changes WHERE id = @cur_backup_id)

		IF @xml IS NULL
			UPDATE @imported_changes
			SET full_size = 0
			WHERE id = @cur_backup_id
		ELSE
			BEGIN
				EXEC sp_xml_preparedocument @idoc OUTPUT, @xml

				UPDATE @imported_changes
				SET full_size = 
					(SELECT
						CASE
							WHEN size IS NULL THEN 0
							ELSE size
						END
					FROM OPENXML( @idoc, 'stats/CBackupStats/BackupSize', 3)
					WITH (size bigint 'text()'))
				WHERE id = @cur_backup_id
				
				UPDATE @imported_changes
				SET full_size = 0
				WHERE id = @cur_backup_id AND full_size IS NULL

				EXEC sp_xml_removedocument @idoc
			END
		
		SET @cur_backup_id = (SELECT TOP(1) id FROM @imported_changes WHERE full_size IS NULL)
    END

        /*             ADD DBID  FIELD     */
        UPDATE @imported_changes
        SET db_instance_id= @dbInstanceId
        
        /*             TIMEZONE UPDATES     */
        UPDATE @imported_changes
        SET
        [creation_time] = DATEADD( hour, -@TimeZoneShift, [creation_time]),
        [modification_time] = DATEADD( hour, -@TimeZoneShift, [modification_time])
        /*      UPDATE EXISTING ROWS        */
        UPDATE [C.Backup.Model.Storages]
        SET
            [C.Backup.Model.Storages].[file_path] = src.[file_path],
            [C.Backup.Model.Storages].[backup_id] = src.[backup_id],
            [C.Backup.Model.Storages].[host_id] = src.[host_id],
            [C.Backup.Model.Storages].[creation_time] = src.[creation_time],
            [C.Backup.Model.Storages].[modification_time] = src.[modification_time],
            [C.Backup.Model.Storages].[stats] = src.[stats],
            [C.Backup.Model.Storages].[version] = src.[version],
            [C.Backup.Model.Storages].[block_size] = src.[block_size],
            [C.Backup.Model.Storages].[db_instance_id] = src.[db_instance_id],
	    [C.Backup.Model.Storages].[full_size] = src.[full_size]
        FROM [C.Backup.Model.Storages] INNER JOIN @imported_changes src
        ON [C.Backup.Model.Storages].id = src.id
        
        /*      INSERT NEW ROWS       */
        INSERT INTO [C.Backup.Model.Storages]([id],[file_path],[backup_id],[host_id],[creation_time],[modification_time],[stats],[version],[block_size],[db_instance_id], [full_size])
        SELECT
            src.[id],
            src.[file_path],
            src.[backup_id],
            src.[host_id],
            src.[creation_time],
            src.[modification_time],
            src.[stats],
            src.[version],
            src.[block_size],
            src.[db_instance_id],
            src.[full_size]
        FROM @imported_changes src LEFT OUTER JOIN [C.Backup.Model.Storages] trg
        ON src.id = trg.id
        WHERE trg.id IS NULL
    END
GO

/* DELS IMPORT*/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_Import_Backup_Model_Storages_Dels]') AND type in (N'P'))
   BEGIN
       DROP PROCEDURE [dbo].[usp_Import_Backup_Model_Storages_Dels]
   END
GO
    CREATE PROCEDURE [dbo].[usp_Import_Backup_Model_Storages_Dels]
        @deleted_objects_xml XML,
	@dbInstanceId uniqueidentifier
    AS
    BEGIN
        SET NOCOUNT ON;
        DECLARE @deleted_objects_table table
        (
           obj_id  uniqueidentifier
        )
        
        /*                importing deleted objects set   */
        DECLARE @idoc  int
        EXEC sp_xml_preparedocument @idoc OUTPUT, @deleted_objects_xml
        INSERT INTO @deleted_objects_table( obj_id )
        SELECT *
        FROM OPENXML( @idoc, N'/root/object',1 )
        WITH
        (
           obj_id uniqueidentifier '@id'
        )
        EXEC sp_xml_removedocument @idoc
        
        DELETE FROM [dbo].[C.Backup.Model.Storages]
        WHERE id IN ( SELECT obj_id FROM @deleted_objects_table )
    END
GO
--
-----------------------------------------------------



-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_Import_HostsByJobs_Changes]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp_Import_HostsByJobs_Changes]
GO
CREATE PROCEDURE [dbo].[usp_Import_HostsByJobs_Changes]
     @dbInstanceId uniqueidentifier,
     @timeZoneShift int,
     @importDoc XML
AS
BEGIN

    SET NOCOUNT ON;
    DECLARE @imported_changes table
    (
		[id] [uniqueidentifier],
		[host_id] [uniqueidentifier],
		[job_id] [uniqueidentifier],
		[uuid] [nvarchar](255),
		[db_instance_id] [uniqueidentifier]
    )
    
    /*  IMPORT XML DOCUMENT  */
    DECLARE @idoc int
    EXEC sp_xml_preparedocument @idoc OUTPUT, @importDoc
    

    INSERT INTO @imported_changes([id], [host_id], [job_id], [uuid])
    SELECT *
    FROM OPENXML( @idoc, '/Root/row', 2)
    WITH
    (
		[id] uniqueidentifier 'id',
		[host_id] uniqueidentifier 'host_id',
		[job_id] uniqueidentifier 'job_id',
		[uuid] nvarchar(255) 'uuid'
    )
    EXEC sp_xml_removedocument @idoc



    /*      ADD DBID  FIELD         */
    UPDATE @imported_changes
    SET db_instance_id= @dbInstanceId


    /*      UPDATE EXISTING ROWS    */
    UPDATE hbj
    SET
		hbj.[host_id] = src.host_id,
		hbj.[job_id] = src.job_id,
		hbj.[uuid] = src.uuid
	FROM [dbo].[C.HostsByJobs] hbj
    INNER JOIN @imported_changes src
    ON hbj.id = src.id
    
    
    /*      INSERT NEW ROWS       */
    INSERT INTO [dbo].[C.HostsByJobs]([id], [host_id], [job_id], [uuid], [db_instance_id])
    SELECT
		src.[id], 
		src.[host_id], 
		src.[job_id],	
		src.[uuid], 
		src.[db_instance_id]
    FROM @imported_changes src 
    LEFT OUTER JOIN [dbo].[C.HostsByJobs] trg
    ON src.id = trg.id
    WHERE trg.id IS NULL
    
END
GO
-----------------------------------------------------



-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_Import_HostsByJobs_Dels]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp_Import_HostsByJobs_Dels]
GO
CREATE PROCEDURE [dbo].[usp_Import_HostsByJobs_Dels]
    @deleted_objects_xml XML,
	@dbInstanceId uniqueidentifier
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @deleted_objects_table table
    (
       obj_id  uniqueidentifier
    )    
    /*   importing deleted objects set   */
    DECLARE @idoc  int
    EXEC sp_xml_preparedocument @idoc OUTPUT, @deleted_objects_xml
    INSERT INTO @deleted_objects_table( obj_id )
    SELECT *
    FROM OPENXML( @idoc, N'/root/object',1 )
    WITH
    (
       obj_id uniqueidentifier '@id'
    )
    EXEC sp_xml_removedocument @idoc
    
    DELETE FROM [dbo].[C.HostsByJobs]
    WHERE id IN ( SELECT obj_id FROM @deleted_objects_table )
END
GO
-----------------------------------------------------




-----------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_Import_Backup_Model_Points_Changes]') AND type in (N'P'))
       DROP PROCEDURE [dbo].[usp_Import_Backup_Model_Points_Changes]
GO
    CREATE PROCEDURE [dbo].[usp_Import_Backup_Model_Points_Changes]
         @dbInstanceId uniqueidentifier,
         @timeZoneShift int,
         @importDoc XML
    AS
    BEGIN
        SET NOCOUNT ON;
        DECLARE @imported_changes table
        (
           [id] uniqueidentifier,
           [link_id] uniqueidentifier,

           [creation_time] datetime,
           [type] int,
           [alg] int,
           [group_id] uniqueidentifier,
           [backup_id] uniqueidentifier,
           [num] numeric,
           [db_instance_id] uniqueidentifier
        )
        
        /*              IMPORT XML DOCUMENT       */
        DECLARE @idoc int
        EXEC sp_xml_preparedocument @idoc OUTPUT, @importDoc
        
        INSERT INTO @imported_changes([id],[link_id],[creation_time],[type],[alg],[group_id],[backup_id],[num],[db_instance_id])

        SELECT *
        FROM OPENXML( @idoc, '/Root/row', 2)
        WITH
        (
           [id] uniqueidentifier 'id',
           [link_id] uniqueidentifier 'link_id',

           [creation_time] datetime 'creation_time',
           [type] int 'type',
           [alg] int 'alg',
           [group_id] uniqueidentifier 'group_id',
           [backup_id] uniqueidentifier 'backup_id',
           [num] numeric 'num',
           [db_instance_id] uniqueidentifier 'db_instance_id'
        )
        EXEC sp_xml_removedocument @idoc
        
        /*             ADD DBID  FIELD     */
        UPDATE @imported_changes
        SET db_instance_id= @dbInstanceId
        
        /*             TIMEZONE UPDATES     */
        UPDATE @imported_changes
        SET
        [creation_time] = DATEADD( hour, -@TimeZoneShift, [creation_time])

        /*      UPDATE EXISTING ROWS        */
        UPDATE pnt
        SET
            pnt.[link_id] = src.[link_id],
            pnt.[num] = src.[num],
            pnt.[creation_time] = src.[creation_time],
            pnt.[type] = src.[type],
            pnt.[alg] = src.[alg],
            pnt.[group_id] = src.[group_id],
            pnt.[backup_id] = src.[backup_id]

        FROM [dbo].[C.Backup.Model.Points] pnt
        INNER JOIN @imported_changes src
        ON pnt.id = src.id
        

        /*      INSERT NEW ROWS       */
        INSERT INTO [C.Backup.Model.Points]([id],[link_id],[creation_time],[type],[alg],[group_id],[backup_id],[db_instance_id], [num])
        SELECT
            src.[id],
            src.[link_id],

            src.[creation_time],
            src.[type],
            src.[alg],
            src.[group_id],
            src.[backup_id],
            src.[db_instance_id],
            src.[num]
        FROM @imported_changes src 
        LEFT OUTER JOIN [dbo].[C.Backup.Model.Points] trg
        ON src.id = trg.id
        WHERE trg.id IS NULL
    END
GO

/* DELS IMPORT*/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_Import_Backup_Model_Points_Dels]') AND type in (N'P'))
   BEGIN
       DROP PROCEDURE [dbo].[usp_Import_Backup_Model_Points_Dels]
   END
GO
    CREATE PROCEDURE [dbo].[usp_Import_Backup_Model_Points_Dels]
        @deleted_objects_xml XML,
	@dbInstanceId uniqueidentifier
    AS
    BEGIN
        SET NOCOUNT ON;
        DECLARE @deleted_objects_table table
        (
           obj_id  uniqueidentifier
        )
        
        /*                importing deleted objects set   */
        DECLARE @idoc  int
        EXEC sp_xml_preparedocument @idoc OUTPUT, @deleted_objects_xml
        INSERT INTO @deleted_objects_table( obj_id )
        SELECT *
        FROM OPENXML( @idoc, N'/root/object',1 )
        WITH
        (
           obj_id uniqueidentifier '@id'
        )
        EXEC sp_xml_removedocument @idoc
        
        DELETE FROM [dbo].[C.Backup.Model.Points]
        WHERE id IN ( SELECT obj_id FROM @deleted_objects_table )
    END
GO




-----------------------------------------------------
--[dbo].[usp_Import_Ssh_creds_Changes]
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_Import_Ssh_creds_Changes]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp_Import_Ssh_creds_Changes]
GO
CREATE PROCEDURE [dbo].[usp_Import_Ssh_creds_Changes]
     @dbInstanceId uniqueidentifier,
     @timeZoneShift int,
     @importDoc XML
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @imported_changes table
    (
        db_instance_id uniqueidentifier,
       [id] uniqueidentifier,
       [host_id] uniqueidentifier,
       [savepassword] bit,
       [elevatetoroot] bit,
       [port] int,
       [startftport] int,
       [endftport] int,
       [buffersize] int,
       [isftserver] bit,
       [timeout] int,
       [adjust_firewall] bit,
       [auto_sudo] bit,
       [enabled] bit
    )
    
    /*              IMPORT XML DOCUMENT       */
    DECLARE @idoc int
    EXEC sp_xml_preparedocument @idoc OUTPUT, @importDoc
    
    INSERT INTO @imported_changes([id],[host_id],[savepassword],[elevatetoroot],[port],[startftport],[endftport],[buffersize],[isftserver],[timeout],[adjust_firewall],[auto_sudo],[enabled])
    SELECT *
    FROM OPENXML( @idoc, '/Root/row', 2)
    WITH
    (
       [id] uniqueidentifier 'id',
       [host_id] uniqueidentifier 'host_id',
       [savepassword] bit 'savepassword',
       [elevatetoroot] bit 'elevatetoroot',
       [port] int 'port',
       [startftport] int 'startftport',
       [endftport] int 'endftport',
       [buffersize] int 'buffersize',
       [isftserver] bit 'isftserver',
       [timeout] int 'timeout',
       [adjust_firewall] bit 'adjust_firewall',
       [auto_sudo] bit 'auto_sudo',
       [enabled] bit 'enabled'
    )
    EXEC sp_xml_removedocument @idoc
    
    /*             ADD DBID  FIELD     */
    UPDATE @imported_changes
    SET db_instance_id= @dbInstanceId
    
    /*             TIMEZONE UPDATES     */
    /*      UPDATE EXISTING ROWS        */
    UPDATE [C.Ssh_creds]
    SET
        [C.Ssh_creds].[host_id] = src.[host_id],
        [C.Ssh_creds].[savepassword] = src.[savepassword],
        [C.Ssh_creds].[elevatetoroot] = src.[elevatetoroot],
        [C.Ssh_creds].[port] = src.[port],
        [C.Ssh_creds].[startftport] = src.[startftport],
        [C.Ssh_creds].[endftport] = src.[endftport],
        [C.Ssh_creds].[buffersize] = src.[buffersize],
        [C.Ssh_creds].[isftserver] = src.[isftserver],
        [C.Ssh_creds].[timeout] = src.[timeout],
        [C.Ssh_creds].[adjust_firewall] = src.[adjust_firewall],
        [C.Ssh_creds].[auto_sudo] = src.[auto_sudo],
        [C.Ssh_creds].[enabled] = src.[enabled]
    FROM [C.Ssh_creds] INNER JOIN @imported_changes src
    ON [C.Ssh_creds].id = src.id
    
    /*      INSERT NEW ROWS       */
    INSERT INTO [C.Ssh_creds](db_instance_id,[id],[host_id],[savepassword],[elevatetoroot],[port],[startftport],[endftport],[buffersize],[isftserver],[timeout],[adjust_firewall],[auto_sudo],[enabled])
    SELECT
        src.db_instance_id,
        src.[id],
        src.[host_id],
        src.[savepassword],
        src.[elevatetoroot],
        src.[port],
        src.[startftport],
        src.[endftport],
        src.[buffersize],
        src.[isftserver],
        src.[timeout],
        src.[adjust_firewall],
        src.[auto_sudo],
        src.[enabled]
    FROM @imported_changes src LEFT OUTER JOIN [C.Ssh_creds] trg
    ON src.id = trg.id
    WHERE trg.id IS NULL
END
GO
-----------------------------------------------------



-----------------------------------------------------
--[dbo].[usp_Import_Ssh_creds_Dels]
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_Import_Ssh_creds_Dels]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp_Import_Ssh_creds_Dels]
GO
CREATE PROCEDURE [dbo].[usp_Import_Ssh_creds_Dels]
    @deleted_objects_xml XML,
	@dbInstanceId uniqueidentifier
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @deleted_objects_table table
    (
       obj_id  uniqueidentifier
    )
    
    /*                importing deleted objects set   */
    DECLARE @idoc  int
    EXEC sp_xml_preparedocument @idoc OUTPUT, @deleted_objects_xml
    INSERT INTO @deleted_objects_table( obj_id )
    SELECT *
    FROM OPENXML( @idoc, N'/root/object',1 )
    WITH
    (
       obj_id uniqueidentifier '@id'
    )
    EXEC sp_xml_removedocument @idoc
    
    DELETE FROM [dbo].[C.Ssh_creds]
    WHERE id IN ( SELECT obj_id FROM @deleted_objects_table )
END
GO
-----------------------------------------------------






/*======== REPLICATED TABLE: C.Backup.Model.OIBs*/

/* UPDATES IMPORT*/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_Import_Backup_Model_OIBs_Changes]') AND type in (N'P'))
   BEGIN
       DROP PROCEDURE [dbo].[usp_Import_Backup_Model_OIBs_Changes]
   END
GO
    CREATE PROCEDURE [dbo].[usp_Import_Backup_Model_OIBs_Changes]
     @dbInstanceId uniqueidentifier,
     @timeZoneShift int,
     @importDoc XML
	AS
	BEGIN
		SET NOCOUNT ON;
		DECLARE @imported_changes table
		(
		   [id] uniqueidentifier,
		   [object_id] uniqueidentifier,
		   [point_id] uniqueidentifier,
		   [storage_id] uniqueidentifier,
		   [link_id] uniqueidentifier,
		   [is_corrupted] bit,
		   [is_consistent] bit,
		   [state] int,
		   [type] int,
		   [alg] int,
		   [inside_dir] nvarchar(400),
		   [creation_time] datetime,
		   [vmname] nvarchar(1000),
		   [guest_os_info] xml,
		   [memory] bigint,
		   [approx_size] bigint,
		   [process_id] int,
		   [has_index] bit,
		   [aux_data] xml,
		   [parent_id] uniqueidentifier,
		   [has_exchange] bit,
		   [db_instance_id] uniqueidentifier,
		   [display_name] nvarchar(256),
		   [original_oib_id] uniqueidentifier
		)
    
		/*              IMPORT XML DOCUMENT       */
		DECLARE @idoc int
		EXEC sp_xml_preparedocument @idoc OUTPUT, @importDoc
    
		INSERT INTO @imported_changes([id],[object_id],[point_id],[storage_id],[link_id],[is_corrupted],[is_consistent],[state],[type],[alg],[inside_dir],[creation_time],[vmname],[guest_os_info],[memory],[approx_size],[process_id],[has_index],[aux_data],[parent_id],[has_exchange],[display_name],[original_oib_id],[db_instance_id])
		SELECT *
		FROM OPENXML( @idoc, '/Root/row', 2)
		WITH
		(
		   [id] uniqueidentifier 'id',
		   [object_id] uniqueidentifier 'object_id',
		   [point_id] uniqueidentifier 'point_id',
		   [storage_id] uniqueidentifier 'storage_id',
		   [link_id] uniqueidentifier 'link_id',
		   [is_corrupted] bit 'is_corrupted',
		   [is_consistent] bit 'is_consistent',
		   [state] int 'state',
		   [type] int 'type',
		   [alg] int 'alg',
		   [inside_dir] nvarchar(400) 'inside_dir',
		   [creation_time] datetime 'creation_time',
		   [vmname] nvarchar(1000) 'vmname',
		   [guest_os_info] xml 'guest_os_info',
		   [memory] bigint 'memory',
		   [approx_size] bigint 'approx_size',
		   [process_id] int 'process_id',
		   [has_index] bit 'has_index',
		   [aux_data] xml 'aux_data',
		   [parent_id] uniqueidentifier 'parent_id',
		   [has_exchange] bit 'has_exchange',
		   [display_name] nvarchar(256) 'display_name',
		   [original_oib_id] uniqueidentifier 'original_oib_id',
		   [db_instance_id] uniqueidentifier 'db_instance_id'
		)
		EXEC sp_xml_removedocument @idoc
    
		/*             ADD DBID  FIELD     */
		UPDATE @imported_changes
		SET db_instance_id= @dbInstanceId
    
		/*             TIMEZONE UPDATES     */
		UPDATE @imported_changes
		SET
		[creation_time] = DATEADD( hour, -@TimeZoneShift, [creation_time])
		/*      UPDATE EXISTING ROWS        */
		UPDATE [C.Backup.Model.OIBs]
		SET
			[C.Backup.Model.OIBs].[object_id] = src.[object_id],
			[C.Backup.Model.OIBs].[point_id] = src.[point_id],
			[C.Backup.Model.OIBs].[storage_id] = src.[storage_id],
			[C.Backup.Model.OIBs].[link_id] = src.[link_id],
			[C.Backup.Model.OIBs].[is_corrupted] = src.[is_corrupted],
			[C.Backup.Model.OIBs].[is_consistent] = src.[is_consistent],
			[C.Backup.Model.OIBs].[state] = src.[state],
			[C.Backup.Model.OIBs].[type] = src.[type],
			[C.Backup.Model.OIBs].[alg] = src.[alg],
			[C.Backup.Model.OIBs].[inside_dir] = src.[inside_dir],
			[C.Backup.Model.OIBs].[creation_time] = src.[creation_time],
			[C.Backup.Model.OIBs].[vmname] = src.[vmname],
			[C.Backup.Model.OIBs].[guest_os_info] = src.[guest_os_info],
			[C.Backup.Model.OIBs].[memory] = src.[memory],
			[C.Backup.Model.OIBs].[approx_size] = src.[approx_size],
			[C.Backup.Model.OIBs].[process_id] = src.[process_id],
			[C.Backup.Model.OIBs].[has_index] = src.[has_index],
			[C.Backup.Model.OIBs].[aux_data] = src.[aux_data],
			[C.Backup.Model.OIBs].[parent_id] = src.[parent_id],
			[C.Backup.Model.OIBs].[has_exchange] = src.[has_exchange],
			[C.Backup.Model.OIBs].[display_name] = src.[display_name],
			[C.Backup.Model.OIBs].[original_oib_id] = ISNULL(src.[original_oib_id], src.id),
			[C.Backup.Model.OIBs].[db_instance_id] = src.[db_instance_id]
		FROM [C.Backup.Model.OIBs] INNER JOIN @imported_changes src
		ON [C.Backup.Model.OIBs].id = src.id
    
		/*      INSERT NEW ROWS       */
		INSERT INTO [C.Backup.Model.OIBs]([id],[object_id],[point_id],[storage_id],[link_id],[is_corrupted],[is_consistent],[state],[type],[alg],[inside_dir],[creation_time],[vmname],[guest_os_info],[memory],[approx_size],[process_id],[has_index],[aux_data],[parent_id],[has_exchange],[display_name],[original_oib_id],[db_instance_id])
		SELECT
			src.[id],
			src.[object_id],
			src.[point_id],
			src.[storage_id],
			src.[link_id],
			src.[is_corrupted],
			src.[is_consistent],
			src.[state],
			src.[type],
			src.[alg],
			src.[inside_dir],
			src.[creation_time],
			src.[vmname],
			src.[guest_os_info],
			src.[memory],
			src.[approx_size],
			src.[process_id],
			src.[has_index],
			src.[aux_data],
			src.[parent_id],
			src.[has_exchange],
			src.[display_name],
			ISNULL(src.[original_oib_id], src.id),
			src.[db_instance_id]
		FROM @imported_changes src LEFT OUTER JOIN [C.Backup.Model.OIBs] trg
		ON src.id = trg.id
		WHERE trg.id IS NULL
	END
GO

/* DELS IMPORT*/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_Import_Backup_Model_OIBs_Dels]') AND type in (N'P'))
   BEGIN
       DROP PROCEDURE [dbo].[usp_Import_Backup_Model_OIBs_Dels]
   END
GO
    CREATE PROCEDURE [dbo].[usp_Import_Backup_Model_OIBs_Dels]
        @deleted_objects_xml XML,
	@dbInstanceId uniqueidentifier
    AS
    BEGIN
        SET NOCOUNT ON;
        DECLARE @deleted_objects_table table
        (
           obj_id  uniqueidentifier
        )
        
        /*                importing deleted objects set   */
        DECLARE @idoc  int
        EXEC sp_xml_preparedocument @idoc OUTPUT, @deleted_objects_xml
        INSERT INTO @deleted_objects_table( obj_id )
        SELECT *
        FROM OPENXML( @idoc, N'/root/object',1 )
        WITH
        (
           obj_id uniqueidentifier '@id'
        )
        EXEC sp_xml_removedocument @idoc
        
        DELETE FROM [dbo].[C.Backup.Model.OIBs]
        WHERE id IN ( SELECT obj_id FROM @deleted_objects_table )
    END
GO
-----------------------------------------------------



-----------------------------------------------------
--[dbo].[usp_Import_LicensedHosts_Changes]
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_Import_LicensedHosts_Changes]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp_Import_LicensedHosts_Changes]
GO
CREATE PROCEDURE [dbo].[usp_Import_LicensedHosts_Changes]
     @dbInstanceId uniqueidentifier,
     @timeZoneShift int,
     @importDoc XML
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @imported_changes table
    (
        db_instance_id uniqueidentifier,
       [id] uniqueidentifier,
       [uuid] nvarchar(255),
       [name] nvarchar(255),
       [cpu] int,
       [type] int,
	   [lic_edition] int,
	   [is_licensed] bit,
	   [cores] int,
	   [lic_tier] int
    )
    
    /*              IMPORT XML DOCUMENT       */
    DECLARE @idoc int
    EXEC sp_xml_preparedocument @idoc OUTPUT, @importDoc
    
    INSERT INTO @imported_changes([id],[uuid],[name],[cpu],[type],[lic_edition],[is_licensed],[cores], [lic_tier])
    SELECT *
    FROM OPENXML( @idoc, '/Root/row', 2)
    WITH
    (
       [id] uniqueidentifier 'id',
       [uuid] nvarchar(255) 'uuid',
       [name] nvarchar(255) 'name',
       [cpu] int 'cpu',
       [type] int 'type',
       [lic_edition] int 'lic_edition',
	   [is_licensed] bit 'is_licensed',
	   [cores] int 'cores',
	   [lic_tier] int 'lic_tier'
    )
    EXEC sp_xml_removedocument @idoc
    
    /*             ADD DBID  FIELD     */
    UPDATE @imported_changes
    SET db_instance_id= @dbInstanceId
    
    /*             TIMEZONE UPDATES     */
    /*      UPDATE EXISTING ROWS        */
    UPDATE [C.LicensedHosts]
    SET
        [C.LicensedHosts].[uuid] = src.[uuid],
        [C.LicensedHosts].[name] = src.[name],
        [C.LicensedHosts].[cpu] = src.[cpu],
        [C.LicensedHosts].[type] = src.[type],
        [C.LicensedHosts].[lic_edition] = src.[lic_edition],
        [C.LicensedHosts].[is_licensed] = src.[is_licensed],
		[C.LicensedHosts].[cores] = src.[cores],
		[C.LicensedHosts].[lic_tier] = src.[lic_tier]
    FROM [C.LicensedHosts] INNER JOIN @imported_changes src
    ON [C.LicensedHosts].id = src.id
    
    /*      INSERT NEW ROWS       */
    INSERT INTO [C.LicensedHosts](db_instance_id,[id],[uuid],[name],[cpu],[type],[lic_edition],[is_licensed],[cores], [lic_tier])
    SELECT
        src.db_instance_id,
        src.[id],
        src.[uuid],
        src.[name],
        src.[cpu],
        src.[type],
        src.[lic_edition],
        src.[is_licensed],
		src.[cores],
		src.[lic_tier]
    FROM @imported_changes src LEFT OUTER JOIN [C.LicensedHosts] trg
    ON src.id = trg.id
    WHERE trg.id IS NULL
END
GO
-----------------------------------------------------


-----------------------------------------------------
--[dbo].[usp_Import_LicensedHosts_Dels]
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_Import_LicensedHosts_Dels]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp_Import_LicensedHosts_Dels]
GO
CREATE PROCEDURE [dbo].[usp_Import_LicensedHosts_Dels]
    @deleted_objects_xml XML,
	@dbInstanceId uniqueidentifier
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @deleted_objects_table table
    (
       obj_id  uniqueidentifier
    )
    
    /*                importing deleted objects set   */
    DECLARE @idoc  int
    EXEC sp_xml_preparedocument @idoc OUTPUT, @deleted_objects_xml
    INSERT INTO @deleted_objects_table( obj_id )
    SELECT *
    FROM OPENXML( @idoc, N'/root/object',1 )
    WITH
    (
       obj_id uniqueidentifier '@id'
    )
    EXEC sp_xml_removedocument @idoc
    
    DELETE FROM [dbo].[C.LicensedHosts]
    WHERE id IN ( SELECT obj_id FROM @deleted_objects_table )
END
GO
-----------------------------------------------------



-----------------------------------------------------
--[dbo].[usp_Import_Hosts_Changes]
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_Import_Hosts_Changes]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp_Import_Hosts_Changes]
GO
CREATE PROCEDURE [dbo].[usp_Import_Hosts_Changes]
     @dbInstanceId uniqueidentifier,
     @timeZoneShift int,
     @importDoc XML
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @imported_changes table
    (
        db_instance_id uniqueidentifier,
       [id] uniqueidentifier,
       [type] int,
       [name] nvarchar(255),
       [ip] nvarchar(50),
       [parent_id] uniqueidentifier,
       [info] nvarchar(255),
       [description] nvarchar(1024),
       [protocol] int,
       [reference] nvarchar(1024),
       [api_version] int,
       [win_creds] xml,
	   [host_instance_id] varchar(255),
	   [physical_host_id] uniqueidentifier
    )
    
    /*              IMPORT XML DOCUMENT       */
    DECLARE @idoc int
    EXEC sp_xml_preparedocument @idoc OUTPUT, @importDoc
    
    INSERT INTO @imported_changes([id],[type],[name],[ip],[parent_id],[info],[description],[protocol],[reference],[api_version],[win_creds],[host_instance_id], [physical_host_id])
    SELECT *
    FROM OPENXML( @idoc, '/Root/row', 2)
    WITH
    (
       [id] uniqueidentifier 'id',
       [type] int 'type',
       [name] nvarchar(255) 'name',
       [ip] nvarchar(50) 'ip',
       [parent_id] uniqueidentifier 'parent_id',
       [info] nvarchar(255) 'info',
       [description] nvarchar(1024) 'description',
       [protocol] int 'protocol',
       [reference] nvarchar(1024) 'reference',
       [api_version] int 'api_version',
       [win_creds] xml 'win_creds',
	   [host_instance_id] varchar(255) 'host_instance_id',
	   [physical_host_id] uniqueidentifier 'physical_host_id'
    )
    EXEC sp_xml_removedocument @idoc
    
    /*             ADD DBID  FIELD     */
    UPDATE @imported_changes
    SET db_instance_id= @dbInstanceId
    
    /*             TIMEZONE UPDATES     */
    /*      UPDATE EXISTING ROWS        */
    UPDATE [C.Hosts]
    SET
        [C.Hosts].[type] = src.[type],
        [C.Hosts].[name] = src.[name],
        [C.Hosts].[ip] = src.[ip],
        [C.Hosts].[parent_id] = src.[parent_id],
        [C.Hosts].[info] = src.[info],
        [C.Hosts].[description] = src.[description],
        [C.Hosts].[protocol] = src.[protocol],
        [C.Hosts].[reference] = src.[reference],
        [C.Hosts].[api_version] = src.[api_version],
        [C.Hosts].[win_creds] = src.[win_creds],
		[C.Hosts].[host_instance_id] = src.[host_instance_id],
		[C.Hosts].[physical_host_id] = src.[physical_host_id]
    FROM [C.Hosts] INNER JOIN @imported_changes src
    ON [C.Hosts].id = src.id
    
    /*      INSERT NEW ROWS       */
    INSERT INTO [C.Hosts](db_instance_id,[id],[type],[name],[ip],[parent_id],[info],[description],[protocol],[reference],[api_version],[win_creds],[host_instance_id],[physical_host_id])
    SELECT
        src.db_instance_id,
        src.[id],
        src.[type],
        src.[name],
        src.[ip],
        src.[parent_id],
        src.[info],
        src.[description],
        src.[protocol],
        src.[reference],
        src.[api_version],
        src.[win_creds],
		src.[host_instance_id],
		src.[physical_host_id]
    FROM @imported_changes src LEFT OUTER JOIN [C.Hosts] trg
    ON src.id = trg.id
    WHERE trg.id IS NULL
END
GO
-----------------------------------------------------



-----------------------------------------------------
--[dbo].[usp_Import_Hosts_Dels]
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_Import_Hosts_Dels]') AND type in (N'P'))
BEGIN
   DROP PROCEDURE [dbo].[usp_Import_Hosts_Dels]
END
GO
CREATE PROCEDURE [dbo].[usp_Import_Hosts_Dels]
    @deleted_objects_xml XML,
	@dbInstanceId uniqueidentifier
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @deleted_objects_table table
    (
       obj_id  uniqueidentifier
    )
    
    /*                importing deleted objects set   */
    DECLARE @idoc  int
    EXEC sp_xml_preparedocument @idoc OUTPUT, @deleted_objects_xml
    INSERT INTO @deleted_objects_table( obj_id )
    SELECT *
    FROM OPENXML( @idoc, N'/root/object',1 )
    WITH
    (
       obj_id uniqueidentifier '@id'
    )
    EXEC sp_xml_removedocument @idoc
    
    DELETE FROM [dbo].[C.Hosts]
    WHERE id IN ( SELECT obj_id FROM @deleted_objects_table )
END
GO
-----------------------------------------------------




-----------------------------------------------------
--[dbo].[usp_Import_Folders_Changes]
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_Import_Folders_Changes]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp_Import_Folders_Changes]
GO
CREATE PROCEDURE [dbo].[usp_Import_Folders_Changes]
     @dbInstanceId uniqueidentifier,
     @timeZoneShift int,
     @importDoc XML
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @imported_changes table
    (
        db_instance_id uniqueidentifier,
       [id] uniqueidentifier,
       [parent_id] uniqueidentifier,
       [name] nvarchar(255)
    )
    
    /*              IMPORT XML DOCUMENT       */
    DECLARE @idoc int
    EXEC sp_xml_preparedocument @idoc OUTPUT, @importDoc
    
    INSERT INTO @imported_changes([id],[parent_id],[name])
    SELECT *
    FROM OPENXML( @idoc, '/Root/row', 2)
    WITH
    (
       [id] uniqueidentifier 'id',
       [parent_id] uniqueidentifier 'parent_id',
       [name] nvarchar(255) 'name'
    )
    EXEC sp_xml_removedocument @idoc
    
    /*             ADD DBID  FIELD     */
    UPDATE @imported_changes
    SET db_instance_id= @dbInstanceId
    
    /*             TIMEZONE UPDATES     */
    /*      UPDATE EXISTING ROWS        */
    UPDATE [C.Folders]
    SET
        [C.Folders].[parent_id] = src.[parent_id],
        [C.Folders].[name] = src.[name]
    FROM [C.Folders] INNER JOIN @imported_changes src
    ON [C.Folders].id = src.id
    
    /*      INSERT NEW ROWS       */
    INSERT INTO [C.Folders](db_instance_id,[id],[parent_id],[name])
    SELECT
        src.db_instance_id,
        src.[id],
        src.[parent_id],
        src.[name]
    FROM @imported_changes src LEFT OUTER JOIN [C.Folders] trg
    ON src.id = trg.id
    WHERE trg.id IS NULL
END
GO
-----------------------------------------------------




-----------------------------------------------------
--[dbo].[usp_Import_Folders_Dels]
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_Import_Folders_Dels]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp_Import_Folders_Dels]
GO
CREATE PROCEDURE [dbo].[usp_Import_Folders_Dels]
    @deleted_objects_xml XML,
	@dbInstanceId uniqueidentifier
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @deleted_objects_table table
    (
       obj_id  uniqueidentifier
    )
    
    /*importing deleted objects set   */
    DECLARE @idoc  int
    EXEC sp_xml_preparedocument @idoc OUTPUT, @deleted_objects_xml
    INSERT INTO @deleted_objects_table( obj_id )
    SELECT *
    FROM OPENXML( @idoc, N'/root/object',1 )
    WITH
    (
       obj_id uniqueidentifier '@id'
    )
    EXEC sp_xml_removedocument @idoc
    
    DELETE FROM [dbo].[C.Folders]
    WHERE id IN ( SELECT obj_id FROM @deleted_objects_table )
END
GO
-----------------------------------------------------




/*======== REPLICATED TABLE: C.VirtualLabs*/

/* UPDATES IMPORT*/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_Import_VirtualLabs_Changes]') AND type in (N'P'))
   BEGIN
       DROP PROCEDURE [dbo].[usp_Import_VirtualLabs_Changes]
   END
GO
    CREATE PROCEDURE [dbo].[usp_Import_VirtualLabs_Changes]
         @dbInstanceId uniqueidentifier,
         @timeZoneShift int,
         @importDoc XML
    AS
    BEGIN
        SET NOCOUNT ON;
        DECLARE @imported_changes table
        (
           [id] uniqueidentifier,
           [name] nvarchar(255),
           [description] nvarchar(255),
           [host_id] uniqueidentifier,
           [vm_ref] nvarchar(1024),
           [db_instance_id] uniqueidentifier
        )
        
        /*              IMPORT XML DOCUMENT       */
        DECLARE @idoc int
        EXEC sp_xml_preparedocument @idoc OUTPUT, @importDoc
        
        INSERT INTO @imported_changes([id],[name],[description],[host_id],[vm_ref], [db_instance_id])
        SELECT *
        FROM OPENXML( @idoc, '/Root/row', 2)
        WITH
        (
           [id] uniqueidentifier 'id',
           [name] nvarchar(255) 'name',
           [description] nvarchar(255) 'description',
           [host_id] uniqueidentifier 'host_id',
           [vm_ref] nvarchar(1024) 'vm_ref',
           [db_instance_id] uniqueidentifier 'db_instance_id'
        )
        EXEC sp_xml_removedocument @idoc
        
        /*             ADD DBID  FIELD     */
        UPDATE @imported_changes
        SET db_instance_id= @dbInstanceId
        
        /*             TIMEZONE UPDATES     */
        /*      UPDATE EXISTING ROWS        */
        UPDATE [C.VirtualLabs]
        SET
            [C.VirtualLabs].[name] = src.[name],
            [C.VirtualLabs].[description] = src.[description],
            [C.VirtualLabs].[host_id] = src.[host_id],
            [C.VirtualLabs].[vm_ref] = src.[vm_ref],
            [C.VirtualLabs].[db_instance_id] = src.[db_instance_id]
        FROM [C.VirtualLabs] INNER JOIN @imported_changes src
        ON [C.VirtualLabs].id = src.id
        
        /*      INSERT NEW ROWS       */
        INSERT INTO [C.VirtualLabs]([id],[name],[description],[host_id],[vm_ref],[db_instance_id])
        SELECT
            src.[id],
            src.[name],
            src.[description],
            src.[host_id],
            src.[vm_ref],
            src.[db_instance_id]
        FROM @imported_changes src LEFT OUTER JOIN [C.VirtualLabs] trg
        ON src.id = trg.id
        WHERE trg.id IS NULL
    END
GO

/* DELS IMPORT*/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_Import_VirtualLabs_Dels]') AND type in (N'P'))
   BEGIN
       DROP PROCEDURE [dbo].[usp_Import_VirtualLabs_Dels]
   END
GO
    CREATE PROCEDURE [dbo].[usp_Import_VirtualLabs_Dels]
        @deleted_objects_xml XML,
	@dbInstanceId uniqueidentifier
    AS
    BEGIN
        SET NOCOUNT ON;
        DECLARE @deleted_objects_table table
        (
           obj_id  uniqueidentifier
        )
        
        /*                importing deleted objects set   */
        DECLARE @idoc  int
        EXEC sp_xml_preparedocument @idoc OUTPUT, @deleted_objects_xml
        INSERT INTO @deleted_objects_table( obj_id )
        SELECT *
        FROM OPENXML( @idoc, N'/root/object',1 )
        WITH
        (
           obj_id uniqueidentifier '@id'
        )
        EXEC sp_xml_removedocument @idoc
        
        DELETE FROM [dbo].[C.VirtualLabs]
        WHERE id IN ( SELECT obj_id FROM @deleted_objects_table )
    END
GO

/*======== REPLICATED TABLE: C.ObjectsInApplicationGroups*/

/* UPDATES IMPORT*/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_Import_ObjectsInApplicationGroups_Changes]') AND type in (N'P'))
   BEGIN
       DROP PROCEDURE [dbo].[usp_Import_ObjectsInApplicationGroups_Changes]
   END
GO
    CREATE PROCEDURE [dbo].[usp_Import_ObjectsInApplicationGroups_Changes]
         @dbInstanceId uniqueidentifier,
         @timeZoneShift int,
         @importDoc XML
    AS
    BEGIN
        SET NOCOUNT ON;
        DECLARE @imported_changes table
        (
           [id] uniqueidentifier,
           [folder_id] uniqueidentifier,
           [object_id] uniqueidentifier,
           [order] int,
           [guest_os_info] xml,
           [effective_memory] bigint,
           [options] xml,
           [db_instance_id] uniqueidentifier
        )
        
        /*              IMPORT XML DOCUMENT       */
        DECLARE @idoc int
        EXEC sp_xml_preparedocument @idoc OUTPUT, @importDoc
        
        INSERT INTO @imported_changes([id],[folder_id],[object_id],[order],[guest_os_info],[effective_memory], [options], [db_instance_id])
        SELECT *
        FROM OPENXML( @idoc, '/Root/row', 2)
        WITH
        (
           [id] uniqueidentifier 'id',
           [folder_id] uniqueidentifier 'folder_id',
           [object_id] uniqueidentifier 'object_id',
           [order] int 'order',
           [guest_os_info] xml 'guest_os_info',
           [effective_memory] bigint 'effective_memory',
           [options] xml 'options',
           [db_instance_id] uniqueidentifier 'db_instance_id'
        )
        EXEC sp_xml_removedocument @idoc
        
        /*             ADD DBID  FIELD     */
        UPDATE @imported_changes
        SET db_instance_id= @dbInstanceId
        
        /*             TIMEZONE UPDATES     */
        /*      UPDATE EXISTING ROWS        */
        UPDATE [C.ObjectsInApplicationGroups]
        SET
            [C.ObjectsInApplicationGroups].[folder_id] = src.[folder_id],
            [C.ObjectsInApplicationGroups].[object_id] = src.[object_id],
            [C.ObjectsInApplicationGroups].[order] = src.[order],
            [C.ObjectsInApplicationGroups].[guest_os_info] = src.[guest_os_info],
            [C.ObjectsInApplicationGroups].[effective_memory] = src.[effective_memory],
            [C.ObjectsInApplicationGroups].[options] = src.[options],
            [C.ObjectsInApplicationGroups].[db_instance_id] = src.[db_instance_id]
        FROM [C.ObjectsInApplicationGroups] INNER JOIN @imported_changes src
        ON [C.ObjectsInApplicationGroups].id = src.id
        
        /*      INSERT NEW ROWS       */
        INSERT INTO [C.ObjectsInApplicationGroups]([id],[folder_id],[object_id],[order],[guest_os_info],[effective_memory],[options],[db_instance_id])
        SELECT
            src.[id],
            src.[folder_id],
            src.[object_id],
            src.[order],
            src.[guest_os_info],
            src.[effective_memory],
            src.[options],
            src.[db_instance_id]
        FROM @imported_changes src LEFT OUTER JOIN [C.ObjectsInApplicationGroups] trg
        ON src.id = trg.id
        WHERE trg.id IS NULL
    END
GO

/* DELS IMPORT*/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_Import_ObjectsInApplicationGroups_Dels]') AND type in (N'P'))
   BEGIN
       DROP PROCEDURE [dbo].[usp_Import_ObjectsInApplicationGroups_Dels]
   END
GO
    CREATE PROCEDURE [dbo].[usp_Import_ObjectsInApplicationGroups_Dels]
        @deleted_objects_xml XML,
	@dbInstanceId uniqueidentifier
    AS
    BEGIN
        SET NOCOUNT ON;
        DECLARE @deleted_objects_table table
        (
           obj_id  uniqueidentifier
        )
        
        /*                importing deleted objects set   */
        DECLARE @idoc  int
        EXEC sp_xml_preparedocument @idoc OUTPUT, @deleted_objects_xml
        INSERT INTO @deleted_objects_table( obj_id )
        SELECT *
        FROM OPENXML( @idoc, N'/root/object',1 )
        WITH
        (
           obj_id uniqueidentifier '@id'
        )
        EXEC sp_xml_removedocument @idoc
        
        DELETE FROM [dbo].[C.ObjectsInApplicationGroups]
        WHERE id IN ( SELECT obj_id FROM @deleted_objects_table )
    END
GO



/*======== REPLICATED TABLE: C.DRRoles*/

/* UPDATES IMPORT*/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_Import_DRRoles_Changes]') AND type in (N'P'))
   BEGIN
       DROP PROCEDURE [dbo].[usp_Import_DRRoles_Changes]
   END
GO
    CREATE PROCEDURE [dbo].[usp_Import_DRRoles_Changes]
         @dbInstanceId uniqueidentifier,
         @timeZoneShift int,
         @importDoc XML
    AS
    BEGIN
        SET NOCOUNT ON;
        DECLARE @imported_changes table
        (
           [id] uniqueidentifier,
           [name] nvarchar(max),
           [effective_memory] bigint,
           [boot_delay] int,
           [test_script_file_full_name] nvarchar(max),
           [db_instance_id] uniqueidentifier
        )
        
        /*              IMPORT XML DOCUMENT       */
        DECLARE @idoc int
        EXEC sp_xml_preparedocument @idoc OUTPUT, @importDoc
        
        INSERT INTO @imported_changes([id],[name],[effective_memory],[boot_delay],[test_script_file_full_name],[db_instance_id])
        SELECT *
        FROM OPENXML( @idoc, '/Root/row', 2)
        WITH
        (
           [id] uniqueidentifier 'id',
           [name] nvarchar(max) 'name',
           [effective_memory] bigint 'effective_memory',
           [boot_delay] int 'boot_delay',
           [test_script_file_full_name] nvarchar(max) 'test_script_file_full_name',
           [db_instance_id] uniqueidentifier 'db_instance_id'
        )
        EXEC sp_xml_removedocument @idoc
        
        /*             ADD DBID  FIELD     */
        UPDATE @imported_changes
        SET db_instance_id= @dbInstanceId
        
        /*             TIMEZONE UPDATES     */
        /*      UPDATE EXISTING ROWS        */
        UPDATE [C.DRRoles]
        SET
            [C.DRRoles].[name] = src.[name],
            [C.DRRoles].[effective_memory] = src.[effective_memory],
            [C.DRRoles].[boot_delay] = src.[boot_delay],
            [C.DRRoles].[test_script_file_full_name] = src.[test_script_file_full_name],
            [C.DRRoles].[db_instance_id] = src.[db_instance_id]
        FROM [C.DRRoles] INNER JOIN @imported_changes src
        ON [C.DRRoles].id = src.id
        
        /*      INSERT NEW ROWS       */
        INSERT INTO [C.DRRoles]([id],[name],[effective_memory],[boot_delay],[test_script_file_full_name],[db_instance_id])
        SELECT
            src.[id],
            src.[name],
            src.[effective_memory],
            src.[boot_delay],
            src.[test_script_file_full_name],
            src.[db_instance_id]
        FROM @imported_changes src LEFT OUTER JOIN [C.DRRoles] trg
        ON src.id = trg.id
        WHERE trg.id IS NULL
    END
GO

/* DELS IMPORT*/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_Import_DRRoles_Dels]') AND type in (N'P'))
   BEGIN
       DROP PROCEDURE [dbo].[usp_Import_DRRoles_Dels]
   END
GO
    CREATE PROCEDURE [dbo].[usp_Import_DRRoles_Dels]
        @deleted_objects_xml XML,
	@dbInstanceId uniqueidentifier
    AS
    BEGIN
        SET NOCOUNT ON;
        DECLARE @deleted_objects_table table
        (
           obj_id  uniqueidentifier
        )
        
        /*                importing deleted objects set   */
        DECLARE @idoc  int
        EXEC sp_xml_preparedocument @idoc OUTPUT, @deleted_objects_xml
        INSERT INTO @deleted_objects_table( obj_id )
        SELECT *
        FROM OPENXML( @idoc, N'/root/object',1 )
        WITH
        (
           obj_id uniqueidentifier '@id'
        )
        EXEC sp_xml_removedocument @idoc
        
        DELETE FROM [dbo].[C.DRRoles]
        WHERE id IN ( SELECT obj_id FROM @deleted_objects_table )
    END
GO





/*======== REPLICATED TABLE: C.Backup.Model.JobSessions*/

/* UPDATES IMPORT*/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_Import_Backup_Model_JobSessions_Changes]') AND type in (N'P'))
   BEGIN
       DROP PROCEDURE [dbo].[usp_Import_Backup_Model_JobSessions_Changes]
   END
GO
    CREATE PROCEDURE [dbo].[usp_Import_Backup_Model_JobSessions_Changes]
         @dbInstanceId uniqueidentifier,
         @timeZoneShift int,
         @importDoc XML
    AS
    BEGIN
        SET NOCOUNT ON;
        DECLARE @imported_changes table
        (
           [id] uniqueidentifier,
           [job_id] uniqueidentifier,
           [job_name] nvarchar(255),
           [job_type] int,
           [creation_time] datetime,
           [end_time] datetime,
           [state] int,
           [result] int,
           [control] int,
           [description] text,
           [operation] nvarchar(400),
           [progress] int,
           [log_xml] xml,
           [log_text] NText,
		   [reason] NText,
           [db_instance_id] uniqueidentifier
        )
        
        /*              IMPORT XML DOCUMENT       */
        DECLARE @idoc int
        EXEC sp_xml_preparedocument @idoc OUTPUT, @importDoc
        
        INSERT INTO @imported_changes([id],[job_id],[job_name],[job_type],[creation_time],[end_time],[state],[result],[control],[description],[operation],[progress],[log_xml],[reason],[db_instance_id])
        SELECT *
        FROM OPENXML( @idoc, '/Root/row', 2)
        WITH
        (
           [id] uniqueidentifier 'id',
           [job_id] uniqueidentifier 'job_id',
           [job_name] nvarchar(255) 'job_name',
           [job_type] int 'job_type',
           [creation_time] datetime 'creation_time',
           [end_time] datetime 'end_time',
           [state] int 'state',
           [result] int 'result',
           [control] int 'control',
           [description] text 'description',
           [operation] nvarchar(400) 'operation',
           [progress] int 'progress',
           [log_xml] xml 'log_xml',
		   [reason] ntext 'reason',
           [db_instance_id] uniqueidentifier 'db_instance_id'
        )
        EXEC sp_xml_removedocument @idoc
        
        /*             ADD DBID  FIELD     */
        UPDATE @imported_changes
        SET db_instance_id= @dbInstanceId
        
        /*             TIMEZONE UPDATES     */
        UPDATE @imported_changes
        SET
        [creation_time] = DATEADD( hour, -@TimeZoneShift, [creation_time]),
        [end_time] = DATEADD( hour, -@TimeZoneShift, [end_time])
        
        /*             LOG UPDATES     */        
		UPDATE @imported_changes
        SET log_text = N''
                
        
        /*      UPDATE EXISTING ROWS        */
        UPDATE [C.Backup.Model.JobSessions]
        SET
            [C.Backup.Model.JobSessions].[job_id] = src.[job_id],
            [C.Backup.Model.JobSessions].[job_name] = src.[job_name],
            [C.Backup.Model.JobSessions].[job_type] = src.[job_type],
            [C.Backup.Model.JobSessions].[creation_time] = src.[creation_time],
            [C.Backup.Model.JobSessions].[end_time] = src.[end_time],
            [C.Backup.Model.JobSessions].[state] = src.[state],
            [C.Backup.Model.JobSessions].[result] = src.[result],
            [C.Backup.Model.JobSessions].[control] = src.[control],
            [C.Backup.Model.JobSessions].[description] = src.[description],
            [C.Backup.Model.JobSessions].[operation] = src.[operation],
            [C.Backup.Model.JobSessions].[progress] = src.[progress],
            [C.Backup.Model.JobSessions].[log_xml] = src.[log_xml],
            [C.Backup.Model.JobSessions].[log_text] =  src.[log_text],
			[C.Backup.Model.JobSessions].[reason] =  src.[reason],
            [C.Backup.Model.JobSessions].[db_instance_id] = src.[db_instance_id]
        FROM [C.Backup.Model.JobSessions] INNER JOIN @imported_changes src
        ON [C.Backup.Model.JobSessions].id = src.id
        
        /*      INSERT NEW ROWS       */
        INSERT INTO [C.Backup.Model.JobSessions]([id],[job_id],[job_name],[job_type],[creation_time],[end_time],[state],[result],[control],[description],[operation],[progress],[log_xml],[reason],[db_instance_id],[log_text])
        SELECT
            src.[id],
            src.[job_id],
            src.[job_name],
            src.[job_type],
            src.[creation_time],
            src.[end_time],
            src.[state],
            src.[result],
            src.[control],
            src.[description],
            src.[operation],
            src.[progress],
            src.[log_xml],
			src.[reason],
            src.[db_instance_id],
            src.[log_text]			
        FROM @imported_changes src LEFT OUTER JOIN [C.Backup.Model.JobSessions] trg
        ON src.id = trg.id
        WHERE trg.id IS NULL
    END
GO

/* DELS IMPORT*/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_Import_Backup_Model_JobSessions_Dels]') AND type in (N'P'))
   BEGIN
       DROP PROCEDURE [dbo].[usp_Import_Backup_Model_JobSessions_Dels]
   END
GO
    CREATE PROCEDURE [dbo].[usp_Import_Backup_Model_JobSessions_Dels]
        @deleted_objects_xml XML,
	@dbInstanceId uniqueidentifier
    AS
    BEGIN
        SET NOCOUNT ON;
        DECLARE @deleted_objects_table table
        (
           obj_id  uniqueidentifier
        )
        
        /*                importing deleted objects set   */
        DECLARE @idoc  int
        EXEC sp_xml_preparedocument @idoc OUTPUT, @deleted_objects_xml
        INSERT INTO @deleted_objects_table( obj_id )
        SELECT *
        FROM OPENXML( @idoc, N'/root/object',1 )
        WITH
        (
           obj_id uniqueidentifier '@id'
        )
        EXEC sp_xml_removedocument @idoc
        
        DELETE FROM [dbo].[C.Backup.Model.JobSessions]
        WHERE id IN ( SELECT obj_id FROM @deleted_objects_table )
    END
GO



/*======== REPLICATED TABLE: C.Backup.Model.SbTaskSessions*/

/* UPDATES IMPORT*/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_Import_Backup_Model_SbTaskSessions_Changes]') AND type in (N'P'))
   BEGIN
       DROP PROCEDURE [dbo].[usp_Import_Backup_Model_SbTaskSessions_Changes]
   END
GO
    CREATE PROCEDURE [dbo].[usp_Import_Backup_Model_SbTaskSessions_Changes]
         @dbInstanceId uniqueidentifier,
         @timeZoneShift int,
         @importDoc XML
    AS
    BEGIN
        SET NOCOUNT ON;
        DECLARE @imported_changes table
        (
           [id] uniqueidentifier,
           [drsession_id] uniqueidentifier,
           [type] int,
           [object_name] nvarchar(max),
           [object_id] uniqueidentifier,
           [oib_id] uniqueidentifier,
           [overall_status] int,
           [description] nvarchar(max),
           [start_time] datetime,
           [finish_time] datetime,
           [total_steps] int,
           [processed_steps] int,
           [powerOn_status] int,
           [heartbeat_status] int,
           [ping_status] int,
           [test_script_status] int,
           [powerOff_status] int,
           [test_script_error_code] int,
           [vm_ref] nvarchar(max),
           [percent] int,
           [appliance_ip] nvarchar(80),
           [subnet_ip] nvarchar(80),
           [subnet_mask] nvarchar(80),
           [vm_external_ip] nvarchar(80),
           [state] int,
           [order_num] int,
           [control] int,
           [leave_powered_on] bit,
           [oiag_id] uniqueidentifier,
           [restore_counter] int,
           [db_instance_id] uniqueidentifier,
           [test_scripts_results] xml,
		   [validation_status] int
        )
        
        /*              IMPORT XML DOCUMENT       */
        DECLARE @idoc int
        EXEC sp_xml_preparedocument @idoc OUTPUT, @importDoc
        
        INSERT INTO @imported_changes([id],[drsession_id],[type],[object_name],[object_id],[oib_id],[overall_status],[description],[start_time],[finish_time],[total_steps],[processed_steps],[powerOn_status],[heartbeat_status],[ping_status],[test_script_status],[powerOff_status],[test_script_error_code],[vm_ref],[percent],[appliance_ip],[subnet_ip],[subnet_mask],[vm_external_ip],[state],[order_num],[control],[leave_powered_on],[oiag_id],[restore_counter],[db_instance_id], [validation_status])
        SELECT *
        FROM OPENXML( @idoc, '/Root/row', 2)
        WITH
        (
           [id] uniqueidentifier 'id',
           [drsession_id] uniqueidentifier 'drsession_id',
           [type] int 'type',
           [object_name] nvarchar(max) 'object_name',
           [object_id] uniqueidentifier 'object_id',
           [oib_id] uniqueidentifier 'oib_id',
           [overall_status] int 'overall_status',
           [description] nvarchar(max) 'description',
           [start_time] datetime 'start_time',
           [finish_time] datetime 'finish_time',
           [total_steps] int 'total_steps',
           [processed_steps] int 'processed_steps',
           [powerOn_status] int 'powerOn_status',
           [heartbeat_status] int 'heartbeat_status',
           [ping_status] int 'ping_status',
           [test_script_status] int 'test_script_status',
           [powerOff_status] int 'powerOff_status',
           [test_script_error_code] int 'test_script_error_code',
           [vm_ref] nvarchar(max) 'vm_ref',
           [percent] int 'percent',
           [appliance_ip] nvarchar(80) 'appliance_ip',
           [subnet_ip] nvarchar(80) 'subnet_ip',
           [subnet_mask] nvarchar(80) 'subnet_mask',
           [vm_external_ip] nvarchar(80) 'vm_external_ip',
           [state] int 'state',
           [order_num] int 'order_num',
           [control] int 'control',
           [leave_powered_on] bit 'leave_powered_on',
           [oiag_id] uniqueidentifier 'oiag_id',
           [restore_counter] int 'restore_counter',
           [db_instance_id] uniqueidentifier 'db_instance_id',
		   [validation_status] int 'validation_status'
        )
        EXEC sp_xml_removedocument @idoc
        
        /*             ADD DBID  FIELD     */
        UPDATE @imported_changes
        SET db_instance_id= @dbInstanceId
        
        /*             TIMEZONE UPDATES     */
        UPDATE @imported_changes
        SET
        [start_time] = DATEADD( hour, -@TimeZoneShift, [start_time]),
        [finish_time] = DATEADD( hour, -@TimeZoneShift, [finish_time])
        /*      UPDATE EXISTING ROWS        */
        UPDATE [C.Backup.Model.SbTaskSessions]
        SET
            [C.Backup.Model.SbTaskSessions].[drsession_id] = src.[drsession_id],
            [C.Backup.Model.SbTaskSessions].[type] = src.[type],
            [C.Backup.Model.SbTaskSessions].[object_name] = src.[object_name],
            [C.Backup.Model.SbTaskSessions].[object_id] = src.[object_id],
            [C.Backup.Model.SbTaskSessions].[oib_id] = src.[oib_id],
            [C.Backup.Model.SbTaskSessions].[overall_status] = src.[overall_status],
            [C.Backup.Model.SbTaskSessions].[description] = src.[description],
            [C.Backup.Model.SbTaskSessions].[start_time] = src.[start_time],
            [C.Backup.Model.SbTaskSessions].[finish_time] = src.[finish_time],
            [C.Backup.Model.SbTaskSessions].[total_steps] = src.[total_steps],
            [C.Backup.Model.SbTaskSessions].[processed_steps] = src.[processed_steps],
            [C.Backup.Model.SbTaskSessions].[powerOn_status] = src.[powerOn_status],
            [C.Backup.Model.SbTaskSessions].[heartbeat_status] = src.[heartbeat_status],
            [C.Backup.Model.SbTaskSessions].[ping_status] = src.[ping_status],
            [C.Backup.Model.SbTaskSessions].[test_script_status] = src.[test_script_status],
            [C.Backup.Model.SbTaskSessions].[powerOff_status] = src.[powerOff_status],
            [C.Backup.Model.SbTaskSessions].[test_script_error_code] = src.[test_script_error_code],
            [C.Backup.Model.SbTaskSessions].[vm_ref] = src.[vm_ref],
            [C.Backup.Model.SbTaskSessions].[percent] = src.[percent],
            [C.Backup.Model.SbTaskSessions].[appliance_ip] = src.[appliance_ip],
            [C.Backup.Model.SbTaskSessions].[subnet_ip] = src.[subnet_ip],
            [C.Backup.Model.SbTaskSessions].[subnet_mask] = src.[subnet_mask],
            [C.Backup.Model.SbTaskSessions].[vm_external_ip] = src.[vm_external_ip],
            [C.Backup.Model.SbTaskSessions].[state] = src.[state],
            [C.Backup.Model.SbTaskSessions].[order_num] = src.[order_num],
            [C.Backup.Model.SbTaskSessions].[control] = src.[control],
            [C.Backup.Model.SbTaskSessions].[leave_powered_on] = src.[leave_powered_on],
            [C.Backup.Model.SbTaskSessions].[oiag_id] = src.[oiag_id],
            [C.Backup.Model.SbTaskSessions].[restore_counter] = src.[restore_counter],
            [C.Backup.Model.SbTaskSessions].[db_instance_id] = src.[db_instance_id],
			[C.Backup.Model.SbTaskSessions].[validation_status] = src.[validation_status]			
        FROM [C.Backup.Model.SbTaskSessions] INNER JOIN @imported_changes src
        ON [C.Backup.Model.SbTaskSessions].id = src.id
        
        /*      INSERT NEW ROWS       */
        INSERT INTO [C.Backup.Model.SbTaskSessions]([id],[drsession_id],[type],[object_name],[object_id],[oib_id],[overall_status],[description],[start_time],[finish_time],[total_steps],[processed_steps],[powerOn_status],[heartbeat_status],[ping_status],[test_script_status],[powerOff_status],[test_script_error_code],[vm_ref],[percent],[appliance_ip],[subnet_ip],[subnet_mask],[vm_external_ip],[state],[order_num],[control],[leave_powered_on],[oiag_id],[restore_counter],[db_instance_id],[validation_status])
        SELECT
            src.[id],
            src.[drsession_id],
            src.[type],
            src.[object_name],
            src.[object_id],
            src.[oib_id],
            src.[overall_status],
            src.[description],
            src.[start_time],
            src.[finish_time],
            src.[total_steps],
            src.[processed_steps],
            src.[powerOn_status],
            src.[heartbeat_status],
            src.[ping_status],
            src.[test_script_status],
            src.[powerOff_status],
            src.[test_script_error_code],
            src.[vm_ref],
            src.[percent],
            src.[appliance_ip],
            src.[subnet_ip],
            src.[subnet_mask],
            src.[vm_external_ip],
            src.[state],
            src.[order_num],
            src.[control],
            src.[leave_powered_on],
            src.[oiag_id],
            src.[restore_counter],
            src.[db_instance_id],
			src.[validation_status]
        FROM @imported_changes src LEFT OUTER JOIN [C.Backup.Model.SbTaskSessions] trg
        ON src.id = trg.id
        WHERE trg.id IS NULL
    END
GO

/* DELS IMPORT*/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_Import_Backup_Model_SbTaskSessions_Dels]') AND type in (N'P'))
   BEGIN
       DROP PROCEDURE [dbo].[usp_Import_Backup_Model_SbTaskSessions_Dels]
   END
GO
    CREATE PROCEDURE [dbo].[usp_Import_Backup_Model_SbTaskSessions_Dels]
        @deleted_objects_xml XML,
	@dbInstanceId uniqueidentifier
    AS
    BEGIN
        SET NOCOUNT ON;
        DECLARE @deleted_objects_table table
        (
           obj_id  uniqueidentifier
        )
        
        /*                importing deleted objects set   */
        DECLARE @idoc  int
        EXEC sp_xml_preparedocument @idoc OUTPUT, @deleted_objects_xml
        INSERT INTO @deleted_objects_table( obj_id )
        SELECT *
        FROM OPENXML( @idoc, N'/root/object',1 )
        WITH
        (
           obj_id uniqueidentifier '@id'
        )
        EXEC sp_xml_removedocument @idoc
        
        DELETE FROM [dbo].[C.Backup.Model.SbTaskSessions]
        WHERE id IN ( SELECT obj_id FROM @deleted_objects_table )
    END
GO



/*======== REPLICATED TABLE: C.Backup.Model.SbSessions*/

/* UPDATES IMPORT*/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_Import_Backup_Model_SbSessions_Changes]') AND type in (N'P'))
   BEGIN
       DROP PROCEDURE [dbo].[usp_Import_Backup_Model_SbSessions_Changes]
   END
GO
    CREATE PROCEDURE [dbo].[usp_Import_Backup_Model_SbSessions_Changes]
         @dbInstanceId uniqueidentifier,
         @timeZoneShift int,
         @importDoc XML
    AS
    BEGIN
        SET NOCOUNT ON;
        DECLARE @imported_changes table
        (
           [id] uniqueidentifier,
           [preferred_date] datetime,
           [db_instance_id] uniqueidentifier
        )
        
        /*              IMPORT XML DOCUMENT       */
        DECLARE @idoc int
        EXEC sp_xml_preparedocument @idoc OUTPUT, @importDoc
        
        INSERT INTO @imported_changes([id],[preferred_date],[db_instance_id])
        SELECT *
        FROM OPENXML( @idoc, '/Root/row', 2)
        WITH
        (
           [id] uniqueidentifier 'id',
           [preferred_date] datetime 'preferred_date',
           [db_instance_id] uniqueidentifier 'db_instance_id'
        )
        EXEC sp_xml_removedocument @idoc
        
        /*             ADD DBID  FIELD     */
        UPDATE @imported_changes
        SET db_instance_id= @dbInstanceId
        
        /*             TIMEZONE UPDATES     */
        UPDATE @imported_changes
        SET
        [preferred_date] = DATEADD( hour, -@TimeZoneShift, [preferred_date])
        /*      UPDATE EXISTING ROWS        */
        UPDATE [C.Backup.Model.SbSessions]
        SET
            [C.Backup.Model.SbSessions].[preferred_date] = src.[preferred_date],
            [C.Backup.Model.SbSessions].[db_instance_id] = src.[db_instance_id]
        FROM [C.Backup.Model.SbSessions] INNER JOIN @imported_changes src
        ON [C.Backup.Model.SbSessions].id = src.id
        
        /*      INSERT NEW ROWS       */
        INSERT INTO [C.Backup.Model.SbSessions]([id],[preferred_date],[db_instance_id])
        SELECT
            src.[id],
            src.[preferred_date],
            src.[db_instance_id]
        FROM @imported_changes src LEFT OUTER JOIN [C.Backup.Model.SbSessions] trg
        ON src.id = trg.id
        WHERE trg.id IS NULL
    END
GO

/* DELS IMPORT*/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_Import_Backup_Model_SbSessions_Dels]') AND type in (N'P'))
   BEGIN
       DROP PROCEDURE [dbo].[usp_Import_Backup_Model_SbSessions_Dels]
   END
GO
    CREATE PROCEDURE [dbo].[usp_Import_Backup_Model_SbSessions_Dels]
        @deleted_objects_xml XML,
	@dbInstanceId uniqueidentifier
    AS
    BEGIN
        SET NOCOUNT ON;
        DECLARE @deleted_objects_table table
        (
           obj_id  uniqueidentifier
        )
        
        /*                importing deleted objects set   */
        DECLARE @idoc  int
        EXEC sp_xml_preparedocument @idoc OUTPUT, @deleted_objects_xml
        INSERT INTO @deleted_objects_table( obj_id )
        SELECT *
        FROM OPENXML( @idoc, N'/root/object',1 )
        WITH
        (
           obj_id uniqueidentifier '@id'
        )
        EXEC sp_xml_removedocument @idoc
        
        DELETE FROM [dbo].[C.Backup.Model.SbSessions]
        WHERE id IN ( SELECT obj_id FROM @deleted_objects_table )
    END
GO



/*======== REPLICATED TABLE: C.Backup.Model.BackupJobSessions*/

/* UPDATES IMPORT*/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_Import_Backup_Model_BackupJobSessions_Changes]') AND type in (N'P'))
   BEGIN
       DROP PROCEDURE [dbo].[usp_Import_Backup_Model_BackupJobSessions_Changes]
   END
GO
    CREATE PROCEDURE [dbo].[usp_Import_Backup_Model_BackupJobSessions_Changes]
         @dbInstanceId uniqueidentifier,
         @timeZoneShift int,
         @importDoc XML
    AS
    BEGIN
        SET NOCOUNT ON;
        DECLARE @imported_changes table
        (
           [id] uniqueidentifier,
           [is_retry] bit,
           [total_objects] int,
           [processed_objects] int,
           [total_size] bigint,
           [processed_size] bigint,
           [job_source_type] int,
           [avg_speed] bigint,
           [is_startfull] bit,
           [db_instance_id] uniqueidentifier,
           [stored_size] bigint,
		   [cur_point_id] uniqueidentifier
        )
        
        /*              IMPORT XML DOCUMENT       */
        DECLARE @idoc int
        EXEC sp_xml_preparedocument @idoc OUTPUT, @importDoc
        
        INSERT INTO @imported_changes([id],[is_retry],[total_objects],[processed_objects],[total_size],[processed_size],[job_source_type],[avg_speed],[is_startfull],[db_instance_id],[stored_size],[cur_point_id])
        SELECT *
        FROM OPENXML( @idoc, '/Root/row', 2)
        WITH
        (
           [id] uniqueidentifier 'id',
           [is_retry] bit 'is_retry',
           [total_objects] int 'total_objects',
           [processed_objects] int 'processed_objects',
           [total_size] bigint 'total_size',
           [processed_size] bigint 'processed_size',
           [job_source_type] int 'job_source_type',
           [avg_speed] bigint 'avg_speed',
           [is_startfull] bit 'is_startfull',
           [db_instance_id] uniqueidentifier 'db_instance_id',
           [stored_size] bigint 'stored_size',
		   [cur_point_id] uniqueidentifier 'cur_point_id'
        )
        EXEC sp_xml_removedocument @idoc
        
        /*             ADD DBID  FIELD     */
        UPDATE @imported_changes
        SET db_instance_id= @dbInstanceId
        
        /*             TIMEZONE UPDATES     */
        /*      UPDATE EXISTING ROWS        */
        UPDATE [C.Backup.Model.BackupJobSessions]
        SET
            [C.Backup.Model.BackupJobSessions].[is_retry] = src.[is_retry],
            [C.Backup.Model.BackupJobSessions].[total_objects] = src.[total_objects],
            [C.Backup.Model.BackupJobSessions].[processed_objects] = src.[processed_objects],
            [C.Backup.Model.BackupJobSessions].[total_size] = src.[total_size],
            [C.Backup.Model.BackupJobSessions].[processed_size] = src.[processed_size],
            [C.Backup.Model.BackupJobSessions].[job_source_type] = src.[job_source_type],
            [C.Backup.Model.BackupJobSessions].[avg_speed] = src.[avg_speed],
            [C.Backup.Model.BackupJobSessions].[is_startfull] = src.[is_startfull],
            [C.Backup.Model.BackupJobSessions].[db_instance_id] = src.[db_instance_id],
            [C.Backup.Model.BackupJobSessions].[stored_size] = src.[stored_size],
            [C.Backup.Model.BackupJobSessions].[cur_point_id] = src.[cur_point_id]
        FROM [C.Backup.Model.BackupJobSessions] INNER JOIN @imported_changes src
        ON [C.Backup.Model.BackupJobSessions].id = src.id
        
        /*      INSERT NEW ROWS       */
        INSERT INTO [C.Backup.Model.BackupJobSessions]([id],[is_retry],[total_objects],[processed_objects],[total_size],[processed_size],[job_source_type],[avg_speed],[is_startfull],[db_instance_id],[stored_size],[cur_point_id])
        SELECT
            src.[id],
            src.[is_retry],
            src.[total_objects],
            src.[processed_objects],
            src.[total_size],
            src.[processed_size],
            src.[job_source_type],
            src.[avg_speed],
            src.[is_startfull],
            src.[db_instance_id],
            src.[stored_size],
            src.[cur_point_id]
        FROM @imported_changes src LEFT OUTER JOIN [C.Backup.Model.BackupJobSessions] trg
        ON src.id = trg.id
        WHERE trg.id IS NULL
    END
GO

/* DELS IMPORT*/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_Import_Backup_Model_BackupJobSessions_Dels]') AND type in (N'P'))
   BEGIN
       DROP PROCEDURE [dbo].[usp_Import_Backup_Model_BackupJobSessions_Dels]
   END
GO
    CREATE PROCEDURE [dbo].[usp_Import_Backup_Model_BackupJobSessions_Dels]
        @deleted_objects_xml XML,
	@dbInstanceId uniqueidentifier
    AS
    BEGIN
        SET NOCOUNT ON;
        DECLARE @deleted_objects_table table
        (
           obj_id  uniqueidentifier
        )
        
        /*                importing deleted objects set   */
        DECLARE @idoc  int
        EXEC sp_xml_preparedocument @idoc OUTPUT, @deleted_objects_xml
        INSERT INTO @deleted_objects_table( obj_id )
        SELECT *
        FROM OPENXML( @idoc, N'/root/object',1 )
        WITH
        (
           obj_id uniqueidentifier '@id'
        )
        EXEC sp_xml_removedocument @idoc
        
        DELETE FROM [dbo].[C.Backup.Model.BackupJobSessions]
        WHERE id IN ( SELECT obj_id FROM @deleted_objects_table )
    END
GO


/*======== REPLICATED TABLE: C.Backup.Model.BackupTaskSessions*/

/* UPDATES IMPORT*/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_Import_Backup_Model_BackupTaskSessions_Changes]') AND type in (N'P'))
   BEGIN
       DROP PROCEDURE [dbo].[usp_Import_Backup_Model_BackupTaskSessions_Changes]
   END
GO
     CREATE PROCEDURE [dbo].[usp_Import_Backup_Model_BackupTaskSessions_Changes]
         @dbInstanceId uniqueidentifier,
         @timeZoneShift int,
         @importDoc XML
    AS
    BEGIN
        SET NOCOUNT ON;
        DECLARE @imported_changes table
        (
           [id] uniqueidentifier,
           [session_id] uniqueidentifier,
           [creation_time] datetime,
           [object_name] nvarchar(2000),
           [status] int,
           [reason] ntext,
           [object_id] uniqueidentifier,
           [end_time] datetime,
           [operation] nvarchar(400),
           [total_objects] int,
           [processed_objects] int,
           [total_size] bigint,
           [processed_size] bigint,
           [mode] int,
           [avg_speed] bigint,
           [change_tracking] bit,
           [db_instance_id] uniqueidentifier,
           [log_xml] xml,
           [read_size] bigint,
           [stored_size] bigint 
        )
        
        /*              IMPORT XML DOCUMENT       */
        DECLARE @idoc int
        EXEC sp_xml_preparedocument @idoc OUTPUT, @importDoc
        
        INSERT INTO @imported_changes([id],[session_id],[creation_time],[object_name],[status],[reason],[object_id],[end_time],[operation],
        [total_objects],[processed_objects],[total_size],[processed_size],[mode],[avg_speed],[change_tracking],[db_instance_id], [log_xml], 
        [read_size], [stored_size] )
        SELECT *
        FROM OPENXML( @idoc, '/Root/row', 2)
        WITH
        (
           [id] uniqueidentifier 'id',
           [session_id] uniqueidentifier 'session_id',
           [creation_time] datetime 'creation_time',
           [object_name] nvarchar(2000) 'object_name',
           [status] int 'status',
           [reason] ntext 'reason',
           [object_id] uniqueidentifier 'object_id',
           [end_time] datetime 'end_time',
           [operation] nvarchar(400) 'operation',
           [total_objects] int 'total_objects',
           [processed_objects] int 'processed_objects',
           [total_size] bigint 'total_size',
           [processed_size] bigint 'processed_size',
           [mode] int 'mode',
           [avg_speed] bigint 'avg_speed',
           [change_tracking] bit 'change_tracking',
           [db_instance_id] uniqueidentifier 'db_instance_id',
           [log_xml] xml 'log_xml',
           [read_size] bigint 'read_size',
           [stored_size] bigint 'stored_size'
        )
        EXEC sp_xml_removedocument @idoc
        
        /*             ADD DBID  FIELD     */
        UPDATE @imported_changes
        SET db_instance_id= @dbInstanceId
        
        /*             TIMEZONE UPDATES     */
        UPDATE @imported_changes
        SET
        [creation_time] = DATEADD( hour, -@TimeZoneShift, [creation_time]),
        [end_time] = DATEADD( hour, -@TimeZoneShift, [end_time])
        /*      UPDATE EXISTING ROWS        */
        UPDATE [C.Backup.Model.BackupTaskSessions]
        SET
            [C.Backup.Model.BackupTaskSessions].[session_id] = src.[session_id],
            [C.Backup.Model.BackupTaskSessions].[creation_time] = src.[creation_time],
            [C.Backup.Model.BackupTaskSessions].[object_name] = src.[object_name],
            [C.Backup.Model.BackupTaskSessions].[status] = src.[status],
            [C.Backup.Model.BackupTaskSessions].[reason] = src.[reason],
            [C.Backup.Model.BackupTaskSessions].[object_id] = src.[object_id],
            [C.Backup.Model.BackupTaskSessions].[end_time] = src.[end_time],
            [C.Backup.Model.BackupTaskSessions].[operation] = src.[operation],
            [C.Backup.Model.BackupTaskSessions].[total_objects] = src.[total_objects],
            [C.Backup.Model.BackupTaskSessions].[processed_objects] = src.[processed_objects],
            [C.Backup.Model.BackupTaskSessions].[total_size] = src.[total_size],
            [C.Backup.Model.BackupTaskSessions].[processed_size] = src.[processed_size],
            [C.Backup.Model.BackupTaskSessions].[mode] = src.[mode],
            [C.Backup.Model.BackupTaskSessions].[avg_speed] = src.[avg_speed],
            [C.Backup.Model.BackupTaskSessions].[change_tracking] = src.[change_tracking],
            [C.Backup.Model.BackupTaskSessions].[db_instance_id] = src.[db_instance_id],
            [C.Backup.Model.BackupTaskSessions].[log_xml] = src.[log_xml],
            [C.Backup.Model.BackupTaskSessions].[read_size] = src.[read_size],
            [C.Backup.Model.BackupTaskSessions].[stored_size] = src.[stored_size]
        FROM [C.Backup.Model.BackupTaskSessions] INNER JOIN @imported_changes src
        ON [C.Backup.Model.BackupTaskSessions].id = src.id
        
        /*      INSERT NEW ROWS       */
        INSERT INTO [C.Backup.Model.BackupTaskSessions]([id],[session_id],[creation_time],[object_name],[status],[reason],
        [object_id],[end_time],[operation],[total_objects],[processed_objects],[total_size],[processed_size],[mode],
        [avg_speed],[change_tracking],[db_instance_id], [log_xml], [read_size], [stored_size] )
        SELECT
            src.[id],
            src.[session_id],
            src.[creation_time],
            src.[object_name],
            src.[status],
            src.[reason],
            src.[object_id],
            src.[end_time],
            src.[operation],
            src.[total_objects],
            src.[processed_objects],
            src.[total_size],
            src.[processed_size],
            src.[mode],
            src.[avg_speed],
            src.[change_tracking],
            src.[db_instance_id],
            src.[log_xml],
            src.[read_size],
            src.[stored_size]
        FROM @imported_changes src LEFT OUTER JOIN [C.Backup.Model.BackupTaskSessions] trg
        ON src.id = trg.id
        WHERE trg.id IS NULL
    END
GO

/* DELS IMPORT*/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_Import_Backup_Model_BackupTaskSessions_Dels]') AND type in (N'P'))
   BEGIN
       DROP PROCEDURE [dbo].[usp_Import_Backup_Model_BackupTaskSessions_Dels]
   END
GO
    CREATE PROCEDURE [dbo].[usp_Import_Backup_Model_BackupTaskSessions_Dels]
        @deleted_objects_xml XML,
	@dbInstanceId uniqueidentifier
    AS
    BEGIN
        SET NOCOUNT ON;
        DECLARE @deleted_objects_table table
        (
           obj_id  uniqueidentifier
        )
        
        /*                importing deleted objects set   */
        DECLARE @idoc  int
        EXEC sp_xml_preparedocument @idoc OUTPUT, @deleted_objects_xml
        INSERT INTO @deleted_objects_table( obj_id )
        SELECT *
        FROM OPENXML( @idoc, N'/root/object',1 )
        WITH
        (
           obj_id uniqueidentifier '@id'
        )
        EXEC sp_xml_removedocument @idoc
        
        DELETE FROM [dbo].[C.Backup.Model.BackupTaskSessions]
        WHERE id IN ( SELECT obj_id FROM @deleted_objects_table )
    END
GO



/*======== REPLICATED TABLE: C.LinkedJobs*/

/* UPDATES IMPORT*/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_Import_LinkedJobs_Changes]') AND type in (N'P'))
   BEGIN
       DROP PROCEDURE [dbo].[usp_Import_LinkedJobs_Changes]
   END
GO
    CREATE PROCEDURE [dbo].[usp_Import_LinkedJobs_Changes]
         @dbInstanceId uniqueidentifier,
         @timeZoneShift int,
         @importDoc XML
    AS
    BEGIN
        SET NOCOUNT ON;
        DECLARE @imported_changes table
        (
           [id] uniqueidentifier,
           [job_id] uniqueidentifier,
           [linked_job_id] uniqueidentifier,
           [db_instance_id] uniqueidentifier
        )
        
        /*              IMPORT XML DOCUMENT       */
        DECLARE @idoc int
        EXEC sp_xml_preparedocument @idoc OUTPUT, @importDoc
        
        INSERT INTO @imported_changes([id],[job_id],[linked_job_id],[db_instance_id])
        SELECT *
        FROM OPENXML( @idoc, '/Root/row', 2)
        WITH
        (
           [id] uniqueidentifier 'id',
           [job_id] uniqueidentifier 'job_id',
           [linked_job_id] uniqueidentifier 'linked_job_id',
           [db_instance_id] uniqueidentifier 'db_instance_id'
        )
        EXEC sp_xml_removedocument @idoc
        
        /*             ADD DBID  FIELD     */
        UPDATE @imported_changes
        SET db_instance_id= @dbInstanceId
        
        /*             TIMEZONE UPDATES     */
        /*      UPDATE EXISTING ROWS        */
        UPDATE [C.LinkedJobs]
        SET
            [C.LinkedJobs].[job_id] = src.[job_id],
            [C.LinkedJobs].[linked_job_id] = src.[linked_job_id],
            [C.LinkedJobs].[db_instance_id] = src.[db_instance_id]
        FROM [C.LinkedJobs] INNER JOIN @imported_changes src
        ON [C.LinkedJobs].id = src.id
        
        /*      INSERT NEW ROWS       */
        INSERT INTO [C.LinkedJobs]([id],[job_id],[linked_job_id],[db_instance_id])
        SELECT
            src.[id],
            src.[job_id],
            src.[linked_job_id],
            src.[db_instance_id]
        FROM @imported_changes src LEFT OUTER JOIN [C.LinkedJobs] trg
        ON src.id = trg.id
        WHERE trg.id IS NULL
    END
GO

/* DELS IMPORT*/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_Import_LinkedJobs_Dels]') AND type in (N'P'))
   BEGIN
       DROP PROCEDURE [dbo].[usp_Import_LinkedJobs_Dels]
   END
GO
    CREATE PROCEDURE [dbo].[usp_Import_LinkedJobs_Dels]
        @deleted_objects_xml XML,
	@dbInstanceId uniqueidentifier
    AS
    BEGIN
        SET NOCOUNT ON;
        DECLARE @deleted_objects_table table
        (
           obj_id  uniqueidentifier
        )
        
        /*                importing deleted objects set   */
        DECLARE @idoc  int
        EXEC sp_xml_preparedocument @idoc OUTPUT, @deleted_objects_xml
        INSERT INTO @deleted_objects_table( obj_id )
        SELECT *
        FROM OPENXML( @idoc, N'/root/object',1 )
        WITH
        (
           obj_id uniqueidentifier '@id'
        )
        EXEC sp_xml_removedocument @idoc
        
        DELETE FROM [dbo].[C.LinkedJobs]
        WHERE id IN ( SELECT obj_id FROM @deleted_objects_table )
    END
GO


/*======== REPLICATED TABLE: C.Backup.Model.RestoreJobSessions*/

/* UPDATES IMPORT*/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_Import_Backup_Model_RestoreJobSessions_Changes]') AND type in (N'P'))
   BEGIN
       DROP PROCEDURE [dbo].[usp_Import_Backup_Model_RestoreJobSessions_Changes]
   END
GO
   CREATE PROCEDURE [dbo].[usp_Import_Backup_Model_RestoreJobSessions_Changes]
         @dbInstanceId uniqueidentifier,
         @timeZoneShift int,
         @importDoc XML
    AS
    BEGIN
        SET NOCOUNT ON;
        DECLARE @imported_changes table
        (
           [id] uniqueidentifier
		  ,[action] int  
		  ,[usn] bigint
		  ,[options] xml
		  ,[initiator_sid] nvarchar(255)
		  ,[initiator_name] nvarchar(255)
		  ,[files_log_xml] xml
		  ,[platform] int
		  ,[multi_restore_id] uniqueidentifier
		  ,[restore_type] int
		  ,[oib_id] uniqueidentifier
		  ,[is_internal] bit
		  ,[oib_display_name] nvarchar(255)
		  ,[oib_creation_time] datetime
		  ,[sub_type] int
		  ,[db_instance_id] uniqueidentifier
        )
        
        /*              IMPORT XML DOCUMENT       */
        DECLARE @idoc int
        EXEC sp_xml_preparedocument @idoc OUTPUT, @importDoc
        
        INSERT INTO @imported_changes([id],[action],[usn],[options],[initiator_sid],[initiator_name],[files_log_xml],[platform],[multi_restore_id],[restore_type],[oib_id],[is_internal],[oib_display_name], [oib_creation_time],[sub_type], [db_instance_id])
        SELECT *
        FROM OPENXML( @idoc, '/Root/row', 2)
        WITH
        (
		   [id] uniqueidentifier 'id'
		  ,[action] int  'action'
		  ,[usn] bigint 'usn'
		  ,[options] xml 'options'
		  ,[initiator_sid] nvarchar(255) 'initiator_sid'
		  ,[initiator_name] nvarchar(255) 'initiator_name'
		  ,[files_log_xml] xml 'files_log_xml'
		  ,[platform] int 'platform'
		  ,[multi_restore_id] uniqueidentifier 'multi_restore_id'
		  ,[restore_type] int 'restore_type'
		  ,[oib_id] uniqueidentifier 'oib_id'
		  ,[is_internal] bit 'is_internal'
		  ,[oib_display_name] nvarchar(255) 'oib_display_name'
		  ,[oib_creation_time] datetime 'oib_creation_time'
		  ,[sub_type] int 'sub_type'
		  ,[db_instance_id] uniqueidentifier 'db_instance_id'
        )
        EXEC sp_xml_removedocument @idoc
        
        /*             ADD DBID  FIELD     */
        UPDATE @imported_changes
        SET db_instance_id= @dbInstanceId
        
        /*             TIMEZONE UPDATES     */
        /*      UPDATE EXISTING ROWS        */
        UPDATE [C.Backup.Model.RestoreJobSessions]
        SET
            [C.Backup.Model.RestoreJobSessions].[id] = src.[id],
            [C.Backup.Model.RestoreJobSessions].[action] = src.[action],
            [C.Backup.Model.RestoreJobSessions].[usn] = src.[usn],
            [C.Backup.Model.RestoreJobSessions].[options] = src.[options],
			[C.Backup.Model.RestoreJobSessions].[initiator_sid] = src.[initiator_sid],
            [C.Backup.Model.RestoreJobSessions].[initiator_name] = src.[initiator_name],
            [C.Backup.Model.RestoreJobSessions].[files_log_xml] = src.[files_log_xml],
            [C.Backup.Model.RestoreJobSessions].[platform] = src.[platform],
            [C.Backup.Model.RestoreJobSessions].[multi_restore_id] = src.[multi_restore_id],
            [C.Backup.Model.RestoreJobSessions].[restore_type] = src.[restore_type],
			[C.Backup.Model.RestoreJobSessions].[oib_id] = src.[oib_id],
			[C.Backup.Model.RestoreJobSessions].[is_internal] = src.[is_internal], 
			[C.Backup.Model.RestoreJobSessions].[oib_display_name] = src.[oib_display_name],
			[C.Backup.Model.RestoreJobSessions].[oib_creation_time] = src.[oib_creation_time],
			[C.Backup.Model.RestoreJobSessions].[sub_type] = src.[sub_type],
			[C.Backup.Model.RestoreJobSessions].[db_instance_id] = src.[db_instance_id]

        FROM [C.Backup.Model.RestoreJobSessions] INNER JOIN @imported_changes src
        ON [C.Backup.Model.RestoreJobSessions].id = src.id
        
        /*      INSERT NEW ROWS       */
        INSERT INTO [C.Backup.Model.RestoreJobSessions]([id],[action],[usn],[options],[initiator_sid],[initiator_name],[files_log_xml],[platform],[multi_restore_id],[restore_type],[oib_id],[is_internal],[oib_display_name], [oib_creation_time],[sub_type],[db_instance_id])
        SELECT
            src.[id],
            src.[action],
            src.[usn],
            src.[options],
			src.[initiator_sid],
            src.[initiator_name],
            src.[files_log_xml],
            src.[platform],
            src.[multi_restore_id],
            src.[restore_type],
			src.[oib_id],
			src.[is_internal],
			src.[oib_display_name], 
			src.[oib_creation_time],
			src.[sub_type],
            src.[db_instance_id]
        FROM @imported_changes src LEFT OUTER JOIN [C.Backup.Model.RestoreJobSessions] trg
        ON src.id = trg.id
        WHERE trg.id IS NULL
    END
GO

/* DELS IMPORT*/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_Import_Backup_Model_RestoreJobSessions_Dels]') AND type in (N'P'))
   BEGIN
       DROP PROCEDURE [dbo].[usp_Import_Backup_Model_RestoreJobSessions_Dels]
   END
GO
    CREATE PROCEDURE [dbo].[usp_Import_Backup_Model_RestoreJobSessions_Dels]
        @deleted_objects_xml XML,
	@dbInstanceId uniqueidentifier
    AS
    BEGIN
        SET NOCOUNT ON;
        DECLARE @deleted_objects_table table
        (
           obj_id  uniqueidentifier
        )
        
        /*                importing deleted objects set   */
        DECLARE @idoc  int
        EXEC sp_xml_preparedocument @idoc OUTPUT, @deleted_objects_xml
        INSERT INTO @deleted_objects_table( obj_id )
        SELECT *
        FROM OPENXML( @idoc, N'/root/object',1 )
        WITH
        (
           obj_id uniqueidentifier '@id'
        )
        EXEC sp_xml_removedocument @idoc
        
        DELETE FROM [dbo].[C.Backup.Model.RestoreJobSessions]
        WHERE id IN ( SELECT obj_id FROM @deleted_objects_table )
    END
GO


/*======== REPLICATED TABLE: C.Backup.Model.RestoreTaskSessions*/

/* UPDATES IMPORT*/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_Import_Backup_Model_RestoreTaskSessions_Changes]') AND type in (N'P'))
   BEGIN
       DROP PROCEDURE [dbo].[usp_Import_Backup_Model_RestoreTaskSessions_Changes]
   END
GO
   CREATE PROCEDURE [dbo].[usp_Import_Backup_Model_RestoreTaskSessions_Changes]
         @dbInstanceId uniqueidentifier,
         @timeZoneShift int,
         @importDoc XML
    AS
    BEGIN
        SET NOCOUNT ON;
        DECLARE @imported_changes table
        (
           [id] uniqueidentifier
		  ,[session_id] uniqueidentifier  
		  ,[object_name] nvarchar(255)
		  ,[object_id] uniqueidentifier
		  ,[status] int
		  ,[reason] ntext
		  ,[creation_time] datetime
		  ,[end_time] datetime
		  ,[operation] nvarchar(255)
		  ,[db_instance_id] uniqueidentifier
        )
        
        /*              IMPORT XML DOCUMENT       */
        DECLARE @idoc int
        EXEC sp_xml_preparedocument @idoc OUTPUT, @importDoc
        
        INSERT INTO @imported_changes([id],[session_id],[object_name],[object_id],[status],[reason],[creation_time],[end_time],[operation],[db_instance_id])
        SELECT *
        FROM OPENXML( @idoc, '/Root/row', 2)
        WITH
        (
		   [id] uniqueidentifier 'id'
		  ,[session_id] uniqueidentifier 'session_id'
		  ,[object_name] nvarchar(255) 'object_name'
		  ,[object_id] uniqueidentifier 'object_id'
		  ,[status] int 'status'
		  ,[reason] ntext 'reason'
		  ,[creation_time] datetime 'creation_time'
		  ,[end_time] datetime 'end_time'
		  ,[operation] nvarchar(400) 'operation'
		  ,[db_instance_id] uniqueidentifier 'db_instance_id'
        )
        EXEC sp_xml_removedocument @idoc
        
        /*             ADD DBID  FIELD     */
        UPDATE @imported_changes
        SET db_instance_id= @dbInstanceId
        
        /*             TIMEZONE UPDATES     */
        /*      UPDATE EXISTING ROWS        */
        UPDATE [C.Backup.Model.RestoreTaskSessions]
        SET
            [C.Backup.Model.RestoreTaskSessions].[id] = src.[id],
            [C.Backup.Model.RestoreTaskSessions].[session_id] = src.[session_id],
            [C.Backup.Model.RestoreTaskSessions].[object_name] = src.[object_name],
            [C.Backup.Model.RestoreTaskSessions].[object_id] = src.[object_id],
            [C.Backup.Model.RestoreTaskSessions].[status] = src.[status],
            [C.Backup.Model.RestoreTaskSessions].[reason] = src.[reason],
            [C.Backup.Model.RestoreTaskSessions].[creation_time] = src.[creation_time],
            [C.Backup.Model.RestoreTaskSessions].[end_time] = src.[end_time],
            [C.Backup.Model.RestoreTaskSessions].[operation] = src.[operation],
			[C.Backup.Model.RestoreTaskSessions].[db_instance_id] = src.[db_instance_id]

        FROM [C.Backup.Model.RestoreTaskSessions] INNER JOIN @imported_changes src
        ON [C.Backup.Model.RestoreTaskSessions].id = src.id
        
        /*      INSERT NEW ROWS       */
        INSERT INTO [C.Backup.Model.RestoreTaskSessions]([id],[session_id],[object_name],[object_id],[status],[reason],[creation_time],[end_time],[operation],[db_instance_id])
        SELECT
            src.[id],
            src.[session_id],
            src.[object_name],
            src.[object_id],
            src.[status],
            src.[reason],
            src.[creation_time],
            src.[end_time],
            src.[operation],
            src.[db_instance_id]
        FROM @imported_changes src LEFT OUTER JOIN [C.Backup.Model.RestoreTaskSessions] trg
        ON src.id = trg.id
        WHERE trg.id IS NULL
    END
GO

/* DELS IMPORT*/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_Import_Backup_Model_RestoreTaskSessions_Dels]') AND type in (N'P'))
   BEGIN
       DROP PROCEDURE [dbo].[usp_Import_Backup_Model_RestoreTaskSessions_Dels]
   END
GO
    CREATE PROCEDURE [dbo].[usp_Import_Backup_Model_RestoreTaskSessions_Dels]
        @deleted_objects_xml XML,
	@dbInstanceId uniqueidentifier
    AS
    BEGIN
        SET NOCOUNT ON;
        DECLARE @deleted_objects_table table
        (
           obj_id  uniqueidentifier
        )
        
        /*                importing deleted objects set   */
        DECLARE @idoc  int
        EXEC sp_xml_preparedocument @idoc OUTPUT, @deleted_objects_xml
        INSERT INTO @deleted_objects_table( obj_id )
        SELECT *
        FROM OPENXML( @idoc, N'/root/object',1 )
        WITH
        (
           obj_id uniqueidentifier '@id'
        )
        EXEC sp_xml_removedocument @idoc
        
        DELETE FROM [dbo].[C.Backup.Model.RestoreTaskSessions]
        WHERE id IN ( SELECT obj_id FROM @deleted_objects_table )
    END
GO

/*======== REPLICATED TABLE: C.PhysicalHosts*/

/* UPDATES IMPORT*/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_Import_PhysicalHosts_Changes]') AND type in (N'P'))
   BEGIN
       DROP PROCEDURE [dbo].[usp_Import_PhysicalHosts_Changes]
   END
GO
   CREATE PROCEDURE [dbo].[usp_Import_PhysicalHosts_Changes]
         @dbInstanceId uniqueidentifier,
         @timeZoneShift int,
         @importDoc XML
    AS
    BEGIN
        SET NOCOUNT ON;
        DECLARE @imported_changes table
        (
           [id] uniqueidentifier
		  ,[name] nvarchar(max)
		  ,[bios_uuid] nvarchar(255)
		  ,[db_instance_id] uniqueidentifier
        )
        
        /*              IMPORT XML DOCUMENT       */
        DECLARE @idoc int
        EXEC sp_xml_preparedocument @idoc OUTPUT, @importDoc
        
        INSERT INTO @imported_changes([id],[name], [bios_uuid],[db_instance_id])
        SELECT *
        FROM OPENXML( @idoc, '/Root/row', 2)
        WITH
        (
		   [id] uniqueidentifier 'id'
		  ,[name] nvarchar(max) 'name'
		  ,[bios_uuid] nvarchar(255) 'bios_uuid'
		  ,[db_instance_id] uniqueidentifier 'db_instance_id'
        )
        EXEC sp_xml_removedocument @idoc
        
        /*             ADD DBID  FIELD     */
        UPDATE @imported_changes
        SET db_instance_id= @dbInstanceId
        
        /*             TIMEZONE UPDATES     */
        /*      UPDATE EXISTING ROWS        */
        UPDATE [C.PhysicalHosts]
        SET
            [C.PhysicalHosts].[id] = src.[id],
            [C.PhysicalHosts].[name] = src.[name],
            [C.PhysicalHosts].[bios_uuid] = src.[bios_uuid],
			[C.PhysicalHosts].[db_instance_id] = src.[db_instance_id]

        FROM [C.PhysicalHosts] INNER JOIN @imported_changes src
        ON [C.PhysicalHosts].id = src.id
        
        /*      INSERT NEW ROWS       */
        INSERT INTO [C.PhysicalHosts]([id],[name],[bios_uuid],[db_instance_id])
        SELECT
            src.[id],
			src.[name],
			src.[bios_uuid],
            src.[db_instance_id]
        FROM @imported_changes src LEFT OUTER JOIN [C.PhysicalHosts] trg
        ON src.id = trg.id
        WHERE trg.id IS NULL
    END
GO

/* DELS IMPORT*/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_Import_PhysicalHosts_Dels]') AND type in (N'P'))
   BEGIN
       DROP PROCEDURE [dbo].[usp_Import_PhysicalHosts_Dels]
   END
GO
    CREATE PROCEDURE [dbo].[usp_Import_PhysicalHosts_Dels]
        @deleted_objects_xml XML,
	@dbInstanceId uniqueidentifier
    AS
    BEGIN
        SET NOCOUNT ON;
        DECLARE @deleted_objects_table table
        (
           obj_id  uniqueidentifier
        )
        
        /*                importing deleted objects set   */
        DECLARE @idoc  int
        EXEC sp_xml_preparedocument @idoc OUTPUT, @deleted_objects_xml
        INSERT INTO @deleted_objects_table( obj_id )
        SELECT *
        FROM OPENXML( @idoc, N'/root/object',1 )
        WITH
        (
           obj_id uniqueidentifier '@id'
        )
        EXEC sp_xml_removedocument @idoc
        
        DELETE FROM [dbo].[C.PhysicalHosts]
        WHERE id IN ( SELECT obj_id FROM @deleted_objects_table )
    END
GO

/*======== REPLICATED TABLE: C.BObjectsRelations*/

/* UPDATES IMPORT*/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_Import_BObjectsRelations_Changes]') AND type in (N'P'))
   BEGIN
       DROP PROCEDURE [dbo].[usp_Import_BObjectsRelations_Changes]
   END
GO
   CREATE PROCEDURE [dbo].[usp_Import_BObjectsRelations_Changes]
         @dbInstanceId uniqueidentifier,
         @timeZoneShift int,
         @importDoc XML
    AS
    BEGIN
        SET NOCOUNT ON;
        DECLARE @imported_changes table
        (
           [id] uniqueidentifier
		  ,[obj_id] uniqueidentifier
		  ,[parent_id] uniqueidentifier
		  ,[rel_type] int
		  ,[db_instance_id] uniqueidentifier
        )
        
        /*              IMPORT XML DOCUMENT       */
        DECLARE @idoc int
        EXEC sp_xml_preparedocument @idoc OUTPUT, @importDoc
        
        INSERT INTO @imported_changes([id],[obj_id],[parent_id], [rel_type], [db_instance_id])
        SELECT *
        FROM OPENXML( @idoc, '/Root/row', 2)
        WITH
        (
		   [id] uniqueidentifier 'id'
		  ,[obj_id] uniqueidentifier 'obj_id'
		  ,[parent_id] uniqueidentifier 'parent_id'
		  ,[rel_type] int 'rel_type'
		  ,[db_instance_id] uniqueidentifier 'db_instance_id'
        )
        EXEC sp_xml_removedocument @idoc
        
        /*             ADD DBID  FIELD     */
        UPDATE @imported_changes
        SET db_instance_id= @dbInstanceId
        
        /*             TIMEZONE UPDATES     */
        /*      UPDATE EXISTING ROWS        */
        UPDATE [C.BObjectsRelations]
        SET
            [C.BObjectsRelations].[id] = src.[id],
            [C.BObjectsRelations].[obj_id] = src.[obj_id],
            [C.BObjectsRelations].[parent_id] = src.[parent_id],
            [C.BObjectsRelations].[rel_type] = src.[rel_type],
			[C.BObjectsRelations].[db_instance_id] = src.[db_instance_id]

        FROM [C.BObjectsRelations] INNER JOIN @imported_changes src
        ON [C.BObjectsRelations].id = src.id
        
        /*      INSERT NEW ROWS       */
        INSERT INTO [C.BObjectsRelations]([id],[obj_id],[parent_id], [rel_type],[db_instance_id])
        SELECT
            src.[id],
		    src.[obj_id],
		    src.[parent_id],
		    src.[rel_type],
		    src.[db_instance_id]
        FROM @imported_changes src LEFT OUTER JOIN [C.BObjectsRelations] trg
        ON src.id = trg.id
        WHERE trg.id IS NULL
    END
GO

/* DELS IMPORT*/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_Import_BObjectsRelations_Dels]') AND type in (N'P'))
   BEGIN
       DROP PROCEDURE [dbo].[usp_Import_BObjectsRelations_Dels]
   END
GO
    CREATE PROCEDURE [dbo].[usp_Import_BObjectsRelations_Dels]
        @deleted_objects_xml XML,
	@dbInstanceId uniqueidentifier
    AS
    BEGIN
        SET NOCOUNT ON;
        DECLARE @deleted_objects_table table
        (
           obj_id  uniqueidentifier
        )
        
        /*                importing deleted objects set   */
        DECLARE @idoc  int
        EXEC sp_xml_preparedocument @idoc OUTPUT, @deleted_objects_xml
        INSERT INTO @deleted_objects_table( obj_id )
        SELECT *
        FROM OPENXML( @idoc, N'/root/object',1 )
        WITH
        (
           obj_id uniqueidentifier '@id'
        )
        EXEC sp_xml_removedocument @idoc
        
        DELETE FROM [dbo].[C.BObjectsRelations]
        WHERE id IN ( SELECT obj_id FROM @deleted_objects_table )
    END
GO


-----------------------------------------------------
--[dbo].[usp_Import_BackupProxies_Changes]
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_Import_BackupProxies_Changes]') AND type in (N'P'))
     DROP PROCEDURE [dbo].[usp_Import_BackupProxies_Changes]
GO
CREATE PROCEDURE [dbo].[usp_Import_BackupProxies_Changes]
     @dbInstanceId uniqueidentifier,
     @timeZoneShift int,
     @importDoc XML
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @imported_changes table
    (
       [id] uniqueidentifier,
       [name] nvarchar(max),
       [description] nvarchar(max),
       [type] int,
       [options] xml,
       [disabled] bit,
       [is_busy] bit,
       [is_unavailable] bit,
       [db_instance_id] uniqueidentifier,
	   [unique_id] uniqueidentifier
    )

    /*              IMPORT XML DOCUMENT      */
    DECLARE @idoc int
    EXEC sp_xml_preparedocument @idoc OUTPUT, @importDoc

    INSERT INTO @imported_changes([id], [name], [description], [type], [options], [disabled], [is_busy], [is_unavailable], [unique_id])
    SELECT *
    FROM OPENXML( @idoc, '/Root/row', 2)
    WITH
    (
       [id] uniqueidentifier 'id',
       [name] nvarchar(max) 'name',
       [description] nvarchar(max) 'description',
       [type] int 'type',
       [options] xml 'options',
       [disabled] bit 'disabled',
       [is_busy] bit 'is_busy',
       [is_unavailable] bit 'is_unavailable',
	   [unique_id] uniqueidentifier 'unique_id'
    )
    EXEC sp_xml_removedocument @idoc


    /*             ADD DBID  FIELD          */
    UPDATE @imported_changes
    SET db_instance_id= @dbInstanceId

    /*             TIMEZONE UPDATES         */
    /*             UPDATE EXISTING ROWS     */
    UPDATE [C.BackupProxies]
    SET
        [C.BackupProxies].[name] = src.[name],
        [C.BackupProxies].[description] = src.[description],
        [C.BackupProxies].[type] = src.[type],
        [C.BackupProxies].[options] = src.[options],
        [C.BackupProxies].[disabled] = src.[disabled],
        [C.BackupProxies].[is_busy] = src.[is_busy],
        [C.BackupProxies].[is_unavailable] = src.[is_unavailable]
    FROM [C.BackupProxies] INNER JOIN @imported_changes src
    ON [C.BackupProxies].[unique_id] = src.[unique_id]

    /*      INSERT NEW ROWS       */
    INSERT INTO [C.BackupProxies]([id], [name], [description], [type], [options], [disabled], [is_busy], [is_unavailable], db_instance_id, [unique_id])
    SELECT
        src.[id],
        src.[name],
        src.[description],
        src.[type],
        src.[options],
        src.[disabled],
        src.[is_busy],
        src.[is_unavailable],
        src.db_instance_id,
		src.[unique_id]
    FROM @imported_changes src LEFT OUTER JOIN [C.BackupProxies] trg
    ON src.[unique_id] = trg.[unique_id]
    WHERE trg.[unique_id] IS NULL
END
GO
-----------------------------------------------------




-----------------------------------------------------
--[dbo].[usp_Import_BackupProxies_Dels]
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_Import_BackupProxies_Dels]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp_Import_BackupProxies_Dels]
GO
CREATE PROCEDURE [dbo].[usp_Import_BackupProxies_Dels]
    @deleted_objects_xml XML,
	@dbInstanceId uniqueidentifier
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @deleted_objects_table table
    (
       obj_id  uniqueidentifier
    )

    /* importing deleted objects set  */
    DECLARE @idoc  int
    EXEC sp_xml_preparedocument @idoc OUTPUT, @deleted_objects_xml
    INSERT INTO @deleted_objects_table( obj_id )
    SELECT *
    FROM OPENXML( @idoc, N'/root/object',1 )
    WITH
    (
       obj_id uniqueidentifier '@id'
    )
    EXEC sp_xml_removedocument @idoc

    DELETE FROM [dbo].[C.BackupProxies]
    WHERE 
		db_instance_id = @dbInstanceId AND
		id IN ( SELECT obj_id FROM @deleted_objects_table )
END
GO
-----------------------------------------------------


-----------------------------------------------------
--[dbo].[usp_Import_BackupRepositories_Changes]
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_Import_BackupRepositories_Changes]') AND type in (N'P'))
     DROP PROCEDURE [dbo].[usp_Import_BackupRepositories_Changes]
GO
CREATE PROCEDURE [dbo].[usp_Import_BackupRepositories_Changes]
     @dbInstanceId uniqueidentifier,
     @timeZoneShift int,
     @importDoc XML
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @imported_changes table
    (
       [id] uniqueidentifier,
       [name] nvarchar(max),
       [description] nvarchar(max),
       [type] int,
       [host_id] uniqueidentifier,
       [mount_host_id] uniqueidentifier,
       [path] nvarchar(max),
       [options] xml,
       [is_busy] bit,
       [is_unavailable] bit,
       [is_full] bit,
       [total_space] bigint,
       [free_space] bigint,
       [unique_id] uniqueidentifier,
       [db_instance_id] uniqueidentifier
    )

    /*              IMPORT XML DOCUMENT      */
    DECLARE @idoc int
    EXEC sp_xml_preparedocument @idoc OUTPUT, @importDoc

    INSERT INTO @imported_changes([id], [name], [description], [type], [host_id], [mount_host_id], [path], [options], [is_busy], [is_unavailable], [is_full], [total_space], [free_space], [unique_id])
    SELECT *
    FROM OPENXML( @idoc, '/Root/row', 2)
    WITH
    (
       [id] uniqueidentifier 'id',
       [name] nvarchar(max) 'name',
       [description] nvarchar(max) 'description',
       [type] int 'type',
       [host_id] uniqueidentifier 'host_id',
       [mount_host_id] uniqueidentifier 'mount_host_id',
       [path] nvarchar(max) 'path',
       [options] xml 'options',
       [is_busy] bit 'is_busy',
       [is_unavailable] bit 'is_unavailable',
       [is_full] bit 'is_full',
       [total_space] bigint 'total_space',
       [free_space] bigint 'free_space',
       [unique_id] uniqueidentifier 'unique_id'
    )
    EXEC sp_xml_removedocument @idoc


    /*             ADD DBID  FIELD          */
    UPDATE @imported_changes
    SET db_instance_id= @dbInstanceId

    /*             TIMEZONE UPDATES         */
    /*             UPDATE EXISTING ROWS     */
    UPDATE [C.BackupRepositories]
    SET
        [C.BackupRepositories].[name] = src.[name],
        [C.BackupRepositories].[description] = src.[description],
        [C.BackupRepositories].[type] = src.[type],
        [C.BackupRepositories].[host_id] = src.[host_id],
        [C.BackupRepositories].[mount_host_id] = src.[mount_host_id],
        [C.BackupRepositories].[path] = src.[path],
        [C.BackupRepositories].[options] = src.[options],
        [C.BackupRepositories].[is_busy] = src.[is_busy],
        [C.BackupRepositories].[is_unavailable] = src.[is_unavailable],
        [C.BackupRepositories].[is_full] = src.[is_full],
        [C.BackupRepositories].[total_space] = src.[total_space],
        [C.BackupRepositories].[free_space] = src.[free_space],
        [C.BackupRepositories].[unique_id] = src.[unique_id]
    FROM [C.BackupRepositories] INNER JOIN @imported_changes src
    ON [C.BackupRepositories].[unique_id] = src.[unique_id]

    /*      INSERT NEW ROWS       */
    INSERT INTO [C.BackupRepositories]([id], [name], [description], [type], [host_id], [mount_host_id], [path], [options], [is_busy], [is_unavailable], [is_full], [total_space], [free_space], [unique_id], db_instance_id)
    SELECT
        src.[id],
        src.[name],
        src.[description],
        src.[type],
        src.[host_id],
        src.[mount_host_id],
        src.[path],
        src.[options],
        src.[is_busy],
        src.[is_unavailable],
        src.[is_full],
        src.[total_space],
        src.[free_space],
        src.[unique_id],
        src.db_instance_id
    FROM @imported_changes src LEFT OUTER JOIN [C.BackupRepositories] trg
    ON src.[unique_id] = trg.[unique_id]
    WHERE trg.id IS NULL
END
GO
-----------------------------------------------------




-----------------------------------------------------
--[dbo].[usp_Import_BackupRepositories_Dels]
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_Import_BackupRepositories_Dels]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp_Import_BackupRepositories_Dels]
GO
CREATE PROCEDURE [dbo].[usp_Import_BackupRepositories_Dels]
    @deleted_objects_xml XML,
	@dbInstanceId uniqueidentifier
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @deleted_objects_table table
    (
       obj_id  uniqueidentifier
    )

    /* importing deleted objects set  */
    DECLARE @idoc  int
    EXEC sp_xml_preparedocument @idoc OUTPUT, @deleted_objects_xml
    INSERT INTO @deleted_objects_table( obj_id )
    SELECT *
    FROM OPENXML( @idoc, N'/root/object',1 )
    WITH
    (
       obj_id uniqueidentifier '@id'
    )
    EXEC sp_xml_removedocument @idoc

    DELETE FROM [dbo].[C.BackupRepositories]
    WHERE 
		db_instance_id = @dbInstanceId AND
		[id] IN ( SELECT obj_id FROM @deleted_objects_table )
END
GO
-----------------------------------------------------


-----------------------------------------------------
--[dbo].[usp_Import_Credentials_Changes]
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_Import_Credentials_Changes]') AND type in (N'P'))
     DROP PROCEDURE [dbo].[usp_Import_Credentials_Changes]
GO
CREATE PROCEDURE [dbo].[usp_Import_Credentials_Changes]
     @dbInstanceId uniqueidentifier,
     @timeZoneShift int,
     @importDoc XML
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @imported_changes table
    (
       [id] uniqueidentifier,
       [user_name] nvarchar(255),
       [db_instance_id] uniqueidentifier
    )

    /*              IMPORT XML DOCUMENT      */
    DECLARE @idoc int
    EXEC sp_xml_preparedocument @idoc OUTPUT, @importDoc

    INSERT INTO @imported_changes([id], [user_name])
    SELECT *
    FROM OPENXML( @idoc, '/Root/row', 2)
    WITH
    (
       [id] uniqueidentifier 'id',
       [user_name] nvarchar(255) 'user_name'
    )
    EXEC sp_xml_removedocument @idoc


    /*             ADD DBID  FIELD          */
    UPDATE @imported_changes
    SET db_instance_id= @dbInstanceId

    /*             TIMEZONE UPDATES         */
    /*             UPDATE EXISTING ROWS     */
    UPDATE [C.Credentials]
    SET
        [C.Credentials].[user_name] = src.[user_name]
    FROM [C.Credentials] INNER JOIN @imported_changes src
    ON [C.Credentials].id = src.id

    /*      INSERT NEW ROWS       */
    INSERT INTO [C.Credentials]([id], [user_name], db_instance_id)
    SELECT
        src.[id],
        src.[user_name],
        src.db_instance_id
    FROM @imported_changes src LEFT OUTER JOIN [C.Credentials] trg
    ON src.id = trg.id
    WHERE trg.id IS NULL
END
GO
-----------------------------------------------------




-----------------------------------------------------
--[dbo].usp_Import_Credentials_Dels
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].usp_Import_Credentials_Dels') AND type in (N'P'))
DROP PROCEDURE [dbo].usp_Import_Credentials_Dels
GO
CREATE PROCEDURE [dbo].usp_Import_Credentials_Dels
    @deleted_objects_xml XML,
	@dbInstanceId uniqueidentifier
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @deleted_objects_table table
    (
       obj_id  uniqueidentifier
    )

    /* importing deleted objects set  */
    DECLARE @idoc  int
    EXEC sp_xml_preparedocument @idoc OUTPUT, @deleted_objects_xml
    INSERT INTO @deleted_objects_table( obj_id )
    SELECT *
    FROM OPENXML( @idoc, N'/root/object',1 )
    WITH
    (
       obj_id uniqueidentifier '@id'
    )
    EXEC sp_xml_removedocument @idoc

    DELETE FROM [dbo].[C.Credentials]    
	WHERE id IN ( SELECT obj_id FROM @deleted_objects_table )
END
GO
-----------------------------------------------------

--[dbo].[usp_Import_JobVssCredentials_Changes]
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_Import_JobVssCredentials_Changes]') AND type in (N'P'))
     DROP PROCEDURE [dbo].[usp_Import_JobVssCredentials_Changes]
GO
CREATE PROCEDURE [dbo].[usp_Import_JobVssCredentials_Changes]
     @dbInstanceId uniqueidentifier,
     @timeZoneShift int,
     @importDoc XML
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @imported_changes table
    (
     		[id] uniqueidentifier,
			[job_id] uniqueidentifier,
			[oij_id] uniqueidentifier,
			[credentials_id] uniqueidentifier,
			[db_instance_id] uniqueidentifier
    )

    /*              IMPORT XML DOCUMENT      */
    DECLARE @idoc int
    EXEC sp_xml_preparedocument @idoc OUTPUT, @importDoc

    INSERT INTO @imported_changes([id], [job_id], [oij_id], [credentials_id])
    SELECT *
    FROM OPENXML( @idoc, '/Root/row', 2)
    WITH
    (
       [id] uniqueidentifier 'id',
       [job_id] uniqueidentifier 'job_id',
	   [oij_id] uniqueidentifier 'oij_id',
	   [credentials_id] uniqueidentifier 'credentials_id'
    )
    EXEC sp_xml_removedocument @idoc


    /*             ADD DBID  FIELD          */
    UPDATE @imported_changes
    SET db_instance_id= @dbInstanceId

    /*             TIMEZONE UPDATES         */
    /*             UPDATE EXISTING ROWS     */
    UPDATE [C.JobVssCredentials]
    SET
        [C.JobVssCredentials].[job_id] = src.job_id,
		[C.JobVssCredentials].[oij_id] = src.oij_id,
		[C.JobVssCredentials].[credentials_id] = src.credentials_id
    FROM [C.JobVssCredentials] INNER JOIN @imported_changes src
    ON [C.JobVssCredentials].id = src.id

    /*      INSERT NEW ROWS       */
    INSERT INTO [C.JobVssCredentials]([id],[job_id],[oij_id],[credentials_id],db_instance_id)
    SELECT
        src.[id],
		src.[job_id],
        src.[oij_id],
		src.[credentials_id],
        src.db_instance_id
    FROM @imported_changes src LEFT OUTER JOIN [C.JobVssCredentials] trg
    ON src.id = trg.id
    WHERE trg.id IS NULL
END
GO
-----------------------------------------------------

-----------------------------------------------------
--[dbo].usp_Import_JobVssCredentials_Dels
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].usp_Import_JobVssCredentials_Dels') AND type in (N'P'))
DROP PROCEDURE [dbo].usp_Import_JobVssCredentials_Dels
GO
CREATE PROCEDURE [dbo].usp_Import_JobVssCredentials_Dels
    @deleted_objects_xml XML,
	@dbInstanceId uniqueidentifier
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @deleted_objects_table table
    (
       obj_id  uniqueidentifier
    )

    /* importing deleted objects set  */
    DECLARE @idoc  int
    EXEC sp_xml_preparedocument @idoc OUTPUT, @deleted_objects_xml
    INSERT INTO @deleted_objects_table( obj_id )
    SELECT *
    FROM OPENXML( @idoc, N'/root/object',1 )
    WITH
    (
       obj_id uniqueidentifier '@id'
    )
    EXEC sp_xml_removedocument @idoc

    DELETE FROM [dbo].[C.JobVssCredentials]    
	WHERE id IN ( SELECT obj_id FROM @deleted_objects_table )
END
GO


/* ============================================================================= */
