SQL Cursor

Question

I have a T-sql script to automate the backup process where our Full and differential backups are backed up using that script and striped based on size, where I need some help to update the existing Script to add another backup (Tlog) with extension .trn

 CREATE TABLE #DbSizeWithNoFreeSpace (
    SELECT [Database_Name],
        CEILING(CAST(DB_Used AS DECIMAL) / 1024.0) AS DBSpaceUsedGB,
        NULL AS [Ratio],
        NULL AS [filecount]
    INTO #DbSizeWithfilecount
    FROM #DbSizeWithNoFreeSpace
    WHERE [Database_Name] NOT IN ('tempdb')
 
    -- Declare variables to use when calculating our ratio and backup file values
    DECLARE @CurrentDBName VARCHAR(50),
        @CurrentDBSpaceUsedGB INT,
        @Ratio INT,
        @filecount1 INT,
        @MaxFileSizeGB INT,
        @MinDBSizeInRatioTable INT
 
    -- Grab our max file size that we'd make a backup file (Azure blob storage has 200 GB limit for block blobs)
    SET @MaxFileSizeGB = (
            SELECT MaxFileSizeGB
            FROM [zsqdbadb].[dbo].[dbaDatabaseBackupDrive] where DatabaseName is NULL and ExcludeBackupInd is NULL
            )
 
    -- Grab the minimum size from our ratio table
    SELECT @MinDBSizeInRatioTable = MIN(DBSize)
    FROM zsqdbadb.dbo.dbaDatabaseBackupRatio_t
 
    -- Declare a cursor using the data from our DBSizefilecount that we'll iterate through
    DECLARE database_filecount_cursor CURSOR
    FOR
    SELECT [Database_name],
        DBSpaceUsedGB,
        Ratio,
        filecount
    FROM #DbSizeWithfilecount
 
    -- Open the cursor
    OPEN database_filecount_cursor
 
    -- Grab the first record and load the values of it's columns in the variables we declared
    FETCH NEXT
    FROM database_filecount_cursor
    INTO @CurrentDBName,
        @CurrentDBSpaceUsedGB,
        @Ratio,
        @filecount1
 
    -- Start our loop through the cursor
    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- Grab the 1st ratio from our look up table that's LESS THAN OR EQUAL to the current DB's space used when we look
        SELECT TOP 1 @Ratio = RatioNumber
        FROM zsqdbadb.dbo.dbaDatabaseBackupRatio_t
        WHERE DBSize <= @CurrentDBSpaceUsedGB
        ORDER BY DBSize DESC
 
        -- Check and see if @Ratio is null, this could be because the current DB size is less than our smallest record in our ratio table
        IF (@Ratio IS NULL)
        BEGIN
            IF (@CurrentDBSpaceUsedGB <= @MinDBSizeInRatioTable)
            BEGIN
                SELECT @Ratio = RatioNumber
                FROM zsqdbadb.dbo.dbaDatabaseBackupRatio_t
                WHERE DBSize = @MinDBSizeInRatioTable
            END
        END
 
        --Calculate our backup files using our formula, note we CAST to ensure we get a minimum of 1 backup file
        SELECT @filecount1 = CEILING(CAST(@CurrentDBSpaceUsedGB AS DECIMAL) / CAST(@MaxFileSizeGB AS DECIMAL) / @Ratio)
 
        --Now that we have our values, let's populate the ratio and backup files for the current database we're iterating through
        UPDATE #DbSizeWithfilecount
        SET ratio = @ratio,
            filecount = @filecount1
        WHERE [Database_Name] = @CurrentDBName
 
        -- Get the next database from the cursor
        FETCH NEXT
        FROM database_filecount_cursor
        INTO @CurrentDBName,
            @CurrentDBSpaceUsedGB,
            @ratio,
            @filecount1
    END
 
    CLOSE database_filecount_cursor;
    DEALLOCATE database_filecount_cursor;
 
    UPDATE @db
    SET fileCount = #DbSizeWithfilecount.filecount
    FROM @db AS D
    INNER JOIN #DbSizeWithfilecount ON (D.dbName = #DbSizeWithfilecount.Database_Name)
 
    -- Clean up our temp tables
    DROP TABLE #DbSizeWithNoFreeSpace
    DROP TABLE #DbSizeWithfilecount
 
    -- *End of code block that calculated how many backup files (filecount) to be used for each database and updated the table variable @db* --
    -- Distribute files evenly among available drives
    UPDATE @db
    SET fileCount = CASE
            WHEN fileCount = 1
                THEN 1
            WHEN fileCount > coalesce(@maxFileCount, (32 / driveCount) * driveCount)
                THEN (coalesce(@maxFileCount, (32 / driveCount) * driveCount) / driveCount) * driveCount
            ELSE CASE
                    WHEN fileCount % driveCount = 0
                        THEN fileCount
                    ELSE ((fileCount / driveCount) + 1) * driveCount
                    END
            END
 
    --Debug
    --SELECT * FROM @db
    DECLARE c1 CURSOR
    FOR
    SELECT T.dbName,
        T.backupType,
        T.lastFull,
        T.drive01,
        T.drive02,
        T.drive03,
        T.drive04,
        T.drive05,
        T.drive06,
        T.drive07,
        T.drive08,
        T.drive09,
        T.drive10,
        T.drive11,
        T.drive12,
        T.drive13,
        T.drive14,
        T.drive15,
        T.drive16,
        T.driveCount,
        T.fileCount,
        T.clevel,
        T.retention,
        T.folder
    FROM @db T
    WHERE backupType IS NOT NULL
 
    OPEN c1
 
    FETCH NEXT
    FROM c1
    INTO @dbName,
        @backupType,
        @lastFull,
        @drive01,
        @drive02,
        @drive03,
        @drive04,
        @drive05,
        @drive06,
        @drive07,
        @drive08,
        @drive09,
        @drive10,
        @drive11,
        @drive12,
        @drive13,
        @drive14,
        @drive15,
        @drive16,
        @driveCount,
        @fileCount,
        @clevel,
        @retention,
        @folder
 
    WHILE @@fetch_status = 0
    BEGIN
        IF @folder IS NULL
            SET @_folder = '\SQL_Backup' + @instance + '\'
        ELSE IF @_drive01 LIKE 'http://%'
            OR @_drive01 LIKE 'https://%'
        BEGIN
            SET @_folder = rtrim(ltrim(@_folder))
 
            IF left(@_folder, 1) <> '/'
                SET @_folder = '/' + @_folder
 
            IF right(@_folder, 1) <> '/'
                SET @_folder = @_folder + '/'
        END
        ELSE
        BEGIN
            SET @_folder = rtrim(ltrim(@_folder))
 
            IF left(@_folder, 1) <> '\'
                SET @_folder = '\' + @_folder
 
            IF right(@_folder, 1) <> '\'
                SET @_folder = @_folder + '\'
        END
 
        IF @driveCount >= 1
        BEGIN
            SET @drive01 = @drive01 + @folder
 
            IF @driveCount >= 2
            BEGIN
                SET @drive02 = @drive02 + @folder
 
                IF @driveCount >= 3
                BEGIN
                    SET @drive03 = @drive03 + @folder
 
                    IF @driveCount >= 4
                    BEGIN
                        SET @drive04 = @drive04 + @folder
 
                        IF @driveCount >= 5
                        BEGIN
                            SET @drive05 = @drive05 + @folder
 
                            IF @driveCount >= 6
                            BEGIN
                                SET @drive06 = @drive06 + @folder
 
                                IF @driveCount >= 7
                                BEGIN
                                    SET @drive07 = @drive07 + @folder
 
                                    IF @driveCount >= 8
                                    BEGIN
                                        SET @drive08 = @drive08 + @folder
 
                                        IF @driveCount >= 9
                                        BEGIN
                                            SET @drive09 = @drive09 + @folder
 
                                            IF @driveCount >= 10
                                            BEGIN
                                                SET @drive10 = @drive10 + @folder
 
                                                IF @driveCount >= 11
                                                BEGIN
                                                    SET @drive11 = @drive11 + @folder
 
                                                    IF @driveCount >= 12
                                                    BEGIN
                                                        SET @drive12 = @drive12 + @folder
 
                                                        IF @driveCount >= 13
                                                        BEGIN
                                                            SET @drive13 = @drive13 + @folder
 
                                                            IF @driveCount >= 14
                                                            BEGIN
                                                                SET @drive14 = @drive14 + @folder
 
                                                                IF @driveCount >= 15
                                                                BEGIN
                                                                    SET @drive15 = @drive15 + @folder
 
                                                                    IF @driveCount >= 16
                                                                    BEGIN
                                                                        SET @drive16 = @drive16 + @folder
                                                                    END
                                                                END
                                                            END
                                                        END
                                                    END
                                                END
                                            END
                                        END
                                    END
                                END
                            END
                        END
                    END
                END
            END
        END
 
        SET @command = 'BACKUP ' + CASE @backupType
                WHEN 'T'
                    THEN 'LOG'
                ELSE 'DATABASE'
                END + ' [' + @dbName + '] TO '
 
        IF @backupType IN ('F', 'D')
        BEGIN
            SELECT @fileN = 1,
                @driveL = CASE @driveCount
                    WHEN 1
                        THEN @drive01
                    WHEN 2
                        THEN @drive02
                    WHEN 3
                        THEN @drive03
                    WHEN 4
                        THEN @drive04
                    WHEN 5
                        THEN @drive05
                    WHEN 6
                        THEN @drive06
                    WHEN 7
                        THEN @drive07
                    WHEN 8
                        THEN @drive08
                    WHEN 9
                        THEN @drive09
                    WHEN 10
                        THEN @drive10
                    WHEN 11
                        THEN @drive11
                    WHEN 12
                        THEN @drive12
                    WHEN 13
                        THEN @drive13
                    WHEN 14
                        THEN @drive14
                    WHEN 15
                        THEN @drive15
                    WHEN 16
                        THEN @drive16
                    END
 
            WHILE @fileN <= @fileCount
            BEGIN
                SET @driveS = CASE @fileN % @driveCount
                        WHEN 0
                            THEN @driveL
                        WHEN 1
                            THEN @drive01
                        WHEN 2
                            THEN @drive02
                        WHEN 3
                            THEN @drive03
                        WHEN 4
                            THEN @drive04
                        WHEN 5
                            THEN @drive05
                        WHEN 6
                            THEN @drive06
                        WHEN 7
                            THEN @drive07
                        WHEN 8
                            THEN @drive08
                        WHEN 9
                            THEN @drive09
                        WHEN 10
                            THEN @drive10
                        WHEN 11
                            THEN @drive11
                        WHEN 12
                            THEN @drive12
                        WHEN 13
                            THEN @drive13
                        WHEN 14
                            THEN @drive14
                        WHEN 15
                            THEN @drive15
                        WHEN 16
                            THEN @drive16
                        END
 
                IF (
                        @_drive01 LIKE 'https://%'
                        OR @_drive01 LIKE 'http://%'
                        )
                BEGIN
                    SET @command = @command + @crlf + CASE @fileN
                            WHEN 1
                                THEN ' '
                            ELSE ','
                            END + 'URL = ''' + @driveS + @dbName + '/' + @dbname + '_' + @backupDT + '_' + CASE @backupType
                            WHEN 'F'
                                THEN 'FULL'
                            ELSE 'DIFF'
                            END + CASE @fileCount
                            WHEN 1
                                THEN ''
                            ELSE '_' + right('0' + cast(@fileN AS VARCHAR(2)), 2)
                            END + '.bak'''
                    SET @fileN = @fileN + 1
                END
                ELSE
                BEGIN
                    SET @createfolder = @driveS + @dbName
                    SET @MD = 'mkdir' + @createfolder
                    EXEC master..xp_cmdshell @MD, no_output
                    SET @createfolder = @createfolder + '\'
                    SET @command = @command + @crlf + CASE @fileN
                            WHEN 1
                                THEN ' '
                            ELSE ','
                            END + 'DISK = ''' + @createfolder + @dbname + '_' + @backupDT + '_' + CASE @backupType
                            WHEN 'F'
                                THEN 'FULL'
                            ELSE 'DIFF'
                            END + CASE @fileCount
                            WHEN 1
                                THEN ''
                            ELSE '_' + right('0' + cast(@fileN AS VARCHAR(2)), 2)
                            END + '.bak'''
                    SET @fileN = @fileN + 1
                END
            END
 
            SET @command = @command + @crlf + ' WITH COMPRESSION, MAXTRANSFERSIZE = 4194304, BLOCKSIZE = 65536, CHECKSUM, STATS = 5, NAME = N''' + @dbName + ' - ' + CASE @backupType
                    WHEN 'F'
                        THEN 'FULL backup''' + ', RETAINDAYS = ' + CAST(@_fretentiondays AS VARCHAR(2))
                    WHEN 'D'
                        THEN 'DIFFERENTIAL database backup''' + ', DIFFERENTIAL, RETAINDAYS = ' + CAST(@_dretentiondays AS VARCHAR(2))
                    END
        END
        ELSE IF (
                @_drive01 LIKE 'https://%'
                OR @_drive01 LIKE 'http://%'
                )
        BEGIN
            SET @command = @command + @crlf + 'URL = ''' + @drive01 + @dbName + '/' + @dbname + '_' + @backupDT + '_' + 'TLOG.trn''' +
                --'DISK = ''' + @drive01 +@dbname +'_' +'LOG.trn'''+
                ' WITH RETAINDAYS = ' + CAST(@_tretentiondays AS VARCHAR(3)) + ',COMPRESSION, NAME = N''' + @dbName + ' - ' + 'TLOG backup'''
        END
        ELSE
        BEGIN
            SET @command = @command + @crlf + 'DISK = ''' + @drive01 + @dbName + '\' + @dbname + '_' + @backupDT + '_' + 'TLOG.trn''' +
                --'DISK = ''' + @drive01 +@dbname +'_' +'LOG.trn'''+
                ' WITH RETAINDAYS = ' + CAST(@_tretentiondays AS VARCHAR(3)) + ',COMPRESSION, NAME = N''' + @dbName + ' - ' + 'TLOG backup'''
        END