-- 445

--------------------------------------------------------------------------------
--
-- Common Utilities
--
--------------------------------------------------------------------------------


--------------------------------------------------------------------------------
--
-- Upgrade_DbDeleteFunction
--
-- Delete function from database.
--
-- Parameters:
--     @function_name - specifies function name
--
-- Example:
--     EXEC [dbo].[Upgrade_DbDeleteFunction] '[dbo].[Upgrade_TransformCppText]'
--
--------------------------------------------------------------------------------
PRINT 'Creating [dbo].[Upgrade_DbDeleteFunction]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Upgrade_DbDeleteFunction]') AND type in (N'P', N'PC'))
	DROP PROCEDURE [dbo].[Upgrade_DbDeleteFunction]
GO

	CREATE PROCEDURE [dbo].[Upgrade_DbDeleteFunction]
		@function_name nvarchar(max)
	AS
	BEGIN
		IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(@function_name) AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
		BEGIN
			DECLARE @command nvarchar(max)
			SET @command = 'DROP FUNCTION ' + @function_name
			EXEC (@command)
		END
	END
GO


--------------------------------------------------------------------------------
--
-- Upgrade_DbDeleteProcedure
--
-- Delete stored procedure from database.
--
-- Parameters:
--     @procedure_name - specifies stored procedure name
--
-- Example:
--     EXEC [dbo].[Upgrade_DbDeleteProcedure] '[dbo].[Upgrade_DbDeleteFunction]'
--
--------------------------------------------------------------------------------
PRINT 'Creating [dbo].[Upgrade_DbDeleteProcedure]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Upgrade_DbDeleteProcedure]') AND type in (N'P', N'PC'))
	DROP PROCEDURE [dbo].[Upgrade_DbDeleteProcedure]
GO

	CREATE PROCEDURE [dbo].[Upgrade_DbDeleteProcedure]
		@procedure_name nvarchar(max)
	AS
	BEGIN
		IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(@procedure_name) AND type in (N'P', N'PC'))
		BEGIN
			DECLARE @command nvarchar(max)
			SET @command = 'DROP PROCEDURE ' + @procedure_name
			EXEC (@command)
		END
	END
GO


--------------------------------------------------------------------------------
--
-- Upgrade_DbDeleteTable
--
-- Delete table from database.
--
-- Parameters:
--     @table_name - specifies table name
--
-- Example:
--     EXEC [dbo].[Upgrade_DbDeleteTable] '[dbo].[Upgrade_Log]'
--
--------------------------------------------------------------------------------
PRINT 'Creating [dbo].[Upgrade_DbDeleteTable]'
EXEC [dbo].[Upgrade_DbDeleteProcedure] '[dbo].[Upgrade_DbDeleteTable]'
GO

	CREATE PROCEDURE [dbo].[Upgrade_DbDeleteTable]
		@table_name nvarchar(max)
	AS
	BEGIN
		IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(@table_name) AND type in (N'U'))
		BEGIN
			DECLARE @command nvarchar(max)
			SET @command = 'DROP TABLE ' + @table_name
			EXEC (@command)
		END
	END
GO


--------------------------------------------------------------------------------
--
-- Text & XML Utilities
--
--------------------------------------------------------------------------------


--------------------------------------------------------------------------------
--
-- Upgrade_IsEmptyString
--
-- Validates is string null/empty
--
-- Parameters:
--     @str - specifies input text for validation
--
-- Result:
--     'TRUE' - string is NULL/''
--     'FALSE' - string is not NULL/''
--
--------------------------------------------------------------------------------
PRINT 'Creating [dbo].[Upgrade_IsEmptyString]'
EXEC [dbo].[Upgrade_DbDeleteFunction] '[dbo].[Upgrade_IsEmptyString]'
GO

	CREATE FUNCTION [dbo].[Upgrade_IsEmptyString] (
		@str nvarchar(max))
	RETURNS bit
	AS
	BEGIN
		IF (@str = NULL OR len(@str) = 0)
			RETURN 'TRUE'

		RETURN 'FALSE'
	END
GO


--------------------------------------------------------------------------------
--
-- Upgrade_IsBlankString
--
-- Validates is string null/empty or contains space/tab(s) only
--
-- Parameters:
--     @str - specifies input text for validation
--
-- Result:
--     'TRUE' - string is NULL/''/' '/CHAR(9)
--     'FALSE' - string is not NULL/''/' '/CHAR(9)
--
--------------------------------------------------------------------------------
PRINT 'Creating [dbo].[Upgrade_IsBlankString]'
EXEC [dbo].[Upgrade_DbDeleteFunction] '[dbo].[Upgrade_IsBlankString]'
GO

	CREATE FUNCTION [dbo].[Upgrade_IsBlankString] (
		@str nvarchar(max))
	RETURNS bit
	AS
	BEGIN
		IF (dbo.Upgrade_IsEmptyString(@str) = 'FALSE')
		BEGIN
			IF (patindex(dbo.Upgrade_TransformCppText('%[^ \t]%'), @str collate SQL_Latin1_General_CP1_CI_AS) > 0)
				RETURN 'FALSE'
		END

		RETURN 'TRUE'
	END
GO


--------------------------------------------------------------------------------
--
-- Upgrade_RightTrimString
--
-- Trim a string from the right using the set of symbols
-- (equal to Upgrade_LeftTrimString).
--
--------------------------------------------------------------------------------
PRINT 'Creating [dbo].[Upgrade_RightTrimString]'
EXEC [dbo].[Upgrade_DbDeleteFunction] '[dbo].[Upgrade_RightTrimString]'
GO

	CREATE FUNCTION [dbo].[Upgrade_RightTrimString] (
		@str nvarchar(max),
		@pattern nvarchar(255))
	RETURNS nvarchar(max)
	AS
	BEGIN
		DECLARE @internal_pattern nvarchar(max)
		SET @internal_pattern = dbo.Upgrade_FormatString('%%[%s]', @pattern)

		DECLARE @index bigint
		SET @index = -1

		WHILE (1 = 1)
		BEGIN
			DECLARE @position bigint		
			SET @position = patindex(@internal_pattern, @str collate SQL_Latin1_General_CP1_CI_AS)
			IF (@position = 0)
				BREAK
	
			SET @index = @position - 1
			SET @internal_pattern = @internal_pattern + '_'
		END

		IF (@index <> -1)
			SET @str = substring(@str, 1, @index)

		RETURN @str
	END
GO


