Migrate.sql

SET NOCOUNT ON;
SET XACT_ABORT ON;
DECLARE @OldData       varbinary(max);
DECLARE @NewData       varbinary(max) = 0x;
DECLARE @DBBinDataNewCompressedPart varbinary(max)
DECLARE @DBBinDataNewCompressedPart0 varbinary(8000)
DECLARE @DBBinDataNewCompressedPart1 varbinary(8000)
DECLARE @DBBinDataNewCompressedPart2 varbinary(8000)
DECLARE @OldHeaderSize int = 16;
DECLARE @OldRecordSize int = 8;
DECLARE @NewRecordSize int = 24;

DECLARE @Position      int;
DECLARE @RecordCount   int;
DECLARE @RecordNumber  int = 0;

DECLARE @OldRecord     binary(8);
DECLARE @SmallDateTime binary(4);
DECLARE @ReadTiny      tinyint;
DECLARE @WriteTiny     tinyint;
DECLARE @ReadBig       bigint;
DECLARE @WriteBig      bigint;
DECLARE @BitMask       binary(2);

DECLARE @PropertyName       VARCHAR(260) 
DECLARE @DBPropNamePart0    VARCHAR(260) 
DECLARE @DBPropNamePart1    VARCHAR(260) 
DECLARE @DBPropNamePart2    VARCHAR(260) 
DECLARE @DBName VARCHAR(255) 
DECLARE @V1_Exists BIT

DECLARE DB_Crs CURSOR FOR SELECT name FROM msdb.sys.extended_properties WHERE name LIKE 'PAYGO:%' AND name NOT LIKE 'PAYGO:%:[0-2]'
OPEN DB_Crs 
FETCH NEXT FROM DB_Crs INTO @PropertyName

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @V1_Exists = 0
    SET @DBName = RIGHT(@PropertyName, LEN(@PropertyName) - CHARINDEX(':', @PropertyName))
    SET @DBPropNamePart0 = @PropertyName + ':0'
    SET @DBPropNamePart1 = @PropertyName + ':1'
    SET @DBPropNamePart2 = @PropertyName + ':2'
    IF EXISTS (SELECT * FROM msdb.sys.extended_properties WHERE name = @DBPropNamePart0)
        SET @V1_Exists = 1

    IF @V1_Exists = 0
    BEGIN
        PRINT 'Migrate ' + @DBName
        SELECT @OldData = DECOMPRESS(CONVERT(varbinary(max), ep.value))
        FROM msdb.sys.extended_properties AS ep
        WHERE ep.class = 0
          AND ep.major_id = 0
          AND ep.minor_id = 0
          AND ep.name = @PropertyName;
        IF @OldData IS NULL
        BEGIN
            THROW 50000, 'Die angegebene Extended Property wurde in msdb nicht gefunden.', 1
        END
        IF DATALENGTH(@OldData) < @OldHeaderSize
        BEGIN
            THROW 50001, 'Das ByteArray ist kleiner als der erwartete 16-Byte-Header.', 1
        END
        IF (DATALENGTH(@OldData) - @OldHeaderSize) % @OldRecordSize <> 0
        BEGIN
            THROW 50002, 'Die Nutzdaten sind nicht durch die alte Datensatzgröße teilbar.', 1
        END
        SET @RecordCount = (DATALENGTH(@OldData) - @OldHeaderSize) / @OldRecordSize
        SET @Position = @OldHeaderSize + 1;
        WHILE @RecordNumber < @RecordCount
        BEGIN
            SET @OldRecord = SUBSTRING
            (
                @OldData,
                @Position,
                @OldRecordSize
            )
            SET @SmallDateTime = SUBSTRING(@OldRecord, 1, 4);
            SET @ReadTiny      = CONVERT(tinyint, SUBSTRING(@OldRecord, 5, 1));
            SET @WriteTiny     = CONVERT(tinyint, SUBSTRING(@OldRecord, 6, 1));
            SET @BitMask       = SUBSTRING(@OldRecord, 7, 2);
            SET @ReadBig  = POWER(CONVERT(bigint, @ReadTiny), 4);
            SET @WriteBig = POWER(CONVERT(bigint, @WriteTiny), 4);
            SET @NewData =
                  @NewData
                + @SmallDateTime
                + CONVERT(binary(8), @ReadBig)
                + CONVERT(binary(8), @WriteBig)
                + 0x0000
                + @BitMask;
            SET @Position += @OldRecordSize;
            SET @RecordNumber += 1;
        END
        SET @DBBinDataNewCompressedPart = COMPRESS(@NewData)
		SET @DBBinDataNewCompressedPart0 = SUBSTRING(@DBBinDataNewCompressedPart,1,7500)
		BEGIN TRY
			SET @DBBinDataNewCompressedPart1 = SUBSTRING(@DBBinDataNewCompressedPart,7501,DATALENGTH(@DBBinDataNewCompressedPart)-7500)
		END TRY
		BEGIN CATCH
			SET @DBBinDataNewCompressedPart1 = 0x
		END CATCH
		BEGIN TRY
			SET @DBBinDataNewCompressedPart2 = SUBSTRING(@DBBinDataNewCompressedPart,15001,DATALENGTH(@DBBinDataNewCompressedPart)-15000)
		END TRY
		BEGIN CATCH
			SET @DBBinDataNewCompressedPart2 = 0x
		END CATCH

        IF EXISTS (SELECT * FROM [msdb].sys.extended_properties WHERE name = @DBPropNamePart0)
		    EXEC [msdb].sys.sp_updateextendedproperty @name = @DBPropNamePart0, @value = @DBBinDataNewCompressedPart0
        ELSE 
		    EXEC [msdb].sys.sp_addextendedproperty @name = @DBPropNamePart0, @value = @DBBinDataNewCompressedPart0
        IF EXISTS (SELECT * FROM [msdb].sys.extended_properties WHERE name = @DBPropNamePart1)
		    EXEC [msdb].sys.sp_updateextendedproperty @name = @DBPropNamePart1, @value = @DBBinDataNewCompressedPart1
        ELSE 
		    EXEC [msdb].sys.sp_addextendedproperty @name = @DBPropNamePart1, @value = @DBBinDataNewCompressedPart1
        IF EXISTS (SELECT * FROM [msdb].sys.extended_properties WHERE name = @DBPropNamePart2)
		    EXEC [msdb].sys.sp_updateextendedproperty @name = @DBPropNamePart2, @value = @DBBinDataNewCompressedPart2
        ELSE 
		    EXEC [msdb].sys.sp_addextendedproperty @name = @DBPropNamePart2, @value = @DBBinDataNewCompressedPart2
    END
    FETCH NEXT FROM DB_Crs INTO @PropertyName
END
CLOSE DB_Crs
DEALLOCATE DB_Crs