--------------------------------------------------------------------------------
--
-- Upgrade_EscapeText
--
-- Transform text to be included into XML nodes.
--
-- Parameters:
--     @text - specifies input text
--
-- Result: escaped string
--
--------------------------------------------------------------------------------
PRINT 'Creating [dbo].[Upgrade_EscapeText]'
EXEC [dbo].[Upgrade_DbDeleteFunction] '[dbo].[Upgrade_EscapeText]'
GO

	CREATE FUNCTION [dbo].[Upgrade_EscapeText] ( @text nvarchar(max) )
	RETURNS nvarchar(max)
	AS
	BEGIN
		SET @text = replace(@text, '<', '&lt;')
		SET @text = replace(@text, '>', '&gt;')
		SET @text = replace(@text, '&', '&amp;')
		SET @text = replace(@text, '"', '&quot;')
		SET @text = replace(@text, '''', '&apos;')

		RETURN @text
	END
GO


--------------------------------------------------------------------------------
--
-- Upgrade_SplitString
--
-- Splits input string into array using separator
--
-- Parameters:
--     @str       - specifies input text
--     @separator - specifies separator (one or more symbols)
--
-- Result: string array
--
--------------------------------------------------------------------------------
PRINT 'Creating [dbo].[Upgrade_SplitString]'
EXEC [dbo].[Upgrade_DbDeleteFunction] '[dbo].[Upgrade_SplitString]'
GO

	CREATE FUNCTION [dbo].[Upgrade_SplitString] (
		@str nvarchar(max),
		@separator nvarchar(10))
	RETURNS @array table ( element nvarchar(max) )
	AS
	BEGIN
		IF (dbo.Upgrade_IsEmptyString(@str) = 'TRUE')
			RETURN

		IF (dbo.Upgrade_IsEmptyString(@separator) = 'TRUE')
		BEGIN
			INSERT INTO @array (element) VALUES (@str)
			RETURN
		END

		DECLARE @start bigint
		SET @start = 0

		WHILE ('TRUE' = 'TRUE')
		BEGIN
			DECLARE @element nvarchar(max)

			DECLARE @position bigint
			SET @position = charindex(@separator, @str, @start)

			IF (@position = 0)
				SET @element = substring(@str, @start, len(@str) - @start + 1)
			ELSE
				SET @element = substring(@str, @start, @position - @start)

			INSERT INTO @array (element) VALUES (@element)

			IF (@position = 0)
				BREAK

			SET @start = @position + len(@separator)				
		END

		RETURN
	END
GO


--------------------------------------------------------------------------------
--
-- Upgrade_TransformCppText
--
-- Replaces control symbols in the input string using CPP-style:
--    \t -> CHAR(9)
--    \n -> CHAR(10)
--    \r -> CHAR(13)
--
-- Parameters:
--     @text - specifies input text for replecement
--
-- Example:
--     DECLARE @text nvarchar(255)
--     SET @text = dbo.Upgrade_TransformCppText('\t\tFirst\r\nSecond\r\nThird')
--
--------------------------------------------------------------------------------
PRINT 'Creating [dbo].[Upgrade_TransformCppText]'
EXEC [dbo].[Upgrade_DbDeleteFunction] '[dbo].[Upgrade_TransformCppText]'
GO

	CREATE FUNCTION [dbo].[Upgrade_TransformCppText] (
		@text nvarchar(max))
	RETURNS nvarchar(max)
	AS
	BEGIN
		SET @text = replace(@text, '\t', CHAR(9))
		SET @text = replace(@text, '\n', CHAR(10))
		SET @text = replace(@text, '\r', CHAR(13))

		RETURN @text
	END
GO


--------------------------------------------------------------------------------
--
-- Upgrade_FormatString
-- Upgrade_FormatString2
-- Upgrade_FormatString3
-- Upgrade_FormatString4
-- Upgrade_FormatString5
-- Upgrade_FormatString6
-- Upgrade_FormatString7
-- Upgrade_FormatString8
-- Upgrade_FormatString9
--
-- Wrapper for "xp_sprintf" extended stored procedure
--
-- Parameters:
--     @format     - specifies a text with format specifier(s)
--     @arg0-@arg9 - specifies input parameters for format specifier(s)
--
-- Result:
--     Returns formatted string.
--
-- Remarks:
--     1. "%s" format specifier is supported only.
--     2. @format parameter lenght is restricted by 255 symbols (by the system)
--
--------------------------------------------------------------------------------
PRINT 'Creating [dbo].[Upgrade_FormatString]'
EXEC [dbo].[Upgrade_DbDeleteFunction] '[dbo].[Upgrade_FormatString]'
GO

	CREATE FUNCTION [dbo].[Upgrade_FormatString] (
		@format nvarchar(255),
		@arg0 sql_variant)
	RETURNS nvarchar(max)
	AS
	BEGIN
		DECLARE @result nvarchar(255)
		SET @format = dbo.Upgrade_TransformCppText(@format)
		EXEC xp_sprintf @result OUTPUT, @format, @arg0
		RETURN @result
	END
GO

PRINT 'Creating [dbo].[Upgrade_FormatString2]'
EXEC [dbo].[Upgrade_DbDeleteFunction] '[dbo].[Upgrade_FormatString2]'
GO

	CREATE FUNCTION [dbo].[Upgrade_FormatString2] (
		@format nvarchar(255),
		@arg0 sql_variant,
		@arg1 sql_variant)
	RETURNS nvarchar(max)
	AS
	BEGIN
		DECLARE @result nvarchar(255)
		SET @format = dbo.Upgrade_TransformCppText(@format)
		EXEC xp_sprintf @result OUTPUT, @format, @arg0, @arg1
		RETURN @result
	END
GO

PRINT 'Creating [dbo].[Upgrade_FormatString3]'
EXEC [dbo].[Upgrade_DbDeleteFunction] '[dbo].[Upgrade_FormatString3]'
GO

	CREATE FUNCTION [dbo].[Upgrade_FormatString3] (
		@format nvarchar(255),
		@arg0 sql_variant,
		@arg1 sql_variant,
		@arg2 sql_variant)
	RETURNS nvarchar(max)
	AS
	BEGIN
		DECLARE @result nvarchar(255)
		SET @format = dbo.Upgrade_TransformCppText(@format)
		EXEC xp_sprintf @result OUTPUT, @format, @arg0, @arg1, @arg2
		RETURN @result
	END
GO

PRINT 'Creating [dbo].[Upgrade_FormatString4]'
EXEC [dbo].[Upgrade_DbDeleteFunction] '[dbo].[Upgrade_FormatString4]'
GO

	CREATE FUNCTION [dbo].[Upgrade_FormatString4] (
		@format nvarchar(255),
		@arg0 sql_variant,
		@arg1 sql_variant,
		@arg2 sql_variant,
		@arg3 sql_variant)
	RETURNS nvarchar(max)
	AS
	BEGIN
		DECLARE @result nvarchar(255)
		SET @format = dbo.Upgrade_TransformCppText(@format)
		EXEC xp_sprintf @result OUTPUT, @format, @arg0, @arg1, @arg2, @arg3
		RETURN @result
	END
GO

PRINT 'Creating [dbo].[Upgrade_FormatString5]'
EXEC [dbo].[Upgrade_DbDeleteFunction] '[dbo].[Upgrade_FormatString5]'
GO

	CREATE FUNCTION [dbo].[Upgrade_FormatString5] (
		@format nvarchar(255),
		@arg0 sql_variant,
		@arg1 sql_variant,
		@arg2 sql_variant,
		@arg3 sql_variant,
		@arg4 sql_variant)
	RETURNS nvarchar(max)
	AS
	BEGIN
		DECLARE @result nvarchar(255)
		SET @format = dbo.Upgrade_TransformCppText(@format)
		EXEC xp_sprintf @result OUTPUT, @format, @arg0, @arg1, @arg2, @arg3, @arg4
		RETURN @result
	END
GO

PRINT 'Creating [dbo].[Upgrade_FormatString6]'
EXEC [dbo].[Upgrade_DbDeleteFunction] '[dbo].[Upgrade_FormatString6]'
GO

	CREATE FUNCTION [dbo].[Upgrade_FormatString6] (
		@format nvarchar(255),
		@arg0 sql_variant,
		@arg1 sql_variant,
		@arg2 sql_variant,
		@arg3 sql_variant,
		@arg4 sql_variant,
		@arg5 sql_variant)
	RETURNS nvarchar(max)
	AS
	BEGIN
		DECLARE @result nvarchar(255)
		SET @format = dbo.Upgrade_TransformCppText(@format)
		EXEC xp_sprintf @result OUTPUT, @format, @arg0, @arg1, @arg2, @arg3, @arg4, @arg5
		RETURN @result
	END
GO

PRINT 'Creating [dbo].[Upgrade_FormatString7]'
EXEC [dbo].[Upgrade_DbDeleteFunction] '[dbo].[Upgrade_FormatString7]'
GO

	CREATE FUNCTION [dbo].[Upgrade_FormatString7] (
		@format nvarchar(255),
		@arg0 sql_variant,
		@arg1 sql_variant,
		@arg2 sql_variant,
		@arg3 sql_variant,
		@arg4 sql_variant,
		@arg5 sql_variant,
		@arg6 sql_variant)
	RETURNS nvarchar(max)
	AS
	BEGIN
		DECLARE @result nvarchar(255)
		SET @format = dbo.Upgrade_TransformCppText(@format)
		EXEC xp_sprintf @result OUTPUT, @format, @arg0, @arg1, @arg2, @arg3, @arg4, @arg5, @arg6
		RETURN @result
	END
GO

PRINT 'Creating [dbo].[Upgrade_FormatString8]'
EXEC [dbo].[Upgrade_DbDeleteFunction] '[dbo].[Upgrade_FormatString8]'
GO

	CREATE FUNCTION [dbo].[Upgrade_FormatString8] (
		@format nvarchar(255),
		@arg0 sql_variant,
		@arg1 sql_variant,
		@arg2 sql_variant,
		@arg3 sql_variant,
		@arg4 sql_variant,
		@arg5 sql_variant,
		@arg6 sql_variant,
		@arg7 sql_variant)
	RETURNS nvarchar(max)
	AS
	BEGIN
		DECLARE @result nvarchar(255)
		SET @format = dbo.Upgrade_TransformCppText(@format)
		EXEC xp_sprintf @result OUTPUT, @format, @arg0, @arg1, @arg2, @arg3, @arg4, @arg5, @arg6, @arg7
		RETURN @result
	END
GO

PRINT 'Creating [dbo].[Upgrade_FormatString9]'
EXEC [dbo].[Upgrade_DbDeleteFunction] '[dbo].[Upgrade_FormatString9]'
GO

	CREATE FUNCTION [dbo].[Upgrade_FormatString9] (
		@format nvarchar(255),
		@arg0 sql_variant,
		@arg1 sql_variant,
		@arg2 sql_variant,
		@arg3 sql_variant,
		@arg4 sql_variant,
		@arg5 sql_variant,
		@arg6 sql_variant,
		@arg7 sql_variant,
		@arg8 sql_variant)
	RETURNS nvarchar(max)
	AS
	BEGIN
		DECLARE @result nvarchar(255)
		SET @format = dbo.Upgrade_TransformCppText(@format)
		EXEC xp_sprintf @result OUTPUT, @format, @arg0, @arg1, @arg2, @arg3, @arg4, @arg5, @arg6, @arg7, @arg8
		RETURN @result
	END
GO


--------------------------------------------------------------------------------
--
-- Upgrade_FormatText
--
-- Formats text for indented output.
--
-- Parameters:
--     @text   - specifies input text buffer
--     @count  - specifies prefix count
--     @prefix - specifies prefix pattern
--
-- Example:
--     SET @value = '123\n234\n345'
--     PRINT dbo.Upgrade_FormatText(@value, 2, DEFAULT)
--
--	Output:
--     "		123"
--     "		234"
--     "		345"
--
--------------------------------------------------------------------------------
PRINT 'Creating [dbo].[Upgrade_FormatText]'
EXEC [dbo].[Upgrade_DbDeleteFunction] '[dbo].[Upgrade_FormatText]'
GO

	CREATE FUNCTION [dbo].[Upgrade_FormatText] (
		@text nvarchar(max),
		@count int,
		@prefix nvarchar(255) = '\t')
	RETURNS nvarchar(max)
	AS
	BEGIN
		DECLARE @str nvarchar(255)
		SET @str = replicate(@prefix, @count)	

		SET @text = replace(@text, dbo.Upgrade_TransformCppText('\n'), '\n' + @str)

		IF (len(@text) != 0)
		BEGIN
			SET @text = @str + @text
		END

		SET @text = dbo.Upgrade_TransformCppText(@text)
		RETURN @text
	END
GO


--------------------------------------------------------------------------------
--
-- Upgrade_FormatXmlText
--
-- Formats XML text for indented output.
--
-- Parameters:
--     @text   - specifies input XML text
--     @count  - specifies prefix count
--     @prefix - specifies prefix pattern
--
-- Example:
--     SET @value = '<a>Load</a><b>Test</b>'
--     PRINT dbo.Upgrade_FormatXmlText(@value, 2, DEFAULT)
--
--	Output:
--     "		<a>Load</a>"
--     "		<b>Test</b>"
--
--------------------------------------------------------------------------------
PRINT 'Creating [dbo].[Upgrade_FormatXmlText]'
EXEC [dbo].[Upgrade_DbDeleteFunction] '[dbo].[Upgrade_FormatXmlText]'
GO

	CREATE FUNCTION [dbo].[Upgrade_FormatXmlText] (
		@text nvarchar(max),
		@count int,
		@prefix nvarchar(255) = '\t')
	RETURNS nvarchar(max)
	AS
	BEGIN
		DECLARE @str nvarchar(255)
		SET @str = replicate(@prefix, @count)	

		SET @text = replace(@text, '><', '>\r\n' + @str + '<')

		IF (len(@text) != 0)
		BEGIN
			SET @text = @str + @text
		END

		SET @text = dbo.Upgrade_TransformCppText(@text)
		RETURN @text
	END
GO


--------------------------------------------------------------------------------
--
-- Upgrade_FormatXmlDocument
--
-- Formats XML for output.
--
-- Parameters:
--     @document - specifies XML document
--     @count    - specifies prefix count
--     @prefix   - specifies prefix pattern
--
-- Example:
--     SET @document = '<root><A><B>123</B></A></root>'
--     PRINT dbo.Upgrade_FormatXmlDocument(@document, 2, DEFAULT)
--
--	Output:
--     "		<root>"
--     "		<A>"
--     "		<B>123</B>"
--     "		</A>"
--     "		</root>"
--
--------------------------------------------------------------------------------
PRINT 'Creating [dbo].[Upgrade_FormatXmlDocument]'
EXEC [dbo].[Upgrade_DbDeleteFunction] '[dbo].[Upgrade_FormatXmlDocument]'
GO

	CREATE FUNCTION [dbo].[Upgrade_FormatXmlDocument] (
		@document xml,
		@count int,
		@prefix nvarchar(255) = '\t')
	RETURNS nvarchar(max)
	AS
	BEGIN
		DECLARE @text nvarchar(max)
		SET @text = cast(@document as nvarchar(max))
		RETURN dbo.Upgrade_FormatXmlText(@text, @count, @prefix)
	END
GO


--------------------------------------------------------------------------------
--
-- Date/Time Utilities
--
--------------------------------------------------------------------------------
PRINT 'Creating [dbo].[Upgrade_FormatDateTime]'
EXEC [dbo].[Upgrade_DbDeleteFunction] '[dbo].[Upgrade_FormatDateTime]'
GO

	CREATE FUNCTION [dbo].[Upgrade_FormatDateTime] (@value datetime)
	RETURNS nvarchar(255)
	AS
	BEGIN
		--SET @value = dateadd(hour, datediff(hh, getutcdate(), getdate()), @value)
		RETURN convert(nvarchar, @value, 20)
	END
GO


--------------------------------------------------------------------------------
--
-- Upgrade_FormatIso8601DateTime
--
-- Formats datetime using ISO8601 rules.
--
-- Parameters:
--     @value - specifies datetime to formatting
--
-- Examples:
--     '2008-10-23T18:52:47.513'
--
--------------------------------------------------------------------------------
PRINT 'Creating [dbo].[Upgrade_FormatIso8601DateTime]'
EXEC [dbo].[Upgrade_DbDeleteFunction] '[dbo].[Upgrade_FormatIso8601DateTime]'
GO

	CREATE FUNCTION [dbo].[Upgrade_FormatIso8601DateTime] (@value datetime)
	RETURNS nvarchar(255)
	AS
	BEGIN
		SET @value = dateadd(hour, datediff(hh, getutcdate(), getdate()), @value)

		-- ISO 8601 format: international standard - works with any language setting
        -- '2006-09-07T16:42:31.301'
		DECLARE @str_iso8601 nvarchar(100)
		SET @str_iso8601 = convert(varchar, @value, 126)

		IF (patindex('%[.]%', @str_iso8601 collate SQL_Latin1_General_CP1_CI_AS) = 0)
			SET @str_iso8601 = @str_iso8601 + '.'

		SET @str_iso8601 = @str_iso8601 + replicate('0', 27 - len(@str_iso8601))

		DECLARE @time_zone int
		SET @time_zone = datediff(hh, getutcdate(), getdate())

		DECLARE @str_time_zone nvarchar(100)
		SET @str_time_zone = right(replicate('0', 2) + convert(varchar, abs(@time_zone)), 2)

		IF (sign(@time_zone) = -1)
			SET @str_time_zone = '-' + @str_time_zone
		ELSE
			SET @str_time_zone = '+' + @str_time_zone

		-- '2011-10-31T15:06:52.9434222+04:00'
		RETURN dbo.Upgrade_FormatString2('%s%s:00', @str_iso8601, @str_time_zone)
	END
GO


--------------------------------------------------------------------------------
--
-- Upgrade_FormatUtcInvariantDateTime
--
-- Formats datetime to UTC format using invariant culture.
--
-- Parameters:
--     @value - specifies datetime to formatting
--
-- Examples:
--     '10/25/2011 07:41:18'
--
--------------------------------------------------------------------------------
PRINT 'Creating [dbo].[Upgrade_FormatUtcInvariantDateTime]'
EXEC [dbo].[Upgrade_DbDeleteFunction] '[dbo].[Upgrade_FormatUtcInvariantDateTime]'
GO

	CREATE FUNCTION [dbo].[Upgrade_FormatUtcInvariantDateTime] (@value datetime)
	RETURNS nvarchar(255)
	AS
	BEGIN
		-- UTC format: invariant culture
		DECLARE @utc datetime
		SET @utc = dateadd(hh, datediff(hh, getdate(), getutcdate()), @value)
		RETURN convert(nvarchar, @utc, 101) + ' ' + convert(nvarchar, @utc, 108)
	END
GO


--------------------------------------------------------------------------------
--
-- Upgrade_FormatDateTimeDiff
--
-- Formats difference between two datetime values.
--
-- Parameters:
--     @start_time - specifies start time
--     @end_time   - specifies end time
--
-- Comment:
--     DO NOT use this function if difference is more than 24 hours (days, etc).
--
-- Examples:
--     '00:02:03'
--
--------------------------------------------------------------------------------
PRINT 'Creating [dbo].[Upgrade_FormatDateTimeDiff]'
EXEC [dbo].[Upgrade_DbDeleteFunction] '[dbo].[Upgrade_FormatDateTimeDiff]'
GO

	CREATE FUNCTION [dbo].[Upgrade_FormatDateTimeDiff] (
		@start_time datetime,
		@end_time datetime)
	RETURNS nvarchar(255)
	AS
	BEGIN
		RETURN convert(varchar,(@end_time - @start_time), 108)
	END
GO


--------------------------------------------------------------------------------
--
-- File System Utilities
--
--------------------------------------------------------------------------------


--------------------------------------------------------------------------------
--
-- Upgrade_InternalKb
--
-- Returns size of file system item in "Kb".
--
-- Parameters:
--     @size - file system item size in bytes
--
--------------------------------------------------------------------------------
PRINT 'Creating [dbo].[Upgrade_InternalKb]'
EXEC [dbo].[Upgrade_DbDeleteFunction] '[dbo].[Upgrade_InternalKb]'
GO

	CREATE FUNCTION [dbo].[Upgrade_InternalKb] (
		@size bigint)
	RETURNS float
	AS
	BEGIN
		RETURN @size / 1024.;
	END
GO


--------------------------------------------------------------------------------
--
-- Upgrade_InternalMb
--
-- Returns size of file system item in "Mb".
--
-- Parameters:
--     @size - file system item size in bytes
--
--------------------------------------------------------------------------------
PRINT 'Creating [dbo].[Upgrade_InternalMb]'
EXEC [dbo].[Upgrade_DbDeleteFunction] '[dbo].[Upgrade_InternalMb]'
GO

	CREATE FUNCTION [dbo].[Upgrade_InternalMb] (
		@size bigint)
	RETURNS float
	AS
	BEGIN
		RETURN @size / 1048576.;
	END
GO


--------------------------------------------------------------------------------
--
-- Upgrade_InternalGb
--
-- Returns size of file system item in "Gb".
--
-- Parameters:
--     @size - file system item size in bytes
--
--------------------------------------------------------------------------------
PRINT 'Creating [dbo].[Upgrade_InternalGb]'
EXEC [dbo].[Upgrade_DbDeleteFunction] '[dbo].[Upgrade_InternalGb]'
GO

	CREATE FUNCTION [dbo].[Upgrade_InternalGb] (
		@size bigint)
	RETURNS float
	AS
	BEGIN
		RETURN @size / 1073741824.;
	END
GO


--------------------------------------------------------------------------------
--
-- Upgrade_InternalTb
--
-- Returns size of file system item in "Tb".
--
-- Parameters:
--     @size - file system item size in bytes
--
--------------------------------------------------------------------------------
PRINT 'Creating [dbo].[Upgrade_InternalTb]'
EXEC [dbo].[Upgrade_DbDeleteFunction] '[dbo].[Upgrade_InternalTb]'
GO

	CREATE FUNCTION [dbo].[Upgrade_InternalTb] (
		@size bigint)
	RETURNS float
	AS
	BEGIN
		RETURN @size / 1099511627776.;
	END
GO


--------------------------------------------------------------------------------
--
-- Upgrade_FormatSize
--
-- Formats size of file system item for visualization.
--
-- Parameters:
--     @size - file system item size in bytes
--
-- Result:
--     Formatted string in form of '10.00 Mb'
--
--------------------------------------------------------------------------------
PRINT 'Creating [dbo].[Upgrade_FormatSize]'
EXEC [dbo].[Upgrade_DbDeleteFunction] '[dbo].[Upgrade_FormatSize]'
GO

	CREATE FUNCTION [dbo].[Upgrade_FormatSize] (
		@size bigint)
	RETURNS nvarchar(255)
	AS
	BEGIN
		IF (dbo.Upgrade_InternalMb(@size) < 1)
			RETURN dbo.Upgrade_FormatString('%s KB', cast(dbo.Upgrade_InternalKb(@size) as decimal(18, 2)))

		IF (dbo.Upgrade_InternalGb(@size) < 1)
			RETURN dbo.Upgrade_FormatString('%s MB', cast(dbo.Upgrade_InternalMb(@size) as decimal(18, 2)))

		IF (dbo.Upgrade_InternalTb(@size) < 1)
			RETURN dbo.Upgrade_FormatString('%s GB', cast(dbo.Upgrade_InternalGb(@size) as decimal(18, 2)))

		RETURN dbo.Upgrade_FormatString('%s TB', cast(dbo.Upgrade_InternalTb(@size) as decimal(18, 2)))
	END
GO


--------------------------------------------------------------------------------
--
-- Upgrade_FormatRate
--
-- Formats speed of file system operations for visualization.
--
-- Parameters:
--     @size - speed in bytes per seconds
--
-- Result:
--     Formatted string in form of '10.00 Mb/s'
--
--------------------------------------------------------------------------------
PRINT 'Creating [dbo].[Upgrade_FormatRate]'
EXEC [dbo].[Upgrade_DbDeleteFunction] '[dbo].[Upgrade_FormatRate]'
GO

	CREATE FUNCTION [dbo].[Upgrade_FormatRate] (
		@speed bigint)
	RETURNS nvarchar(255)
	AS
	BEGIN
		IF (dbo.Upgrade_InternalMb(@speed) < 1)
			RETURN dbo.Upgrade_FormatString('%s KB/s', round(dbo.Upgrade_InternalKb(@speed), 0))

		IF (dbo.Upgrade_InternalGb(@speed) < 1)
			RETURN dbo.Upgrade_FormatString('%s MB/s', round(dbo.Upgrade_InternalMb(@speed), 0))

		RETURN dbo.Upgrade_FormatString('%s GB/s', round(dbo.Upgrade_InternalGb(@speed), 0))
	END
GO


--------------------------------------------------------------------------------
--
-- Upgrade_GetFileSystemType
--
-- Returns file system type which is based on path inside FS.
--
-- Parameters:
--     @path - specifies FS path
--
-- Result:
--     -1 - Unknown FS
--      0 - Windows path (C:\Windows\...)
--      1 - Linux path (/store/folder/...)
--      2 - UNC path (\\SERVER\share\...)
--
--------------------------------------------------------------------------------
PRINT 'Creating [dbo].[Upgrade_GetFileSystemType]'
EXEC [dbo].[Upgrade_DbDeleteFunction] '[dbo].[Upgrade_GetFileSystemType]'
GO

	CREATE FUNCTION [dbo].[Upgrade_GetFileSystemType] (
		@path nvarchar(max))
	RETURNS int
	AS 
	BEGIN	
		SET @path = ltrim(rtrim(@path))

		IF (len(@path) != 0)
		BEGIN
			DECLARE @index bigint
			
			SET @index = patindex('[A-Za-z]:', @path collate SQL_Latin1_General_CP1_CI_AS)
			IF (@index > 0)
				RETURN 0

			SET @index = patindex('[A-Za-z]:\%', @path collate SQL_Latin1_General_CP1_CI_AS)
			IF (@index > 0)
				RETURN 0

			SET @index = patindex('/[^/]%', @path collate SQL_Latin1_General_CP1_CI_AS)
			IF (@index > 0)
				RETURN 1
	
			SET @index = patindex('\\[^\]%', @path collate SQL_Latin1_General_CP1_CI_AS)
			IF (@index > 0)
				RETURN 2
		END

		RETURN -1
	END
GO


--------------------------------------------------------------------------------
--
-- Upgrade_FileSystemIsRoot
--
-- Returns a flag to defines if file system path is Windows root drive or not.
--
-- Parameters:
--     @path - specifies root path
--
--------------------------------------------------------------------------------
PRINT 'Creating [dbo].[Upgrade_FileSystemIsRoot]'
EXEC [dbo].[Upgrade_DbDeleteFunction] '[dbo].[Upgrade_FileSystemIsRoot]'
GO

	CREATE FUNCTION [dbo].[Upgrade_FileSystemIsRoot] (
		@path nvarchar(max))
	RETURNS bit
	AS 
	BEGIN
		SET @path = ltrim(rtrim(@path))

		DECLARE @index bigint

		SET @index = patindex('[A-Za-z]:', @path collate SQL_Latin1_General_CP1_CI_AS)
		IF (@index > 0)
			RETURN 'TRUE'

		SET @index = patindex('[A-Za-z]:[\/]', @path collate SQL_Latin1_General_CP1_CI_AS)
		IF (@index > 0)
			RETURN 'TRUE'

		RETURN 'FALSE'
	END
GO


--------------------------------------------------------------------------------
--
-- Upgrade_FileSystemComparePaths
--
-- Compares two paths in different or equal FS in depending on theirs types.
--
-- Parameters:
--     @path1 - specifies first FS path
--     @path2 - specifies second FS path
--
--------------------------------------------------------------------------------
PRINT 'Creating [dbo].[Upgrade_FileSystemComparePaths]'
EXEC [dbo].[Upgrade_DbDeleteFunction] '[dbo].[Upgrade_FileSystemComparePaths]'
GO

	CREATE FUNCTION [dbo].[Upgrade_FileSystemComparePaths] (
		@path1 nvarchar(max),
		@path2 nvarchar(max))
	RETURNS bit
	AS 
	BEGIN	
		SET @path1 = dbo.Upgrade_FileSystemRemoveSeparator(@path1)
		SET @path2 = dbo.Upgrade_FileSystemRemoveSeparator(@path2)

		DECLARE @type1 int
		SET @type1 = dbo.Upgrade_GetFileSystemType(@path1)

		DECLARE @type2 int
		SET @type2 = dbo.Upgrade_GetFileSystemType(@path2)

--     -1 - Unknown FS
--      0 - Windows path (C:\Windows\...)
--      1 - Linux path (/store/folder/...)
--      2 - UNC path (\\SERVER\share\...)

		IF ((@type1 != @type2) OR (@type1 = 1 AND @type2 = 1))
			RETURN CASE WHEN (cast(@path1 as varbinary(max)) = cast(@path2 as varbinary(max))) THEN 'TRUE' ELSE 'FALSE' END

		RETURN CASE WHEN (@path1 = @path2) THEN 'TRUE' ELSE 'FALSE' END
	END
GO


--------------------------------------------------------------------------------
--
-- Upgrade_GetFileSystemParentFolder
--
-- Returns parent folder of specified path inside FS.
--
-- Parameters:
--     @path - specifies FS path
--
-- Result:
--     Returns parent folder or '' in a case of error.
--
-- Remarks:
--     UNC, Linux and Windows FS are supported.
--
-- Example:
--     PRINT dbo.Upgrade_GetFileSystemParentFolder('D:\123\ABC')
-- 
-- Output:
--     "D:\123"
--
--------------------------------------------------------------------------------
PRINT 'Creating [dbo].[Upgrade_GetFileSystemParentFolder]'
EXEC [dbo].[Upgrade_DbDeleteFunction] '[dbo].[Upgrade_GetFileSystemParentFolder]'
GO

	CREATE FUNCTION [dbo].[Upgrade_GetFileSystemParentFolder] (
		@path nvarchar(max))
	RETURNS nvarchar(max)
	AS 
	BEGIN	
		SET @path = ltrim(rtrim(@path))

		IF (len(@path) != 0)
		BEGIN
			DECLARE @reverse nvarchar(max)
			SET @reverse = reverse(@path)

			DECLARE @index bigint

			SET @index = patindex('%\%[^\]\\', @reverse collate SQL_Latin1_General_CP1_CI_AS)
			IF (@index = 0) 
			BEGIN
				SET @index = patindex('%/%[^/]/', @reverse collate SQL_Latin1_General_CP1_CI_AS)
				IF (@index = 0)
				BEGIN
					SET @index = patindex('%\%\:[A-Za-z]', @reverse collate SQL_Latin1_General_CP1_CI_AS)
					IF (@index = 0) RETURN ''					
				END
			END

			RETURN substring(@path, 0, len(@path) - @index + 1)
		END

		RETURN ''
	END
GO


--------------------------------------------------------------------------------
--
-- Upgrade_GetFileSystemParentFolderEx
--
-- Returns parent folder of specified path inside FS.
--
-- Parameters:
--     @path - specifies FS path
--
-- Result:
--     Returns parent folder or '' in a case of error.
--
-- Remarks:
--     UNC, Linux and Windows FS are supported.
--
-- Example:
--     PRINT dbo.Upgrade_GetFileSystemParentFolderEx('D:\123\ABC')
-- 
-- Output:
--     "D:\123"
--
--------------------------------------------------------------------------------
PRINT 'Creating [dbo].[Upgrade_GetFileSystemParentFolderEx]'
EXEC [dbo].[Upgrade_DbDeleteFunction] '[dbo].[Upgrade_GetFileSystemParentFolderEx]'
GO

	CREATE FUNCTION [dbo].[Upgrade_GetFileSystemParentFolderEx] (
		@path nvarchar(max))
	RETURNS nvarchar(max)
	AS 
	BEGIN
		SET @path = ltrim(rtrim(@path))
		IF (len(@path) = 0)
			RETURN ''  -- Error
		
		DECLARE @temp nvarchar(max)
		SET @temp = reverse(@path)

		DECLARE @pattern nvarchar(255)
		DECLARE @separator nvarchar(255)
		
		DECLARE @start bigint

		-- Windows
		SET @pattern = '%[\/]:[A-Za-z]'
		SET @separator = '[\/]'
		
		SET @start = patindex(@pattern, @temp collate SQL_Latin1_General_CP1_CI_AS)
		IF (@start = 0)
		BEGIN
			-- Share
			SET @pattern = '%[^\]\%[^\]\\'
			SET @separator = '[\]'

			SET @start = patindex(@pattern, @temp collate SQL_Latin1_General_CP1_CI_AS)
			IF (@start <> 0)
			BEGIN
				SET @start = @start + 2
			END
			ELSE
			BEGIN
				-- Linux
				SET @pattern = '%[^/]/'
				SET @separator = '[/]'
				
				SET @start = patindex(@pattern, @temp collate SQL_Latin1_General_CP1_CI_AS)
				IF (@start <> 0)
				BEGIN
					SET @start = @start + 1
				END
				ELSE
				BEGIN
					RETURN ''  -- Error
				END
			END
		END

		-- Processing
		DECLARE @index bigint
		SET @index = 1

		DECLARE @skip bit
		SET @skip = 'TRUE'

		DECLARE @char nvarchar
		DECLARE @flag bit
		
		WHILE (1 = 1)
		BEGIN
			IF (@index >= @start)
			BEGIN
				SET @index = @index - 1
				BREAK
			END

			SET @char = substring(@temp, @index, 1)

			SET @flag = 'FALSE'
	
			IF (patindex(@separator, @char collate SQL_Latin1_General_CP1_CI_AS) <> 0)
				SET @flag = 'TRUE'
	
			IF (@flag = 'FALSE' AND @skip = 'TRUE')
				SET @skip = 'FALSE'
	
			IF (@skip = 'FALSE' AND @flag = 'TRUE')
				BREAK

			SET @index = @index + 1
		END
		
		RETURN substring(@path, 1, len(@path) - @index)
	END
GO


--------------------------------------------------------------------------------
--
-- Upgrade_GetFileSystemSeparators
--
-- Returns separator symbols which is using by FS.
--
-- Parameters:
--     @type          - specifies FS type
--     @all_supported - specifies flag to return standard or all separators
--
-- Result:
--     Returns separator symbol or '' in a case of error.
--
-- Remarks:
--     UNC, Linux and Windows FS are supported.
--
-- Example:
--     PRINT dbo.Upgrade_GetFileSystemSeparator('D:\123\ABC')
--
-- Output:
--     "\"
--
--------------------------------------------------------------------------------
PRINT 'Creating [dbo].[Upgrade_GetFileSystemSeparators]'
EXEC [dbo].[Upgrade_DbDeleteFunction] '[dbo].[Upgrade_GetFileSystemSeparators]'
GO

	CREATE FUNCTION [dbo].[Upgrade_GetFileSystemSeparators] (
		@type int,
		@all_supported bit = 'FALSE')
	RETURNS nvarchar(255)
	AS 
	BEGIN
		IF (@type = 0 AND @all_supported = 'TRUE')
		BEGIN
			RETURN '\/'
		END

		IF (@type = 0 OR @type = 2)
			RETURN '\'
		
		IF (@type = 1)
			RETURN '/'

		RETURN ''
	END
GO


--------------------------------------------------------------------------------
--
-- Upgrade_FileSystemRemoveSeparator
--
-- Remove one or more separators in the end of path.
--
-- Parameters:
--     @path - specifies the input path
--
-- Example:
--     PRINT dbo.Upgrade_FileSystemRemoveSeparator('D:\123\\\\\\\\\\\///////\/\/\')
-- 
-- Output:
--     "D:\123"
--
--------------------------------------------------------------------------------
PRINT 'Creating [dbo].[Upgrade_FileSystemRemoveSeparator]'
EXEC [dbo].[Upgrade_DbDeleteFunction] '[dbo].[Upgrade_FileSystemRemoveSeparator]'
GO

	CREATE FUNCTION [dbo].[Upgrade_FileSystemRemoveSeparator] (
		@path nvarchar(max))
	RETURNS nvarchar(max)
	AS 
	BEGIN	
		SET @path = ltrim(rtrim(@path))

		DECLARE @type int
		SET @type = dbo.Upgrade_GetFileSystemType(@path)

		DECLARE @separators nvarchar(255)
		SET @separators = dbo.Upgrade_GetFileSystemSeparators(@type, 'TRUE')

		SET @path = dbo.Upgrade_RightTrimString(@path, @separators)

		IF (dbo.Upgrade_FileSystemIsRoot(@path) = 'TRUE')
		BEGIN
			DECLARE @separator nvarchar(255)
			SET @separator = dbo.Upgrade_GetFileSystemSeparators(@type, DEFAULT)

			SET @path = @path + @separator
		END

		RETURN @path
	END
GO


--------------------------------------------------------------------------------
--
-- Upgrade_FileSystemAddSeparator
--
-- Add optional separator to the end of path.
--
-- Parameters:
--     @path - specifies the input path
--
-- Example:
--     PRINT dbo.Upgrade_FileSystemAddSeparator('D:\123\\\\\\\\\')
-- 
-- Output:
--     "D:\123\"
--
--------------------------------------------------------------------------------
PRINT 'Creating [dbo].[Upgrade_FileSystemAddSeparator]'
EXEC [dbo].[Upgrade_DbDeleteFunction] '[dbo].[Upgrade_FileSystemAddSeparator]'
GO

	CREATE FUNCTION [dbo].[Upgrade_FileSystemAddSeparator] (
		@path nvarchar(max))
	RETURNS nvarchar(max)
	AS 
	BEGIN	
		DECLARE @type int
		SET @type = dbo.Upgrade_GetFileSystemType(@path)

		DECLARE @separator nvarchar(255)
		SET @separator = dbo.Upgrade_GetFileSystemSeparators(@type, DEFAULT)

		SET @path = dbo.Upgrade_FileSystemRemoveSeparator(@path)

		IF (dbo.Upgrade_FileSystemIsRoot(@path) = 'FALSE')
		BEGIN
			SET @path = @path + @separator
		END	

		RETURN @path
	END
GO


--------------------------------------------------------------------------------
--
-- Upgrade_FileSystemBuildPath
--
-- Build new FS path using folder and item paths.
--
-- Parameters:
--     @folder - specifies folder path
--     @ite    - specifies item path
--
-- Result:
--     Returns new FS path or '' in a case of error.
--
-- Example:
--     PRINT dbo.Upgrade_FileSystemBuildPath('D:\123\ABC', '2')
-- 
-- Output:
--     "D:\123\ABC\2"
--
--------------------------------------------------------------------------------
PRINT 'Creating [dbo].[Upgrade_FileSystemBuildPath]'
EXEC [dbo].[Upgrade_DbDeleteFunction] '[dbo].[Upgrade_FileSystemBuildPath]'
GO

	CREATE FUNCTION [dbo].[Upgrade_FileSystemBuildPath] (
		@folder nvarchar(max),
		@item nvarchar(max))
	RETURNS nvarchar(max)
	AS 
	BEGIN	
		SET @folder = ltrim(rtrim(@folder))
		SET @item = ltrim(rtrim(@item))

		IF (len(@folder) != 0)
		BEGIN
			SET @folder = dbo.Upgrade_FileSystemRemoveSeparator(@folder)

			IF (len(@item) != 0)
				SET @folder = dbo.Upgrade_FileSystemAddSeparator(@folder)
		END

		RETURN @folder + @item
	END
GO


--------------------------------------------------------------------------------
--
--  Low Level Data Transformations & Visualizations
--
--------------------------------------------------------------------------------


--------------------------------------------------------------------------------
--
-- Upgrade_GetBackupRepositoryType
--
-- Returns backup repository type using host type and FS path.
--
-- Parameters:
--     @host_type  - specifies host type (DB value)
--     @target_dir - specifies FS path (UNC, Windows, Linux)
--
-- Result:
--     -1 - Unknown repository
--      0 - Windows repository
--      1 - Linux repository
--      2 - CIFS repository
--
--------------------------------------------------------------------------------
PRINT 'Creating [dbo].[Upgrade_GetBackupRepositoryType]'
EXEC [dbo].[Upgrade_DbDeleteFunction] '[dbo].[Upgrade_GetBackupRepositoryType]'
GO

	CREATE FUNCTION [dbo].[Upgrade_GetBackupRepositoryType] (
		@host_type int,
		@target_dir nvarchar(max))
	RETURNS int
	AS
	BEGIN
		-- Windows = 0
		-- Linux   = 1
		-- UNC     = 2
		DECLARE @filesystem_type int
		SET @filesystem_type = dbo.Upgrade_GetFileSystemType(@target_dir)

		-- ESX          = 0   // Not used
		-- VC           = 1   // Not used
		-- Linux        = 2
		-- Local        = 3
		-- Windows      = 5
		-- ESXi         = 6   // Not used
		-- HvServer     = 7   // Reserved
		-- HvCluster    = 8   // Reserved
		-- VMM          = 9   // Reserved
		-- BackupServer = 10  // Reserved
		IF (@filesystem_type != -1)
		BEGIN
			IF (@filesystem_type = 0 AND (@host_type = 3 OR @host_type = 5))
				return 0

			IF (@filesystem_type = 1 AND (@host_type = 0 OR @host_type = 2))
				return 1

			IF (@filesystem_type = 2 AND (@host_type = 0 OR @host_type = 2 OR @host_type = 3 OR @host_type = 5))
				return 2
		END

		RETURN -1
	END
GO


--------------------------------------------------------------------------------
--
-- Upgrade_GetBackupRepositoryTypeName
--
-- Returns backup repository type name.
--
-- Parameters:
--     @repository_type - specifies repository type
--
--------------------------------------------------------------------------------
PRINT 'Creating [dbo].[Upgrade_GetBackupRepositoryTypeName]'
EXEC [dbo].[Upgrade_DbDeleteFunction] '[dbo].[Upgrade_GetBackupRepositoryTypeName]'
GO

	CREATE FUNCTION [dbo].[Upgrade_GetBackupRepositoryTypeName] ( @repository_type int )
	RETURNS nvarchar(255)
	AS
	BEGIN
		IF (@repository_type = 0)
			RETURN 'Windows Repository'
			
		IF (@repository_type = 1)
			RETURN 'Linux Repository'
			
		IF (@repository_type = 2)
			RETURN 'CIFS Repository'
			
		RETURN 'Unknown Repository'
	END
GO


--------------------------------------------------------------------------------
--
-- Upgrade_GetJobTypeName
--
-- Returns job type name.
--
-- Parameters:
--     @job_type - specifies job type
--
--------------------------------------------------------------------------------
PRINT 'Creating [dbo].[Upgrade_GetJobTypeName]'
EXEC [dbo].[Upgrade_DbDeleteFunction] '[dbo].[Upgrade_GetJobTypeName]'
GO

	CREATE FUNCTION [dbo].[Upgrade_GetJobTypeName] (
		@job_type int)
	RETURNS nvarchar(255)
	AS
	BEGIN
		-- Backup = 0
		-- Replica = 1
		-- Copy = 2
		-- DRV = 3
		-- RestoreVm = 4
		-- RestoreVmFiles = 5
		-- RestoreFiles = 6
		-- Failover = 7

		IF (@job_type = 0)
			RETURN 'Backup'

		IF (@job_type = 1)
			RETURN 'Replica'

		IF (@job_type = 2)
			RETURN 'Copy'

		IF (@job_type = 3)
			RETURN 'SureBackup'

		IF (@job_type = 4)
			RETURN 'Restore VM'

		IF (@job_type = 5)
			RETURN 'Restore VM Files'

		IF (@job_type = 6)
			RETURN 'Restore Files'

		IF (@job_type = 7)
			RETURN 'Failover'

		RETURN 'Unknown'
	END
GO


--------------------------------------------------------------------------------
--
-- Upgrade_GetJobSourceTypeName
--
-- Returns job source type name.
--
-- Parameters:
--     @type - specifies job source type.
--
-- Result:
--     'folder' - is job source type is 'Files"
--     'VM'     - in other cases
--
--------------------------------------------------------------------------------
PRINT 'Creating [dbo].[Upgrade_GetJobSourceTypeName]'
EXEC [dbo].[Upgrade_DbDeleteFunction] '[dbo].[Upgrade_GetJobSourceTypeName]'
GO

	CREATE FUNCTION [dbo].[Upgrade_GetJobSourceTypeName] (
		@type int)
	RETURNS nvarchar(255)
	AS
	BEGIN
		-- EJobSourceType.Files = 3
	
		IF (@type = 3)
			RETURN 'folder'

		RETURN 'VM'
	END
GO


--------------------------------------------------------------------------------
--
-- Upgrade_GetTaskSourceModeName
--
-- Returns task source mode name.
--
-- Parameters:
--     @source_mode - specifies task source mode.
--
-- Result:
--     'service console agent' - NetSsh
--     'agentless'             - NetNfs
--     'VCB SAN'               - VcbSan
--     'VCB NBD'               - VcbNbd
--     'VCB NBDSSL'            - VcbNbdSsl
--     'SAN/NBD'               - VddkSanNbd
--     'SAN/NBDSSL'            - VddkSanNbdSsl
--     'SAN'                   - VddkSan
--     'NBD'                   - VddkNbd
--     'VCB SAN'               - VddkNbdSsl
--     'HOTADD'                - VddkHotAdd
--     'HOTADD/NBD'            - VddkHotAddNbd
--     'Unknown'               - ?
--
--------------------------------------------------------------------------------
PRINT 'Creating [dbo].[Upgrade_GetTaskSourceModeName]'
EXEC [dbo].[Upgrade_DbDeleteFunction] '[dbo].[Upgrade_GetTaskSourceModeName]'
GO

	CREATE FUNCTION [dbo].[Upgrade_GetTaskSourceModeName] (
		@source_mode int)
	RETURNS nvarchar(255)
	AS
	BEGIN
		-- Unknown = 0
		-- NetSsh = 1
		-- NetNfc = 2
		-- VcbSan = 3
		-- VcbNbd = 4
		-- VcbNbdSsl = 5
		-- VddkSanNbd = 6
		-- VddkSanNbdSsl = 7
		-- VddkSan = 8
		-- VddkNbd = 9
		-- VddkNbdSsl = 10
		-- VddkHotAdd = 11
		-- VddkHotAddNbd = 12

		IF (@source_mode = 1)   -- NetSsh
			RETURN 'service console agent'

		IF (@source_mode = 2)   -- NetNfc
			RETURN 'agentless'

		IF (@source_mode = 3)   -- VcbSan
			RETURN 'VCB SAN'

		IF (@source_mode = 4)   -- VcbNbd
			RETURN 'VCB NBD'

		IF (@source_mode = 5)   -- VcbNbdSsl
			RETURN 'VCB NBDSSL'

		IF (@source_mode = 6)   -- VddkSanNbd
			RETURN 'SAN/NBD'

		IF (@source_mode = 7)   -- VddkSanNbdSsl
			RETURN 'SAN/NBDSSL'

		IF (@source_mode = 8)   -- VddkSan
			RETURN 'SAN'

		IF (@source_mode = 9)   -- VddkNbd
			RETURN 'NBD'

		IF (@source_mode = 10)  -- VddkNbdSsl
			RETURN 'VCB SAN'

		IF (@source_mode = 11)  -- VddkHotAdd
			RETURN 'HOTADD'

		IF (@source_mode = 12)  -- VddkHotAddNbd
			RETURN 'HOTADD/NBD'

		RETURN ''               -- Unknown
	END
GO


--------------------------------------------------------------------------------
--
-- Upgrade_GetTaskChangeTrackingName
--
-- Returns information about CBT for task source mode.
--
-- Parameters:
--     @source_mode     - specifies job task source mode
--     @change_tracking - specifies CBT mode
--
-- Result:
--     'with changed block tracking'
--     'without changed block tracking'
--
--------------------------------------------------------------------------------
PRINT 'Creating [dbo].[Upgrade_GetTaskChangeTrackingName]'
EXEC [dbo].[Upgrade_DbDeleteFunction] '[dbo].[Upgrade_GetTaskChangeTrackingName]'
GO

	CREATE FUNCTION [dbo].[Upgrade_GetTaskChangeTrackingName] (
		@source_mode int,
		@change_tracking bit)
	RETURNS nvarchar(255)
	AS
	BEGIN
		-- VddkSanNbd = 6
		-- VddkSanNbdSsl = 7
		-- VddkSan = 8
		-- VddkNbd = 9
		-- VddkNbdSsl = 10
		-- VddkHotAdd = 11
		-- VddkHotAddNbd = 12

		IF (@source_mode >= 6 AND @source_mode <= 12)
		BEGIN
			DECLARE @str nvarchar(255)

			IF (@change_tracking = 'TRUE')
				SET @str = 'with'
			ELSE
				SET @str = 'without'

			RETURN dbo.Upgrade_FormatString('%s changed block tracking', @str)
		END

		RETURN ''
	END
GO


--------------------------------------------------------------------------------
--
-- XML & Data Transformation
--
--------------------------------------------------------------------------------


--------------------------------------------------------------------------------
--
-- Upgrade_CreateBackupJobSessionXmlLog
--
-- Creates backup job session text report in 5.0 style.
--
--------------------------------------------------------------------------------
PRINT 'Creating [dbo].[Upgrade_CreateBackupJobSessionXmlLog]'
EXEC [dbo].[Upgrade_DbDeleteFunction] '[dbo].[Upgrade_CreateBackupJobSessionXmlLog]'
GO

	CREATE FUNCTION [dbo].[Upgrade_CreateBackupJobSessionXmlLog] (
		@session_id uniqueidentifier,
		@start_time datetime,
		@end_time datetime,
		@operation nvarchar(400),
		@description nvarchar(max),
		@total_objects int,
		@total_size bigint,
		@processed_size bigint,
		@average_speed bigint,
		@job_source_type int,
		@successes int,
		@warnings int,
		@failures int)
	RETURNS nvarchar(max)
	AS
	BEGIN
		-- Calculations

		DECLARE @session_info_count int
		SET @session_info_count = @failures + @warnings + @successes

		DECLARE @str_job_source_type nvarchar(255)
		SET @str_job_source_type = dbo.Upgrade_GetJobSourceTypeName(@job_source_type)
	
		-- Build Report

		DECLARE @result nvarchar(max)
		SET @result = ''

		SET @result = @result + dbo.Upgrade_CreateXmlLogRecord(
			0,
			@start_time,
			dbo.Upgrade_FormatString5('%s of %s %s processed (%s failed, %s warnings)', @session_info_count, @total_objects, @str_job_source_type, @failures, @warnings),
			'FALSE')

		SET @result = @result + dbo.Upgrade_CreateXmlLogRecord(
			1,
			@start_time,
			dbo.Upgrade_FormatString2('Total size of %s to backup: %s', @str_job_source_type, dbo.Upgrade_FormatSize(@total_size)),
			'FALSE')

		SET @result = @result + dbo.Upgrade_CreateXmlLogRecord(
			2,
			@start_time,
			'Processed size: ' + dbo.Upgrade_FormatSize(@processed_size),
			'FALSE')

		SET @result = @result + dbo.Upgrade_CreateXmlLogRecord(
			3,
			@start_time,
			'Processing rate: ' + dbo.Upgrade_FormatRate(@average_speed),
			'FALSE')

		SET @result = @result + dbo.Upgrade_CreateXmlLogRecord(
			4,
			@start_time,
			'Start time: ' + dbo.Upgrade_FormatDateTime(@start_time), --cast(@start_time as nvarchar(255)),
			'FALSE')

		SET @result = @result + dbo.Upgrade_CreateXmlLogRecord(
			5,
			@start_time,
			'End time: ' + dbo.Upgrade_FormatDateTime(@end_time), --cast(@end_time as nvarchar(255)),
			'FALSE')

		SET @result = @result + dbo.Upgrade_CreateXmlLogRecord(
			6,
			@start_time,
			'Duration: ' + dbo.Upgrade_FormatDateTimeDiff(@start_time, @end_time),
			'FALSE')

		DECLARE @index bigint
		SET @index = 6

		DECLARE @sep nvarchar(255)
		SET @sep = '. '

		IF (dbo.Upgrade_IsBlankString(@operation) = 'FALSE' OR dbo.Upgrade_IsBlankString(@description) = 'FALSE')
		BEGIN
			DECLARE @text nvarchar(max)
			SET @text = @operation + @sep + @description
			SET @text = replace(@text, dbo.Upgrade_TransformCppText('\r'), '')
			SET @text = replace(@text, dbo.Upgrade_TransformCppText('\n'), @sep)

			SELECT
				@index = @index + 1,
				@result = coalesce(@result + '', '') + dbo.Upgrade_CreateXmlLogRecord(@index, @start_time, [element], 'TRUE')
			FROM
				dbo.Upgrade_SplitString(@text, @sep)
			WHERE
				dbo.Upgrade_IsBlankString([element]) = 'FALSE'
		END

		RETURN '<Root TotalUsn="0" TotalId="' + cast(@index as nvarchar(255)) + '">' + @result + '</Root>'
	END
GO


--------------------------------------------------------------------------------
--
-- Upgrade_CreateBackupTaskSessionXmlLog
--
-- Creates backup task session text report in 5.0 style.
--
-- Parameters:
--     @session_id - specifies backup task session identifier
--
-- Example:
--     <Root TotalUsn="0" TotalId="...
--         <Log...>0 of 0 files processed</Log>
--         <Log...>Total VM size: 10,00 GB</Log>
--         <Log...>Processed size: 0,00 KB</Log>
--         <Log...>Processing rate: 0 KB/s</Log>
--         <Log...>Backup mode: SAN without changed block tracking</Log>
--         <Log...>Start time: 22.10.2011 12:17:51</Log>
--         <Log...>End time: 22.10.2011 12:17:52</Log>
--         <Log...>Duration: 0:00:01</Log>
--         <Log...>Validating task</Log>
--         <Log...>Failed to create VM locker</Log>
--         <Log...>Failed to process VM "2003-0%2f19" (Host "WheelJack") because it is already being processed by another job</Log>
--     </Root>
--
--------------------------------------------------------------------------------
PRINT 'Creating [dbo].[Upgrade_CreateBackupTaskSessionXmlLog]'
EXEC [dbo].[Upgrade_DbDeleteFunction] '[dbo].[Upgrade_CreateBackupTaskSessionXmlLog]'
GO

	CREATE FUNCTION [dbo].[Upgrade_CreateBackupTaskSessionXmlLog] (
		@id uniqueidentifier,
		@session_id uniqueidentifier,
		@start_time datetime,
		@end_time datetime,
		@operation nvarchar(400),
		@reason nvarchar(max),
		@total_objects int,
		@total_size bigint,
		@processed_objects int,
		@processed_size bigint,
		@average_speed bigint,
		@source_mode int,
		@change_tracking bit,
		@job_source_type int)
	RETURNS nvarchar(max)
	AS
	BEGIN
		-- Build Report

		DECLARE @result nvarchar(max)
		SET @result = ''

		SET @result = @result + dbo.Upgrade_CreateXmlLogRecord(
			0,
			@start_time,
			dbo.Upgrade_FormatString2('%s of %s files processed', @processed_objects, @total_objects),
			'FALSE')

		SET @result = @result + dbo.Upgrade_CreateXmlLogRecord(
			1,
			@start_time,
			dbo.Upgrade_FormatString2('Total %s size: %s', dbo.Upgrade_GetJobSourceTypeName(@job_source_type), dbo.Upgrade_FormatSize(@total_size)),
			'FALSE')

		SET @result = @result + dbo.Upgrade_CreateXmlLogRecord(
			2,
			@start_time,
			'Processed size: ' + dbo.Upgrade_FormatSize(@processed_size),
			'FALSE')

		SET @result = @result + dbo.Upgrade_CreateXmlLogRecord(
			3,
			@start_time,
			'Processing rate: ' + dbo.Upgrade_FormatRate(@average_speed),
			'FALSE')

		SET @result = @result + dbo.Upgrade_CreateXmlLogRecord(
			4,
			@start_time,
			dbo.Upgrade_FormatString2('Backup mode: %s %s', dbo.Upgrade_GetTaskSourceModeName(@source_mode), dbo.Upgrade_GetTaskChangeTrackingName(@source_mode, @change_tracking)),
			'FALSE')

		SET @result = @result + dbo.Upgrade_CreateXmlLogRecord(
			5,
			@start_time,
			'Start time: ' + dbo.Upgrade_FormatDateTime(@start_time), --cast(@start_time as nvarchar(255)),
			'FALSE')

		SET @result = @result + dbo.Upgrade_CreateXmlLogRecord(
			6,
			@start_time,
			'End time: ' + dbo.Upgrade_FormatDateTime(@end_time), --cast(@end_time as nvarchar(255)),
			'FALSE')

		SET @result = @result + dbo.Upgrade_CreateXmlLogRecord(
			7,
			@start_time,
			'Duration: ' + dbo.Upgrade_FormatDateTimeDiff(@start_time, @end_time),
			'FALSE')

		DECLARE @index bigint
		SET @index = 7

		DECLARE @sep nvarchar(255)
		SET @sep = '. '

		IF (dbo.Upgrade_IsBlankString(@operation) = 'FALSE' OR dbo.Upgrade_IsBlankString(@reason) = 'FALSE')
		BEGIN
			DECLARE @text nvarchar(max)
			SET @text = @operation + @sep + @reason
			SET @text = replace(@text, dbo.Upgrade_TransformCppText('\r'), '')
			SET @text = replace(@text, dbo.Upgrade_TransformCppText('\n'), @sep)

			SELECT
				@index = @index + 1,
				@result = coalesce(@result + '', '') + dbo.Upgrade_CreateXmlLogRecord(@index, @start_time, [element], 'TRUE')
			FROM
				dbo.Upgrade_SplitString(@text, @sep)
			WHERE
				dbo.Upgrade_IsBlankString([element]) = 'FALSE'
		END

		RETURN '<Root TotalUsn="0" TotalId="' + cast(@index as nvarchar(255)) + '">' + @result + '</Root>'
	END
GO


--------------------------------------------------------------------------------
--
-- Upgrade_CreateXmlLogRecord
--
-- Creates XML log record.
--
-- Parameters:
--     @id          - specifies position index of XML log record
--     @time        - specifies creation time
--     @title       - specifies log record's title
--     @make_pretty - specifies flag for optional title processing
--
-- Example:
--     <Log
--         Cookie=""
--         Usn="0"
--         Status="ESucceeded"
--         Style="ENone"
--         Id="12"
--         Time="2011-10-21T23:49:46.343"
--         UtcTime="10/21/2011 19:49:46"
--         StartTime="2011-10-21T23:49:46.343"
--     >
--         <Title>Failed to create VM locker</Title>
--         <Description/>
--     </Log>'
--
--------------------------------------------------------------------------------
PRINT 'Creating [dbo].[Upgrade_CreateXmlLogRecord]'
EXEC [dbo].[Upgrade_DbDeleteFunction] '[dbo].[Upgrade_CreateXmlLogRecord]'
GO

	CREATE FUNCTION [dbo].[Upgrade_CreateXmlLogRecord] (
		@id bigint,
		@time datetime,
		@title nvarchar(max),
		@make_pretty bit)
	RETURNS nvarchar(max)
	AS
	BEGIN
		DECLARE @str_time nvarchar(255)
		DECLARE @str_utc_time nvarchar(255)

		IF (@make_pretty = 'TRUE')
		BEGIN
			SET @title = ltrim(@title)
			SET @title = dbo.Upgrade_RightTrimString(@title, '. ')
			SET @title = dbo.Upgrade_EscapeText(@title)
		END

		SET @str_time = dbo.Upgrade_FormatIso8601DateTime(@time)
		SET @str_utc_time = dbo.Upgrade_FormatUtcInvariantDateTime(@time)

		RETURN
			'<Log Cookie="" Usn="0" Status="ESucceeded" Style="ENone" Id="' + cast(@id as nvarchar(255)) + 
			'" Time="' + @str_time + '" UtcTime="' + @str_utc_time + '" StartTime="' + @str_time + 
			'"><Title>' + @title + '</Title><Description/></Log>'
	END
GO


--------------------------------------------------------------------------------
--
-- Upgrade_CreateDefaultBackupRepositoryOptions
--
-- Creates default backup repository options.
--
-- Parameters:
--     none
--
-- Example:
--     <BackupRepositoryOptions/>
--
--------------------------------------------------------------------------------
PRINT 'Creating [dbo].[Upgrade_CreateDefaultBackupRepositoryOptions]'
EXEC [dbo].[Upgrade_DbDeleteFunction] '[dbo].[Upgrade_CreateDefaultBackupRepositoryOptions]'
GO

	CREATE FUNCTION [dbo].[Upgrade_CreateDefaultBackupRepositoryOptions] ()
	RETURNS xml
	AS
	BEGIN
		DECLARE @document xml
		SET @document = '<BackupRepositoryOptions/>'
		RETURN @document
	END
GO


--------------------------------------------------------------------------------
--
-- Upgrade_UpdateJobOptions
--
-- Updates job options from 5.x to 6.0.
--
-- Parameters:
--     @document - specifies job options
--
--------------------------------------------------------------------------------
PRINT 'Creating [dbo].[Upgrade_UpdateJobOptions]'
EXEC [dbo].[Upgrade_DbDeleteFunction] '[dbo].[Upgrade_UpdateJobOptions]'
GO

	CREATE FUNCTION [dbo].[Upgrade_UpdateJobOptions] (
		@document xml)
	RETURNS xml
	AS
	BEGIN
		SET @document.modify('insert element JobOptionsRoot{""} as last into (/)')

		-- Children
		SET @document.modify('insert /*[1]/RunManually into (/*[2])[1]')
		SET @document.modify('insert /*[1]/Algorithm into (/*[2])[1]')
		SET @document.modify('insert /*[1]/FullBackupScheduleKind into (/*[2])[1]')
		SET @document.modify('insert /*[1]/FullBackupDays into (/*[2])[1]')
		SET @document.modify('insert /*[1]/TransformFullToSyntethic into (/*[2])[1]')
		SET @document.modify('insert /*[1]/TransformIncrementsToSyntethic into (/*[2])[1]')
		SET @document.modify('insert /*[1]/TransformToSyntethicDays into (/*[2])[1]')
		SET @document.modify('insert /*[1]/FullBackupMonthlyScheduleOptions into (/*[2])[1]')
		SET @document.modify('insert /*[1]/SnmpNotification into (/*[2])[1]')
		SET @document.modify('insert /*[1]/EmailNotification into (/*[2])[1]')
		SET @document.modify('insert /*[1]/EmailNotificationAddresses into (/*[2])[1]')
		SET @document.modify('insert /*[1]/CheckRetention into (/*[2])[1]')
		SET @document.modify('insert /*[1]/RetainCycles into (/*[2])[1]')
		SET @document.modify('insert /*[1]/RetainDays into (/*[2])[1]')
		SET @document.modify('insert /*[1]/CompressionLevel into (/*[2])[1]')
		SET @document.modify('insert /*[1]/EnableDeduplication into (/*[2])[1]')
		SET @document.modify('insert /*[1]/StgBlockSize into (/*[2])[1]')
		-- EnableDataAlignment (new)
		SET @document.modify('insert /*[1]/EnableIntegrityChecks into (/*[2])[1]')
		SET @document.modify('insert /*[1]/EnableFullBackup into (/*[2])[1]')
		SET @document.modify('insert /*[1]/EncryptLanTraffic into (/*[2])[1]')
		SET @document.modify('insert /*[1]/FailoverToNetworkMode into (/*[2])[1]')
		SET @document.modify('insert /*[1]/VCBMode into (/*[2])[1]')
		SET @document.modify('insert /*[1]/VDDKMode into (/*[2])[1]')
		SET @document.modify('insert /*[1]/EnableDoubleSnapshot into (/*[2])[1]')
		SET @document.modify('insert /*[1]/DoubleSnapshotThresholdMb into (/*[2])[1]')
		SET @document.modify('insert /*[1]/UseChangeTracking into (/*[2])[1]')
		SET @document.modify('insert /*[1]/EnableChangeTracking into (/*[2])[1]')
		SET @document.modify('insert /*[1]/VMToolsQuiesce into (/*[2])[1]')
		SET @document.modify('insert /*[1]/VmAttributeName into (/*[2])[1]')
		SET @document.modify('insert /*[1]/Templates into (/*[2])[1]')
		-- ExcludeSwapFile (new)
		SET @document.modify('insert /*[1]/TemplatesOnce into (/*[2])[1]')
		SET @document.modify('insert /*[1]/SetResultsToVmNotes into (/*[2])[1]')
		-- PostJobCommand
		DECLARE @days xml
		SET @days = (SELECT
			T.c.value('.', 'nvarchar(max)') as [Day]
		FROM
			@document.nodes('/*[1]/PostCommand/Days/DayOfWeek') T(c)
		FOR XML RAW (''), ROOT ('Days'), ELEMENTS)

		SET @document.modify('delete /*/PostCommand/Days')

		SELECT @document = (SELECT @document '*', @days 'TempNode' FOR XML PATH(''), TYPE)

		SET @document.modify('insert element PostJobCommand{""} as last into (/*[2])[1]')
		SET @document.modify('insert /*[1]/PostCommand/* into (/*[2]/PostJobCommand)[1]')
		SET @document.modify('insert /TempNode/* into (/*[2]/PostJobCommand)[1]')
		SET @document.modify('delete /TempNode')

		-- Cleanup
		SET @document.modify('delete /*[1]')

		RETURN @document
	END
GO


--------------------------------------------------------------------------------
--
-- Upgrade_UpdateJobScheduleOptions
--
-- Updates job schedule options from 5.x to 6.0.
--
-- Parameters:
--     @document - specifies job schedule options
--
--------------------------------------------------------------------------------
PRINT 'Creating [dbo].[Upgrade_UpdateJobScheduleOptions]'
EXEC [dbo].[Upgrade_DbDeleteFunction] '[dbo].[Upgrade_UpdateJobScheduleOptions]'
GO

	CREATE FUNCTION [dbo].[Upgrade_UpdateJobScheduleOptions] (
		@document xml)
	RETURNS xml
	AS
	BEGIN
		-- Removing namespaces for root node
		SET @document.modify('insert element ScheduleOptions{""} as last into (/)')
		SET @document.modify('insert /*[1]/* into (/*[2])[1]')
		SET @document.modify('delete /*[1]')
		RETURN @document
	END
GO


--------------------------------------------------------------------------------
--
-- Upgrade_UpdateJobVssOptions
--
-- Updates job VSS options from 5.x to 6.0.
--
-- Parameters:
--     @document - specifies job VSS options
--
--------------------------------------------------------------------------------
PRINT 'Creating [dbo].[Upgrade_UpdateJobVssOptions]'
EXEC [dbo].[Upgrade_DbDeleteFunction] '[dbo].[Upgrade_UpdateJobVssOptions]'
GO

	CREATE FUNCTION [dbo].[Upgrade_UpdateJobVssOptions] (
		@document xml)
	RETURNS xml
	AS
	BEGIN
		-- Removing namespaces for root node
		SET @document.modify('insert element CVssOptions{""} as last into (/)')
		SET @document.modify('insert /*[1]/* into (/*[2])[1]')
		SET @document.modify('delete /*[1]')
		RETURN @document
	END
GO


--------------------------------------------------------------------------------
--
-- Upgrade_UpdateLegacyReplicaJobOptions
--
-- Updates replica job options from 5.x to 6.0 to become 'Legacy Replica'.
--
-- Parameters:
--     @document - specifies replica job options
--
--------------------------------------------------------------------------------
PRINT 'Creating [dbo].[Upgrade_UpdateLegacyReplicaJobOptions]'
EXEC [dbo].[Upgrade_DbDeleteFunction] '[dbo].[Upgrade_UpdateLegacyReplicaJobOptions]'
GO

	CREATE FUNCTION [dbo].[Upgrade_UpdateLegacyReplicaJobOptions] (
		@document xml)
	RETURNS xml
	AS
	BEGIN
		-- PostJobCommand
		SET @document.modify('insert element PostJobCommand{""} as last into (/*[1])[1]')
		SET @document.modify('insert /*[1]/PostCommand/* into (/*[1]/PostJobCommand)[1]')

		-- Cleanup
		SET @document.modify('delete /*[1]/PostCommand')

		RETURN @document
	END
GO


--============================================================================--


--------------------------------------------------------------------------------
--
-- Begin Upgrade
--
--------------------------------------------------------------------------------
SET NOCOUNT ON

DECLARE @db_version int
SET @db_version = (SELECT max([current_version]) FROM [dbo].[Version])

PRINT ''
PRINT 'Upgrading database...'
PRINT dbo.Upgrade_FormatString('\t[Time Stamp]="%s"', convert(varchar, getdate(), 113))
PRINT dbo.Upgrade_TransformCppText('\t[Current Version]="5.X"')
PRINT dbo.Upgrade_TransformCppText('\t[Next Version]="6.0"')
PRINT dbo.Upgrade_FormatString('\t[Database Version]="%s"', @db_version)
PRINT dbo.Upgrade_FormatString('\t[Server Name]="%s"', @@servername)
PRINT dbo.Upgrade_FormatString('\t[Process ID]="%s"', @@spid)
PRINT dbo.Upgrade_FormatString('\t[User Name]="%s"', SYSTEM_USER)
PRINT ''

GO


--------------------------------------------------------------------------------
--
-- Backup Job / Backup Repositories / Backups
--
--------------------------------------------------------------------------------
PRINT 'Upgrading "Backup Jobs", "Backup Repositories" and "Backups"...'
PRINT dbo.Upgrade_FormatString('\t[Start Date]="%s"', convert(varchar, getdate(), 113))
PRINT ''

DECLARE @job_count int
SET @job_count = 0


DECLARE backup_jobs_cursor CURSOR FOR
SELECT
	[id],
	[name],
	[target_dir],
	[target_host_id],
	[options],
	[schedule],
	[vss_options]
FROM
	[dbo].[BJobs]
WHERE
	[type] = 0 AND
	[repository_id] IS NULL

OPEN backup_jobs_cursor

DECLARE @job_id uniqueidentifier
DECLARE @job_name nvarchar(255)
DECLARE @job_target_dir nvarchar(2000)
DECLARE @job_target_host_id uniqueidentifier
DECLARE @job_options xml
DECLARE @job_schedule_options xml
DECLARE @job_vss_options xml

FETCH NEXT FROM
	backup_jobs_cursor
INTO
	@job_id,
	@job_name,
	@job_target_dir,
	@job_target_host_id,
	@job_options,
	@job_schedule_options,
	@job_vss_options

WHILE @@fetch_status = 0
BEGIN                                                             
	BEGIN TRANSACTION t_backup_jobs
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

	PRINT dbo.Upgrade_TransformCppText('BEGIN ITERATION: BACKUP JOB')
	PRINT dbo.Upgrade_TransformCppText('\tLoading "Backup Job":')
	PRINT dbo.Upgrade_FormatString('\t\t[Id]="%s"', @job_id)
	PRINT dbo.Upgrade_FormatString('\t\t[Name]="%s"', @job_name)
	PRINT dbo.Upgrade_FormatString('\t\t[Target Directory]="%s"', @job_target_dir)
	PRINT dbo.Upgrade_FormatString('\t\t[Target Host ID]="%s"', @job_target_host_id)
	PRINT dbo.Upgrade_TransformCppText('\t\t[Options]=')
	PRINT dbo.Upgrade_FormatXmlDocument(@job_options, 3, DEFAULT)
	PRINT dbo.Upgrade_TransformCppText('\t\t[VSS Options]=')
	PRINT dbo.Upgrade_FormatXmlDocument(@job_vss_options, 3, DEFAULT)

	-- Backup Repository
	PRINT dbo.Upgrade_FormatString('\tSearching type for host [ID]="%s"...', @job_target_host_id)

	IF NOT EXISTS (SELECT TOP 1 * FROM [dbo].[Hosts] WHERE [id] = @job_target_host_id)
	BEGIN
		PRINT dbo.Upgrade_FormatString('\tERROR: cannot find "Host" for "Backup Job" [ID]="%s".', @job_id)
		PRINT dbo.Upgrade_TransformCppText('SKIP ITERATION: BACKUP JOB')

		GOTO NextBackupJobIteration
	END

	DECLARE @host_name nvarchar(255)
	DECLARE @host_type int

	SELECT TOP 1
		@host_name = [name],
		@host_type = [type]
	FROM
		[dbo].[Hosts]
	WHERE
		[id] = @job_target_host_id

	PRINT dbo.Upgrade_FormatString('\tFound host [ID]="%s":', @job_target_host_id)
	PRINT dbo.Upgrade_FormatString('\t\t[File System Type]="%s"', dbo.Upgrade_GetFileSystemType(@job_target_dir))
	PRINT dbo.Upgrade_FormatString('\t\t[Host Name]="%s"', @host_name)
	PRINT dbo.Upgrade_FormatString('\t\t[Host Type]="%s"', @host_type)

	DECLARE @repository_type int
	SET @repository_type = dbo.Upgrade_GetBackupRepositoryType(@host_type, @job_target_dir)

	IF (@repository_type = -1)
	BEGIN
		PRINT dbo.Upgrade_FormatString2('\tERROR: invalid repository type for [Host Type]="%s" and [Target Dir]="%s".', @host_type, @job_target_dir)
		PRINT dbo.Upgrade_TransformCppText('SKIP ITERATION: BACKUP JOB')

		GOTO NextBackupJobIteration
	END

	SET @job_target_dir = dbo.Upgrade_FileSystemRemoveSeparator(@job_target_dir)

	PRINT dbo.Upgrade_FormatString('\tSearching existed repository with [Target Dir]="%s"...', @job_target_dir)

	DECLARE @repository_id uniqueidentifier
	SET @repository_id = (SELECT TOP 1 [id] FROM [dbo].[BackupRepositories] WHERE dbo.Upgrade_FileSystemComparePaths([path], @job_target_dir) = 'TRUE')

	PRINT dbo.Upgrade_FormatString('\tFound repository [ID]="%s"', @repository_id)

	IF (@repository_id IS NULL)
	BEGIN
		PRINT dbo.Upgrade_TransformCppText('BEGIN ITERATION: BACKUP REPOSITORY')

		SET @repository_id = newid()

		DECLARE @repository_name nvarchar(max)
		DECLARE @repository_options xml

		SET @repository_name = dbo.Upgrade_FormatString2('Backup Repository (%s, ''%s'')', @host_name, @job_target_dir)
		SET @repository_options = dbo.Upgrade_CreateDefaultBackupRepositoryOptions()

		PRINT dbo.Upgrade_TransformCppText('\tCreating "Backup Repository":')
		PRINT dbo.Upgrade_FormatString('\t\t[ID]="%s"', @repository_id)
		PRINT dbo.Upgrade_FormatString('\t\t[Name]="%s"', cast(@repository_name as nvarchar(255)))
		PRINT dbo.Upgrade_FormatString2('\t\t[Repository Type]="%s (%s)"', @repository_type, dbo.Upgrade_GetBackupRepositoryTypeName(@repository_type))
		PRINT dbo.Upgrade_FormatString('\t\t[Repository Host ID]="%s"', @job_target_host_id)
		PRINT dbo.Upgrade_FormatString('\t\t[Repository Folder]="%s"', @job_target_dir)
		PRINT dbo.Upgrade_TransformCppText('\t\t[Options]=')
		PRINT dbo.Upgrade_FormatXmlDocument(@repository_options, 3, DEFAULT)

		INSERT INTO
			[dbo].[BackupRepositories]
		VALUES
		(
			@repository_id,
			@repository_name,
			'Created by Veeam Backup (upgrade)',
			@repository_type,
			@job_target_host_id,
			'6745a759-2205-4cd2-b172-8ec8f7e60ef8',
			@job_target_dir,
			@repository_options,
			0,
			NULL
		)

		PRINT dbo.Upgrade_TransformCppText('END ITERATION: BACKUP REPOSITORY')
	END

	-- Backup Jobs

	SET @job_options = dbo.Upgrade_UpdateJobOptions(@job_options)
	SET @job_schedule_options = dbo.Upgrade_UpdateJobScheduleOptions(@job_schedule_options)	
	SET @job_vss_options = dbo.Upgrade_UpdateJobVssOptions(@job_vss_options)	

	PRINT dbo.Upgrade_TransformCppText('\tUpdating "Backup Job":')
	PRINT dbo.Upgrade_FormatString('\t\t[ID]="%s"', @job_id)
	PRINT dbo.Upgrade_FormatString('\t\t[Repository ID]="%s"', @repository_id)
	PRINT dbo.Upgrade_TransformCppText('\t\t[Options]=')
	PRINT dbo.Upgrade_FormatXmlDocument(@job_options, 3, DEFAULT)
	PRINT dbo.Upgrade_TransformCppText('\t\t[VSS_Options]=')
	PRINT dbo.Upgrade_FormatXmlDocument(@job_vss_options, 3, DEFAULT)

	UPDATE
		[dbo].[BJobs]
	SET
		[repository_id] = @repository_id,
		[initialsync_repository_id] = '00000000-0000-0000-0000-000000000000',
		[options] = @job_options,
		[schedule] = @job_schedule_options,
		[vss_options] = @job_vss_options
	WHERE
		[id] = @job_id

	-- Backups

	DECLARE backup_cursor CURSOR FOR
	SELECT
		[id],
		[job_name]
	FROM
		[dbo].[Backup.Model.Backups]
	WHERE
		[job_id] =  @job_id

	OPEN backup_cursor

	DECLARE @backup_id uniqueidentifier
	DECLARE @backup_job_name nvarchar(255)
	DECLARE @backup_dir_path nvarchar(1000)

	FETCH NEXT FROM
		backup_cursor
	INTO
		@backup_id,
		@backup_job_name

	WHILE @@fetch_status = 0
	BEGIN
		PRINT dbo.Upgrade_TransformCppText('BEGIN ITERATION: BACKUP')
		PRINT dbo.Upgrade_TransformCppText('\tLoading "Backup":')
		PRINT dbo.Upgrade_FormatString('\t\t[ID]="%s"', @backup_id)
		PRINT dbo.Upgrade_FormatString('\t\t[Name]="%s"', @backup_job_name)
		PRINT dbo.Upgrade_FormatString('\t\t[Job ID]="%s"', @job_id)
		PRINT dbo.Upgrade_FormatString('\tSearching "Storage": [Backup ID]="%s"...', @backup_id)

		DECLARE @storage_id uniqueidentifier
		DECLARE @storage_file_path nvarchar(1000)

		IF NOT EXISTS (SELECT TOP 1 * FROM [dbo].[Backup.Model.Storages] WHERE [backup_id] = @backup_id ORDER BY [creation_time] DESC)
		BEGIN
			PRINT dbo.Upgrade_FormatString('\tERROR: cannot find "Storage" for "Backup" [ID]="%s".', @backup_id)
			PRINT dbo.Upgrade_TransformCppText('SKIP ITERATION: BACKUP')

			GOTO NextBackupIteration
		END

		SELECT TOP 1
			@storage_id = [id],
			@storage_file_path = [file_path]
		FROM
			[dbo].[Backup.Model.Storages]
		WHERE
			[backup_id] = @backup_id
		ORDER BY
			[creation_time]
		DESC

		PRINT dbo.Upgrade_TransformCppText('\tLoading "Storage":')
		PRINT dbo.Upgrade_FormatString('\t\t[ID]="%s"', @storage_id)
		PRINT dbo.Upgrade_FormatString('\t\t[Backup ID]="%s"', @backup_id)
		PRINT dbo.Upgrade_FormatString('\t\t[File Path]="%s"', @storage_file_path)

		SET @backup_dir_path = dbo.Upgrade_GetFileSystemParentFolderEx(@storage_file_path)			

		PRINT dbo.Upgrade_TransformCppText('\tUpdating "Backup":')
		PRINT dbo.Upgrade_FormatString('\t\t[ID]="%s"', @backup_id)
		PRINT dbo.Upgrade_FormatString('\t\t[Job ID]="%s"', @job_id)
		PRINT dbo.Upgrade_FormatString('\t\t[Dir Path]="%s"', @backup_dir_path)
		PRINT dbo.Upgrade_FormatString('\t\t[Repository ID]="%s"', @repository_id)

		UPDATE
			[dbo].[Backup.Model.Backups]
		SET
			[target_type] = 0,
			[dir_path] = @backup_dir_path,
			[repository_id] = @repository_id
		WHERE
			[job_id] = @job_id

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

		PRINT dbo.Upgrade_TransformCppText('END ITERATION: BACKUP')

NextBackupIteration:
		FETCH NEXT FROM
			backup_cursor
		INTO
			@backup_id,
			@backup_job_name
	END

	CLOSE backup_cursor
	DEALLOCATE backup_cursor
	
	----------------------------------------------------------------------------

	PRINT dbo.Upgrade_TransformCppText('END ITERATION: BACKUP JOB')

NextBackupJobIteration:
	COMMIT TRANSACTION t_backup_jobs

	SET @job_count = @job_count + 1

	FETCH NEXT FROM
		backup_jobs_cursor
	INTO
		@job_id,
		@job_name,
		@job_target_dir,
		@job_target_host_id,
		@job_options,
		@job_schedule_options,
		@job_vss_options
END

CLOSE backup_jobs_cursor
DEALLOCATE backup_jobs_cursor


PRINT dbo.Upgrade_FormatString('\t[Job Count]="%s"', @job_count)
PRINT dbo.Upgrade_FormatString('\t[Total Read]="%s"', @@total_read)
PRINT dbo.Upgrade_FormatString('\t[Total Write]="%s"', @@total_write)
PRINT dbo.Upgrade_FormatString('\t[Total Errors]="%s"', @@total_errors)
PRINT dbo.Upgrade_FormatString('\t[End Date]="%s"', convert(varchar, getdate(), 113))
PRINT ''
GO


--------------------------------------------------------------------------------
--
-- Replication Job
--
--------------------------------------------------------------------------------
PRINT 'Upgrading "Replication Jobs"...'
PRINT dbo.Upgrade_FormatString('\t[Start Date]="%s"', convert(varchar, getdate(), 113))
PRINT ''

DECLARE @job_count int
SET @job_count = 0


DECLARE replication_jobs_cursor CURSOR FOR
SELECT
	[id],
	[name],
	[options],
	[schedule],
	[vss_options]
FROM
	[dbo].[BJobs]
WHERE
	[type] = 1 AND
	[repository_id] IS NULL

OPEN replication_jobs_cursor

DECLARE @job_id uniqueidentifier
DECLARE @job_name nvarchar(255)
DECLARE @job_options xml
DECLARE @job_schedule_options xml
DECLARE @job_vss_options xml

FETCH NEXT FROM
	replication_jobs_cursor
INTO
	@job_id,
	@job_name,
	@job_options,
	@job_schedule_options,
	@job_vss_options

WHILE @@fetch_status = 0
BEGIN                                                             
	BEGIN TRANSACTION t_replication_jobs
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

	PRINT dbo.Upgrade_TransformCppText('BEGIN ITERATION: REPLICATION JOB')
	PRINT dbo.Upgrade_TransformCppText('\tLoading "SureBackup Job":')
	PRINT dbo.Upgrade_FormatString('\t\t[Id]="%s"', @job_id)
	PRINT dbo.Upgrade_FormatString('\t\t[Name]="%s"', @job_name)
	PRINT dbo.Upgrade_TransformCppText('\t\t[Options]=')
	PRINT dbo.Upgrade_FormatXmlDocument(@job_options, 3, DEFAULT)
	PRINT dbo.Upgrade_TransformCppText('\t\t[Schedule Options]=')
	PRINT dbo.Upgrade_FormatXmlDocument(@job_schedule_options, 3, DEFAULT)
	PRINT dbo.Upgrade_TransformCppText('\t\t[VSS Options]=')
	PRINT dbo.Upgrade_FormatXmlDocument(@job_vss_options, 3, DEFAULT)

	-- Replication Jobs

	SET @job_options = dbo.Upgrade_UpdateLegacyReplicaJobOptions(@job_options)
	SET @job_schedule_options = dbo.Upgrade_UpdateJobScheduleOptions(@job_schedule_options)	
	SET @job_vss_options = dbo.Upgrade_UpdateJobVssOptions(@job_vss_options)	

	PRINT dbo.Upgrade_TransformCppText('\tUpdating "Replication Job":')
	PRINT dbo.Upgrade_FormatString('\t\t[ID]="%s"', @job_id)
	PRINT dbo.Upgrade_TransformCppText('\t\t[Options]=')
	PRINT dbo.Upgrade_FormatXmlDocument(@job_options, 3, DEFAULT)
	PRINT dbo.Upgrade_TransformCppText('\t\t[Schedule Options]=')
	PRINT dbo.Upgrade_FormatXmlDocument(@job_schedule_options, 3, DEFAULT)
	PRINT dbo.Upgrade_TransformCppText('\t\t[VSS_Options]=')
	PRINT dbo.Upgrade_FormatXmlDocument(@job_vss_options, 3, DEFAULT)

	UPDATE
		[dbo].[BJobs]
	SET
		[options] = @job_options,
		[schedule] = @job_schedule_options,
		[vss_options] = @job_vss_options,
		[repository_id] = '00000000-0000-0000-0000-000000000000'
	WHERE
		[id] = @job_id

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

	PRINT dbo.Upgrade_TransformCppText('END ITERATION: REPLICATION JOB')

NextReplicationJobIteration:
	COMMIT TRANSACTION t_replication_jobs

	SET @job_count = @job_count + 1

	FETCH NEXT FROM
		replication_jobs_cursor
	INTO
		@job_id,
		@job_name,
		@job_options,
		@job_schedule_options,
		@job_vss_options
END

CLOSE replication_jobs_cursor
DEALLOCATE replication_jobs_cursor

PRINT dbo.Upgrade_FormatString('\t[Job Count]="%s"', @job_count)
PRINT dbo.Upgrade_FormatString('\t[Total Read]="%s"', @@total_read)
PRINT dbo.Upgrade_FormatString('\t[Total Write]="%s"', @@total_write)
PRINT dbo.Upgrade_FormatString('\t[Total Errors]="%s"', @@total_errors)
PRINT dbo.Upgrade_FormatString('\t[End Date]="%s"', convert(varchar, getdate(), 113))
PRINT ''
GO


--------------------------------------------------------------------------------
--
-- Copy Job
--
--------------------------------------------------------------------------------
PRINT 'Upgrading "Copy Jobs"...'
PRINT dbo.Upgrade_FormatString('\t[Start Date]="%s"', convert(varchar, getdate(), 113))
PRINT ''

DECLARE @job_count int
SET @job_count = 0

DECLARE copy_jobs_cursor CURSOR FOR
SELECT
	[id],
	[name],
	[options],
	[schedule],
	[vss_options]
FROM
	[dbo].[BJobs]
WHERE
	[type] = 2 AND
	[repository_id] IS NULL

OPEN copy_jobs_cursor

DECLARE @job_id uniqueidentifier
DECLARE @job_name nvarchar(255)
DECLARE @job_options xml
DECLARE @job_schedule_options xml
DECLARE @job_vss_options xml

FETCH NEXT FROM
	copy_jobs_cursor
INTO
	@job_id,
	@job_name,
	@job_options,
	@job_schedule_options,
	@job_vss_options

WHILE @@fetch_status = 0
BEGIN                                                             
	BEGIN TRANSACTION t_copy_jobs
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

	PRINT dbo.Upgrade_TransformCppText('BEGIN ITERATION: COPY JOB')
	PRINT dbo.Upgrade_TransformCppText('\tLoading "Copy Job":')
	PRINT dbo.Upgrade_FormatString('\t\t[Id]="%s"', @job_id)
	PRINT dbo.Upgrade_FormatString('\t\t[Name]="%s"', @job_name)
	PRINT dbo.Upgrade_TransformCppText('\t\t[Options]=')
	PRINT dbo.Upgrade_FormatXmlDocument(@job_options, 3, DEFAULT)
	PRINT dbo.Upgrade_TransformCppText('\t\t[Schedule Options]=')
	PRINT dbo.Upgrade_FormatXmlDocument(@job_schedule_options, 3, DEFAULT)
	PRINT dbo.Upgrade_TransformCppText('\t\t[VSS Options]=')
	PRINT dbo.Upgrade_FormatXmlDocument(@job_vss_options, 3, DEFAULT)

	-- Copy Jobs

	SET @job_options = dbo.Upgrade_UpdateJobOptions(@job_options)
	SET @job_schedule_options = dbo.Upgrade_UpdateJobScheduleOptions(@job_schedule_options)	
	SET @job_vss_options = dbo.Upgrade_UpdateJobVssOptions(@job_vss_options)	

	PRINT dbo.Upgrade_TransformCppText('\tUpdating "Copy Job":')
	PRINT dbo.Upgrade_FormatString('\t\t[ID]="%s"', @job_id)
	PRINT dbo.Upgrade_TransformCppText('\t\t[Options]=')
	PRINT dbo.Upgrade_FormatXmlDocument(@job_options, 3, DEFAULT)
	PRINT dbo.Upgrade_TransformCppText('\t\t[Schedule Options]=')
	PRINT dbo.Upgrade_FormatXmlDocument(@job_schedule_options, 3, DEFAULT)
	PRINT dbo.Upgrade_TransformCppText('\t\t[VSS_Options]=')
	PRINT dbo.Upgrade_FormatXmlDocument(@job_vss_options, 3, DEFAULT)

	UPDATE
		[dbo].[BJobs]
	SET
		[options] = @job_options,
		[schedule] = @job_schedule_options,
		[vss_options] = @job_vss_options,
		[repository_id] = '00000000-0000-0000-0000-000000000000'
	WHERE
		[id] = @job_id

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

	PRINT dbo.Upgrade_TransformCppText('END ITERATION: COPY JOB')

NextCopyJobIteration:
	COMMIT TRANSACTION t_copy_jobs

	SET @job_count = @job_count + 1

	FETCH NEXT FROM
		copy_jobs_cursor
	INTO
		@job_id,
		@job_name,
		@job_options,
		@job_schedule_options,
		@job_vss_options
END

CLOSE copy_jobs_cursor
DEALLOCATE copy_jobs_cursor

PRINT dbo.Upgrade_FormatString('\t[Job Count]="%s"', @job_count)
PRINT dbo.Upgrade_FormatString('\t[Total Read]="%s"', @@total_read)
PRINT dbo.Upgrade_FormatString('\t[Total Write]="%s"', @@total_write)
PRINT dbo.Upgrade_FormatString('\t[Total Errors]="%s"', @@total_errors)
PRINT dbo.Upgrade_FormatString('\t[End Date]="%s"', convert(varchar, getdate(), 113))
PRINT ''
GO


--------------------------------------------------------------------------------
--
-- SureBackup Job
--
--------------------------------------------------------------------------------
PRINT 'Upgrading "SureBackup Jobs..."'
PRINT dbo.Upgrade_FormatString('\t[Start Date]="%s"', convert(varchar, getdate(), 113))
PRINT ''

DECLARE @job_count int
SET @job_count = 0

DECLARE surebackup_jobs_cursor CURSOR FOR
SELECT
	[id],
	[name],
	[options],
	[schedule],
	[vss_options]
FROM
	[dbo].[BJobs]
WHERE
	[type] = 3 AND
	[repository_id] IS NULL

OPEN surebackup_jobs_cursor

DECLARE @job_id uniqueidentifier
DECLARE @job_name nvarchar(255)
DECLARE @job_options xml
DECLARE @job_schedule_options xml
DECLARE @job_vss_options xml

FETCH NEXT FROM
	surebackup_jobs_cursor
INTO
	@job_id,
	@job_name,
	@job_options,
	@job_schedule_options,
	@job_vss_options

WHILE @@fetch_status = 0
BEGIN                                                             
	BEGIN TRANSACTION t_surebackup_jobs
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

	PRINT dbo.Upgrade_TransformCppText('BEGIN ITERATION: SUREBACKUP JOB')
	PRINT dbo.Upgrade_TransformCppText('\tLoading "SureBackup Job":')
	PRINT dbo.Upgrade_FormatString('\t\t[Id]="%s"', @job_id)
	PRINT dbo.Upgrade_FormatString('\t\t[Name]="%s"', @job_name)
	PRINT dbo.Upgrade_TransformCppText('\t\t[Options]=')
	PRINT dbo.Upgrade_FormatXmlDocument(@job_options, 3, DEFAULT)
	PRINT dbo.Upgrade_TransformCppText('\t\t[Schedule Options]=')
	PRINT dbo.Upgrade_FormatXmlDocument(@job_schedule_options, 3, DEFAULT)
	PRINT dbo.Upgrade_TransformCppText('\t\t[VSS Options]=')
	PRINT dbo.Upgrade_FormatXmlDocument(@job_vss_options, 3, DEFAULT)

	-- SureBackup Jobs

	SET @job_schedule_options = dbo.Upgrade_UpdateJobScheduleOptions(@job_schedule_options)	
	SET @job_vss_options = dbo.Upgrade_UpdateJobVssOptions(@job_vss_options)	

	PRINT dbo.Upgrade_TransformCppText('\tUpdating "SureBackup Job":')
	PRINT dbo.Upgrade_FormatString('\t\t[ID]="%s"', @job_id)
	PRINT dbo.Upgrade_TransformCppText('\t\t[Options]=')
	PRINT dbo.Upgrade_FormatXmlDocument(@job_options, 3, DEFAULT)
	PRINT dbo.Upgrade_TransformCppText('\t\t[Schedule Options]=')
	PRINT dbo.Upgrade_FormatXmlDocument(@job_schedule_options, 3, DEFAULT)
	PRINT dbo.Upgrade_TransformCppText('\t\t[VSS_Options]=')
	PRINT dbo.Upgrade_FormatXmlDocument(@job_vss_options, 3, DEFAULT)

	UPDATE
		[dbo].[BJobs]
	SET
		[options] = @job_options,
		[schedule] = @job_schedule_options,
		[vss_options] = @job_vss_options,
		[repository_id] = '00000000-0000-0000-0000-000000000000'
	WHERE
		[id] = @job_id

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

	PRINT dbo.Upgrade_TransformCppText('END ITERATION: SUREBACKUP JOB')

NextSureBackupJobIteration:
	COMMIT TRANSACTION t_surebackup_jobs

	SET @job_count = @job_count + 1

	FETCH NEXT FROM
		surebackup_jobs_cursor
	INTO
		@job_id,
		@job_name,
		@job_options,
		@job_schedule_options,
		@job_vss_options
END

CLOSE surebackup_jobs_cursor
DEALLOCATE surebackup_jobs_cursor

PRINT dbo.Upgrade_FormatString('\t[Job Count]="%s"', @job_count)
PRINT dbo.Upgrade_FormatString('\t[Total Read]="%s"', @@total_read)
PRINT dbo.Upgrade_FormatString('\t[Total Write]="%s"', @@total_write)
PRINT dbo.Upgrade_FormatString('\t[Total Errors]="%s"', @@total_errors)
PRINT dbo.Upgrade_FormatString('\t[End Date]="%s"', convert(varchar, getdate(), 113))
PRINT ''
GO


--------------------------------------------------------------------------------
--
-- Job Sessions
--
--------------------------------------------------------------------------------
SET NOCOUNT ON
PRINT 'Upgrading "Backup Job Sessions"...'

DECLARE @error nvarchar(max)
DECLARE @job_count int
SET @job_count = 0

PRINT dbo.Upgrade_TransformCppText('BEGIN TRANSACTION: BACKUP JOB SESSIONS')
PRINT dbo.Upgrade_FormatString('\t[Start Date]="%s"', convert(varchar, getdate(), 113))
PRINT ''
BEGIN TRANSACTION t_sessions
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

DECLARE sessions_cursor CURSOR FOR
SELECT
	[dbo].[Backup.Model.JobSessions].[id],
	[dbo].[Backup.Model.JobSessions].[creation_time],
	[dbo].[Backup.Model.JobSessions].[end_time],
	[dbo].[Backup.Model.JobSessions].[operation],
	[dbo].[Backup.Model.JobSessions].[description],
	[dbo].[Backup.Model.BackupJobSessions].[total_objects],
	[dbo].[Backup.Model.BackupJobSessions].[total_size],
	[dbo].[Backup.Model.BackupJobSessions].[processed_size],
	[dbo].[Backup.Model.BackupJobSessions].[avg_speed],
	[dbo].[Backup.Model.BackupJobSessions].[job_source_type],

	successes = (SELECT
			COUNT([dbo].[Backup.Model.BackupTaskSessions].[id])
		FROM
			[dbo].[Backup.Model.BackupTaskSessions] 
		WHERE
			[dbo].[Backup.Model.BackupTaskSessions].[session_id] = [dbo].[Backup.Model.BackupJobSessions].[id] AND [dbo].[Backup.Model.BackupTaskSessions].[status] = 0),

	warnings = (SELECT
			COUNT([dbo].[Backup.Model.BackupTaskSessions].[id])
		FROM
			[dbo].[Backup.Model.BackupTaskSessions] 
		WHERE
			[dbo].[Backup.Model.BackupTaskSessions].[session_id] = [dbo].[Backup.Model.BackupJobSessions].[id] AND [dbo].[Backup.Model.BackupTaskSessions].[status] = 3),

	errors = (SELECT
			COUNT([dbo].[Backup.Model.BackupTaskSessions].[id])
		FROM
			[dbo].[Backup.Model.BackupTaskSessions] 
		WHERE
			[dbo].[Backup.Model.BackupTaskSessions].[session_id] = [dbo].[Backup.Model.BackupJobSessions].[id] AND [dbo].[Backup.Model.BackupTaskSessions].[status] = 2)

FROM
	[dbo].[Backup.Model.JobSessions] RIGHT JOIN [dbo].[Backup.Model.BackupJobSessions] 
ON
	[dbo].[Backup.Model.JobSessions].[id] = [dbo].[Backup.Model.BackupJobSessions].[id]
WHERE
	[log_xml] IS NOT NULL AND
	[log_xml].value('count(/*[1]/*)', 'int') = 0

DECLARE @id uniqueidentifier
DECLARE @start_time datetime
DECLARE @end_time datetime
DECLARE @total_objects int
DECLARE @total_size bigint
DECLARE @processed_size bigint
DECLARE @average_speed bigint
DECLARE @operation nvarchar(400)
DECLARE @description nvarchar(max)
DECLARE @job_source_type int
DECLARE @successes int
DECLARE @warnings int
DECLARE @failures int

OPEN sessions_cursor

FETCH NEXT FROM
	sessions_cursor
INTO
	@id,
	@start_time,
	@end_time,
	@operation,
	@description,
	@total_objects,
	@total_size,
	@processed_size,
	@average_speed,
	@job_source_type,
	@successes,
	@warnings,
	@failures

WHILE @@fetch_status = 0
BEGIN                                                             
--	PRINT dbo.Upgrade_TransformCppText('BEGIN ITERATION: BACKUP JOB SESSION')
--	PRINT dbo.Upgrade_TransformCppText('\tLoading "Backup Job Session":')
--	PRINT dbo.Upgrade_FormatString('\t\t[Id]="%s"', @id)
--	PRINT dbo.Upgrade_FormatString('\t\t[Start Time]="%s"', @start_time)
--	PRINT dbo.Upgrade_FormatString('\t\t[End Time]="%s"', @end_time)
--	PRINT dbo.Upgrade_FormatString('\t\t[Operation]="%s"', @operation)
--	PRINT dbo.Upgrade_FormatString('\t\t[Description]="%s"', cast(@description as nvarchar(255)))
--	PRINT dbo.Upgrade_FormatString('\t\t[Total Objects]="%s"', @total_objects)
--	PRINT dbo.Upgrade_FormatString('\t\t[Total Size]="%s"', @total_size)
--	PRINT dbo.Upgrade_FormatString('\t\t[Processed Size]="%s"', @processed_size)
--	PRINT dbo.Upgrade_FormatString('\t\t[Average Speed]="%s"', @average_speed)
--	PRINT dbo.Upgrade_FormatString('\t\t[Job Source Type]="%s"', @job_source_type)
--	PRINT dbo.Upgrade_FormatString('\t\t[Failures]="%s"', @failures)
--	PRINT dbo.Upgrade_FormatString('\t\t[Warnings]="%s"', @warnings)
--	PRINT dbo.Upgrade_FormatString('\t\t[Successes]="%s"', @successes)

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

--	PRINT dbo.Upgrade_TransformCppText('\tCreating text report based on task session data...')

	-- Build Report

	DECLARE @xml_log nvarchar(max)
	SET @xml_log = dbo.Upgrade_CreateBackupJobSessionXmlLog(
		@id,
		@start_time,
		@end_time,
		@operation,
		@description,
		@total_objects,
		@total_size,
		@processed_size,
		@average_speed,
		@job_source_type,
		@successes,
		@warnings,
		@failures)

--	PRINT dbo.Upgrade_TransformCppText('\tCreating XML log based on job session data...')

--	PRINT dbo.Upgrade_TransformCppText('\t\t[XML Log]=')
--	PRINT dbo.Upgrade_FormatXmlText(@xml_log, 3, DEFAULT)

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

	UPDATE
		[dbo].[Backup.Model.JobSessions]
	SET
		[log_xml] = @xml_log
	WHERE CURRENT OF sessions_cursor

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

--	PRINT dbo.Upgrade_TransformCppText('END ITERATION: BACKUP JOB SESSION')

	SET @job_count = @job_count + 1

	FETCH NEXT FROM
		sessions_cursor
	INTO
		@id,
		@start_time,
		@end_time,
		@operation,
		@description,
		@total_objects,
		@total_size,
		@processed_size,
		@average_speed,
		@job_source_type,
		@successes,
		@warnings,
		@failures
END

CLOSE sessions_cursor
DEALLOCATE sessions_cursor

GOTO Finalization

ErrorHandler:
	IF (@@trancount != 0)
	BEGIN
		PRINT dbo.Upgrade_TransformCppText('ROLLBACK TRANSACTION: BACKUP JOB SESSIONS')
		ROLLBACK TRANSACTION t_sessions
	END
	RAISERROR(@error, 11, 1)

Finalization:
	DECLARE @transaction_count int
	SET @transaction_count = @@trancount

	IF (@@trancount != 0)
	BEGIN
		PRINT dbo.Upgrade_TransformCppText('COMMIT TRANSACTION: BACKUP JOB SESSIONS')
		COMMIT TRANSACTION t_sessions
	END

	PRINT dbo.Upgrade_FormatString('\t[Transaction Count]="%s"', @transaction_count)
	PRINT dbo.Upgrade_FormatString('\t[Job Count]="%s"', @job_count)
	PRINT dbo.Upgrade_FormatString('\t[Total Read]="%s"', @@total_read)
	PRINT dbo.Upgrade_FormatString('\t[Total Write]="%s"', @@total_write)
	PRINT dbo.Upgrade_FormatString('\t[Total Errors]="%s"', @@total_errors)
	PRINT dbo.Upgrade_FormatString('\t[End Date]="%s"', convert(varchar, getdate(), 113))
	PRINT ''
GO


--------------------------------------------------------------------------------
--
-- Task Sessions
--
--------------------------------------------------------------------------------
PRINT 'Upgrading "Backup Task Sessions"...'

DECLARE @error nvarchar(max)
DECLARE @job_count int
SET @job_count = 0

PRINT dbo.Upgrade_TransformCppText('BEGIN TRANSACTION: BACKUP TASK SESSIONS')
PRINT dbo.Upgrade_FormatString('\t[Start Date]="%s"', convert(varchar, getdate(), 113))
PRINT ''
BEGIN TRANSACTION t_sessions
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

DECLARE sessions_cursor CURSOR FOR
SELECT
	[dbo].[Backup.Model.BackupTaskSessions].[id],
	[dbo].[Backup.Model.BackupTaskSessions].[session_id],
	[dbo].[Backup.Model.BackupTaskSessions].[total_objects],
	[dbo].[Backup.Model.BackupTaskSessions].[processed_objects],
	[dbo].[Backup.Model.BackupTaskSessions].[total_size],
	[dbo].[Backup.Model.BackupTaskSessions].[processed_size],
	[dbo].[Backup.Model.BackupTaskSessions].[avg_speed],
	[dbo].[Backup.Model.BackupTaskSessions].[mode],
	[dbo].[Backup.Model.BackupTaskSessions].[change_tracking],
	[dbo].[Backup.Model.BackupTaskSessions].[creation_time],
	[dbo].[Backup.Model.BackupTaskSessions].[end_time],
	[dbo].[Backup.Model.BackupTaskSessions].[operation],
	[dbo].[Backup.Model.BackupTaskSessions].[reason],
	[dbo].[Backup.Model.BackupJobSessions].[job_source_type]
FROM
	[dbo].[Backup.Model.BackupTaskSessions] RIGHT JOIN [dbo].[Backup.Model.BackupJobSessions] 
ON
	[dbo].[Backup.Model.BackupTaskSessions].[session_id] = [dbo].[Backup.Model.BackupJobSessions].[id]
WHERE
	[log_xml] IS NOT NULL AND
	[log_xml].value('count(/*[1]/*)', 'int') = 0

DECLARE @id uniqueidentifier
DECLARE @session_id uniqueidentifier
DECLARE @total_objects int
DECLARE @processed_objects int
DECLARE @total_size bigint
DECLARE @processed_size bigint
DECLARE @average_speed bigint
DECLARE @source_mode int
DECLARE @change_tracking bit
DECLARE @start_time datetime
DECLARE @end_time datetime
DECLARE @operation nvarchar(400)
DECLARE @reason nvarchar(max)
DECLARE @job_source_type int

OPEN sessions_cursor

FETCH NEXT FROM
	sessions_cursor
INTO
	@id,
	@session_id,
	@total_objects,
	@processed_objects,
	@total_size,
	@processed_size,
	@average_speed,
	@source_mode,
	@change_tracking,
	@start_time,
	@end_time,
	@operation,
	@reason,
	@job_source_type

WHILE @@fetch_status = 0
BEGIN                                                             
--	PRINT dbo.Upgrade_TransformCppText('BEGIN ITERATION: BACKUP TASK SESSION')
--	PRINT dbo.Upgrade_TransformCppText('\tLoading "Backup Task Session":')
--	PRINT dbo.Upgrade_FormatString('\t\t[Id]="%s"', @id)
--	PRINT dbo.Upgrade_FormatString('\t\t[Session Id]="%s"', @session_id)
--	PRINT dbo.Upgrade_FormatString('\t\t[Start Time]="%s"', @start_time)
--	PRINT dbo.Upgrade_FormatString('\t\t[End Time]="%s"', @end_time)
--	PRINT dbo.Upgrade_FormatString('\t\t[Operation]="%s"', @operation)
--	PRINT dbo.Upgrade_FormatString('\t\t[Reason="%s"', cast(@reason as nvarchar(255)))
--	PRINT dbo.Upgrade_FormatString('\t\t[Total Objects]="%s"', @total_objects)
--	PRINT dbo.Upgrade_FormatString('\t\t[Total Size]="%s"', @total_size)
--	PRINT dbo.Upgrade_FormatString('\t\t[Processed Size]="%s"', @processed_size)
--	PRINT dbo.Upgrade_FormatString('\t\t[Average Speed]="%s"', @average_speed)
--	PRINT dbo.Upgrade_FormatString('\t\t[Source Mode]="%s"', @source_mode)
--	PRINT dbo.Upgrade_FormatString('\t\t[Change Tracking]="%s"', @change_tracking)
--	PRINT dbo.Upgrade_FormatString('\t\t[Job Source Type]="%s"', @average_speed)

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

--	PRINT dbo.Upgrade_TransformCppText('\tCreating text report based on task session data...')

	-- Build Report

	DECLARE @xml_log nvarchar(max)
	SET @xml_log = dbo.Upgrade_CreateBackupTaskSessionXmlLog(
		@id,
		@session_id,
		@start_time,
		@end_time,
		@operation,
		@reason,
		@total_objects,
		@total_size,
		@processed_objects,
		@processed_size,
		@average_speed,
		@source_mode,
		@change_tracking,
		@job_source_type)

--	PRINT dbo.Upgrade_TransformCppText('\tCreating XML log based on task session data...')

--	PRINT dbo.Upgrade_TransformCppText('\t\t[XML Log]=')
--	PRINT dbo.Upgrade_FormatXmlText(@xml_log, 3, DEFAULT)

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

	UPDATE
		[dbo].[Backup.Model.BackupTaskSessions]
	SET
		[log_xml] = @xml_log
	WHERE CURRENT OF sessions_cursor

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

--	PRINT dbo.Upgrade_TransformCppText('END ITERATION: BACKUP TASK SESSION')

	SET @job_count = @job_count + 1

	FETCH NEXT FROM
		sessions_cursor
	INTO
		@id,
		@session_id,
		@total_objects,
		@processed_objects,
		@total_size,
		@processed_size,
		@average_speed,
		@source_mode,
		@change_tracking,
		@start_time,
		@end_time,
		@operation,
		@reason,
		@job_source_type
END

CLOSE sessions_cursor
DEALLOCATE sessions_cursor

GOTO Finalization

ErrorHandler:
	IF (@@trancount != 0)
	BEGIN
		PRINT dbo.Upgrade_TransformCppText('ROLLBACK TRANSACTION: BACKUP TASK SESSIONS')
		ROLLBACK TRANSACTION t_sessions
	END
	RAISERROR(@error, 11, 1)

Finalization:
	DECLARE @transaction_count int
	SET @transaction_count = @@trancount

	IF (@@trancount != 0)
	BEGIN
		PRINT dbo.Upgrade_TransformCppText('COMMIT TRANSACTION: BACKUP TASK SESSIONS')
		COMMIT TRANSACTION t_sessions
	END

	PRINT dbo.Upgrade_FormatString('\t[Transaction Count]="%s"', @transaction_count)
	PRINT dbo.Upgrade_FormatString('\t[Task Count]="%s"', @job_count)
	PRINT dbo.Upgrade_FormatString('\t[Total Read]="%s"', @@total_read)
	PRINT dbo.Upgrade_FormatString('\t[Total Write]="%s"', @@total_write)
	PRINT dbo.Upgrade_FormatString('\t[Total Errors]="%s"', @@total_errors)
	PRINT dbo.Upgrade_FormatString('\t[End Date]="%s"', convert(varchar, getdate(), 113))
	PRINT ''
GO


--------------------------------------------------------------------------------
--
-- End Upgrade
--
--------------------------------------------------------------------------------
PRINT 'Finishing database upgrade...'
PRINT dbo.Upgrade_FormatString('\t[Time Stamp]="%s"', convert(varchar, getdate(), 113))
PRINT ''

GO


--------------------------------------------------------------------------------
--
-- Finalization
--
--------------------------------------------------------------------------------
PRINT 'Deleting [dbo].[Upgrade_IsEmptyString]'
EXEC [dbo].[Upgrade_DbDeleteFunction] '[dbo].[Upgrade_IsEmptyString]'

PRINT 'Deleting [dbo].[Upgrade_IsBlankString]'
EXEC [dbo].[Upgrade_DbDeleteFunction] '[dbo].[Upgrade_IsBlankString]'

PRINT 'Deleting [dbo].[Upgrade_RightTrimString]'
EXEC [dbo].[Upgrade_DbDeleteFunction] '[dbo].[Upgrade_RightTrimString]'

PRINT 'Deleting [dbo].[Upgrade_EscapeText]'
EXEC [dbo].[Upgrade_DbDeleteFunction] '[dbo].[Upgrade_EscapeText]'

PRINT 'Deleting [dbo].[Upgrade_SplitString]'
EXEC [dbo].[Upgrade_DbDeleteFunction] '[dbo].[Upgrade_SplitString]'

PRINT 'Deleting [dbo].[Upgrade_TransformCppText]'
EXEC [dbo].[Upgrade_DbDeleteFunction] '[dbo].[Upgrade_TransformCppText]'

PRINT 'Deleting [dbo].[Upgrade_FormatString]'
EXEC [dbo].[Upgrade_DbDeleteFunction] '[dbo].[Upgrade_FormatString]'

PRINT 'Deleting [dbo].[Upgrade_FormatString2]'
EXEC [dbo].[Upgrade_DbDeleteFunction] '[dbo].[Upgrade_FormatString2]'

PRINT 'Deleting [dbo].[Upgrade_FormatString3]'
EXEC [dbo].[Upgrade_DbDeleteFunction] '[dbo].[Upgrade_FormatString3]'

PRINT 'Deleting [dbo].[Upgrade_FormatString4]'
EXEC [dbo].[Upgrade_DbDeleteFunction] '[dbo].[Upgrade_FormatString4]'

PRINT 'Deleting [dbo].[Upgrade_FormatString5]'
EXEC [dbo].[Upgrade_DbDeleteFunction] '[dbo].[Upgrade_FormatString5]'

PRINT 'Deleting [dbo].[Upgrade_FormatString6]'
EXEC [dbo].[Upgrade_DbDeleteFunction] '[dbo].[Upgrade_FormatString6]'

PRINT 'Deleting [dbo].[Upgrade_FormatString7]'
EXEC [dbo].[Upgrade_DbDeleteFunction] '[dbo].[Upgrade_FormatString7]'

PRINT 'Deleting [dbo].[Upgrade_FormatString8]'
EXEC [dbo].[Upgrade_DbDeleteFunction] '[dbo].[Upgrade_FormatString8]'

PRINT 'Deleting [dbo].[Upgrade_FormatString9]'
EXEC [dbo].[Upgrade_DbDeleteFunction] '[dbo].[Upgrade_FormatString9]'

PRINT 'Deleting [dbo].[Upgrade_FormatText]'
EXEC [dbo].[Upgrade_DbDeleteFunction] '[dbo].[Upgrade_FormatText]'

PRINT 'Deleting [dbo].[Upgrade_FormatXmlText]'
EXEC [dbo].[Upgrade_DbDeleteFunction] '[dbo].[Upgrade_FormatXmlText]'

PRINT 'Deleting [dbo].[Upgrade_FormatXmlDocument]'
EXEC [dbo].[Upgrade_DbDeleteFunction] '[dbo].[Upgrade_FormatXmlDocument]'

PRINT 'Deleting [dbo].[Upgrade_FormatIso8601DateTime]'
EXEC [dbo].[Upgrade_DbDeleteFunction] '[dbo].[Upgrade_FormatIso8601DateTime]'

PRINT 'Deleting [dbo].[Upgrade_FormatDateTime]'
EXEC [dbo].[Upgrade_DbDeleteFunction] '[dbo].[Upgrade_FormatDateTime]'

PRINT 'Deleting [dbo].[Upgrade_FormatUtcInvariantDateTime]'
EXEC [dbo].[Upgrade_DbDeleteFunction] '[dbo].[Upgrade_FormatUtcInvariantDateTime]'

PRINT 'Deleting [dbo].[Upgrade_FormatDateTimeDiff]'
EXEC [dbo].[Upgrade_DbDeleteFunction] '[dbo].[Upgrade_FormatDateTimeDiff]'

PRINT 'Deleting [dbo].[Upgrade_InternalKb]'
EXEC [dbo].[Upgrade_DbDeleteFunction] '[dbo].[Upgrade_InternalKb]'

PRINT 'Deleting [dbo].[Upgrade_InternalMb]'
EXEC [dbo].[Upgrade_DbDeleteFunction] '[dbo].[Upgrade_InternalMb]'

PRINT 'Deleting [dbo].[Upgrade_InternalGb]'
EXEC [dbo].[Upgrade_DbDeleteFunction] '[dbo].[Upgrade_InternalGb]'

PRINT 'Deleting [dbo].[Upgrade_InternalTb]'
EXEC [dbo].[Upgrade_DbDeleteFunction] '[dbo].[Upgrade_InternalTb]'

PRINT 'Deleting [dbo].[Upgrade_FormatSize]'
EXEC [dbo].[Upgrade_DbDeleteFunction] '[dbo].[Upgrade_FormatSize]'

PRINT 'Deleting [dbo].[Upgrade_FormatRate]'
EXEC [dbo].[Upgrade_DbDeleteFunction] '[dbo].[Upgrade_FormatRate]'

PRINT 'Deleting [dbo].[Upgrade_GetFileSystemType]'
EXEC [dbo].[Upgrade_DbDeleteFunction] '[dbo].[Upgrade_GetFileSystemType]'

PRINT 'Deleting [dbo].[Upgrade_FileSystemIsRoot]'
EXEC [dbo].[Upgrade_DbDeleteFunction] '[dbo].[Upgrade_FileSystemIsRoot]'

PRINT 'Deleting [dbo].[Upgrade_FileSystemComparePaths]'
EXEC [dbo].[Upgrade_DbDeleteFunction] '[dbo].[Upgrade_FileSystemComparePaths]'

PRINT 'Deleting [dbo].[Upgrade_GetFileSystemParentFolder]'
EXEC [dbo].[Upgrade_DbDeleteFunction] '[dbo].[Upgrade_GetFileSystemParentFolder]'

PRINT 'Deleting [dbo].[Upgrade_GetFileSystemParentFolderEx]'
EXEC [dbo].[Upgrade_DbDeleteFunction] '[dbo].[Upgrade_GetFileSystemParentFolderEx]'

PRINT 'Deleting [dbo].[Upgrade_GetFileSystemSeparators]'
EXEC [dbo].[Upgrade_DbDeleteFunction] '[dbo].[Upgrade_GetFileSystemSeparators]'

PRINT 'Deleting [dbo].[Upgrade_FileSystemRemoveSeparator]'
EXEC [dbo].[Upgrade_DbDeleteFunction] '[dbo].[Upgrade_FileSystemRemoveSeparator]'

PRINT 'Deleting [dbo].[Upgrade_FileSystemAddSeparator]'
EXEC [dbo].[Upgrade_DbDeleteFunction] '[dbo].[Upgrade_FileSystemAddSeparator]'

PRINT 'Deleting [dbo].[Upgrade_FileSystemBuildPath]'
EXEC [dbo].[Upgrade_DbDeleteFunction] '[dbo].[Upgrade_FileSystemBuildPath]'

PRINT 'Deleting [dbo].[Upgrade_GetBackupRepositoryType]'
EXEC [dbo].[Upgrade_DbDeleteFunction] '[dbo].[Upgrade_GetBackupRepositoryType]'

PRINT 'Deleting [dbo].[Upgrade_GetBackupRepositoryTypeName]'
EXEC [dbo].[Upgrade_DbDeleteFunction] '[dbo].[Upgrade_GetBackupRepositoryTypeName]'

PRINT 'Deleting [dbo].[Upgrade_GetJobTypeName]'
EXEC [dbo].[Upgrade_DbDeleteFunction] '[dbo].[Upgrade_GetJobTypeName]'

PRINT 'Deleting [dbo].[Upgrade_GetJobSourceTypeName]'
EXEC [dbo].[Upgrade_DbDeleteFunction] '[dbo].[Upgrade_GetJobSourceTypeName]'

PRINT 'Deleting [dbo].[Upgrade_GetTaskSourceModeName]'
EXEC [dbo].[Upgrade_DbDeleteFunction] '[dbo].[Upgrade_GetTaskSourceModeName]'

PRINT 'Deleting [dbo].[Upgrade_GetTaskChangeTrackingName]'
EXEC [dbo].[Upgrade_DbDeleteFunction] '[dbo].[Upgrade_GetTaskChangeTrackingName]'

PRINT 'Deleting [dbo].[Upgrade_CreateBackupJobSessionXmlLog]'
EXEC [dbo].[Upgrade_DbDeleteFunction] '[dbo].[Upgrade_CreateBackupJobSessionXmlLog]'

PRINT 'Deleting [dbo].[Upgrade_CreateBackupTaskSessionXmlLog]'
EXEC [dbo].[Upgrade_DbDeleteFunction] '[dbo].[Upgrade_CreateBackupTaskSessionXmlLog]'

PRINT 'Deleting [dbo].[Upgrade_CreateXmlLogRecord]'
EXEC [dbo].[Upgrade_DbDeleteFunction] '[dbo].[Upgrade_CreateXmlLogRecord]'

PRINT 'Deleting [dbo].[Upgrade_CreateDefaultBackupRepositoryOptions]'
EXEC [dbo].[Upgrade_DbDeleteFunction] '[dbo].[Upgrade_CreateDefaultBackupRepositoryOptions]'

PRINT 'Deleting [dbo].[Upgrade_UpdateJobOptions]'
EXEC [dbo].[Upgrade_DbDeleteFunction] '[dbo].[Upgrade_UpdateJobOptions]'

PRINT 'Deleting [dbo].[Upgrade_UpdateJobScheduleOptions]'
EXEC [dbo].[Upgrade_DbDeleteFunction] '[dbo].[Upgrade_UpdateJobScheduleOptions]'

PRINT 'Deleting [dbo].[Upgrade_UpdateJobVssOptions]'
EXEC [dbo].[Upgrade_DbDeleteFunction] '[dbo].[Upgrade_UpdateJobVssOptions]'

PRINT 'Deleting [dbo].[Upgrade_UpdateLegacyReplicaJobOptions]'
EXEC [dbo].[Upgrade_DbDeleteFunction] '[dbo].[Upgrade_UpdateLegacyReplicaJobOptions]'

GO


IF EXISTS (SELECT * FROM sys.objects WHERE name = 'db50_update' AND type = 'D')
BEGIN
	DROP DEFAULT db50_update
END
GO

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'db50_logging' AND type = 'D')
BEGIN
	DROP DEFAULT db50_logging
END
GO

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'db50_sessions_update' AND type = 'D')
BEGIN
	DROP DEFAULT db50_sessions_update
END
GO


PRINT 'Deleting [dbo].[Upgrade_Log]'
EXEC [dbo].[Upgrade_DbDeleteTable] '[dbo].[Upgrade_Log]'

PRINT 'Deleting [dbo].[Upgrade_DbDeleteTable]'
EXEC [dbo].[Upgrade_DbDeleteProcedure] '[dbo].[Upgrade_DbDeleteTable]'

PRINT 'Deleting [dbo].[Upgrade_DbDeleteFunction]'
EXEC [dbo].[Upgrade_DbDeleteProcedure] '[dbo].[Upgrade_DbDeleteFunction]'

PRINT 'Deleting [dbo].[Upgrade_DbDeleteProcedure]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Upgrade_DbDeleteProcedure]') AND type in (N'P', N'PC'))
	DROP PROCEDURE [dbo].[Upgrade_DbDeleteProcedure]
GO

--
--  445 -> 446
--
IF EXISTS (SELECT * FROM [dbo].[Version] WHERE current_version = 445)
BEGIN
	IF @@Error = 0 BEGIN UPDATE [dbo].[Version] SET current_version = 446; END
END
GO
