Post reply

Issue with using Ola Hallengren Backup script

  • February 16, 2018 at 3:45 am

    #333599

    Hi, I am using OH's excellent scripts, however I have found and issue when it comes to backing up to Azure blob storage via URL.  All the backups work fine, apart from one 1TB sized database that when compressed has a backup size of around 300GB.  I have split this into 12 files backed up locally and takes around an hour.  I was using OH script however they would error with the "Device has reached the block size limit" so I did some reading and used a native SQL Backup with the following:

    BACKUP DATABASE [myDataBase] TO URL = N'https://myazurestoreage.blob.core.windows.net/mydatabasebackup1of12.bak',
     "                                                               "                                                    "
     "                                                               "                                                    "                
        URL = N'https://myazurestoreage.blob.core.windows.net/mydatabasebackup12of12.bak'
    WITH MAXTRANSFERSIZE = 4194304, BLOCKSIZE = 65536, BUFFERCOUNT = 50, COPY_ONLY, NOFORMAT, NOINIT, NAME = N'myDatabase=-Full Database Backup', NOSKIP, NOREWIND, NOUNLOAD, STATS = 5
    GO

    So this works fine, takes around an hour which is okay (I welcome any suggestions to make it quicker) .  I then try to do the same with OH scripts:

    EXECUTE dbo.DatabaseBackup
    @databases = 'myDatabase',
    @URL = 'https://myazurestoreage.blob.core.windows.net',
    @BackupType = 'FULL',
    @Compress = 'Y',
    @BlockSize = 65563,
    @BufferCount = 50,
    @MaxTransferSize = 4194304,
    @NumberOfFiles = 12,
    @verify = 'N';

    When executing this in SSMS or via job I get the following error:

    Msg 50000, Level 16, State 1, Procedure DatabaseBackup, Line 750 [Batch Start Line 0]
    The value for the parameter @BlockSize is not supported.

    Msg 50000, Level 16, State 1, Procedure DatabaseBackup, Line 764 [Batch Start Line 0]
    The value for the parameter @MaxTransferSize is not supported.

    Msg 50000, Level 16, State 1, Procedure DatabaseBackup, Line 771 [Batch Start Line 0]
    The value for the parameter @NumberOfFiles is not supported.

    Msg 50000, Level 16, State 1, Procedure DatabaseBackup, Line 911 [Batch Start Line 0]

    I have looked on OH's site (https://ola.hallengren.com/sql-server-backup.html) and sections D & E describe this so this should work.

    Thanks
    qh

    [font="Tahoma"]Who looks outside, dreams; who looks inside, awakes. – Carl Jung.[/font]

  • February 16, 2018 at 4:36 am

    #1980026

    quackhandle1975 - Friday, February 16, 2018 3:45 AM

    Hi, I am using OH's excellent scripts, however I have found and issue when it comes to backing up to Azure blob storage via URL.  All the backups work fine, apart from one 1TB sized database that when compressed has a backup size of around 300GB.  I have split this into 12 files backed up locally and takes around an hour.  I was using OH script however they would error with the "Device has reached the block size limit" so I did some reading and used a native SQL Backup with the following:

    BACKUP DATABASE [myDataBase] TO URL = N'https://myazurestoreage.blob.core.windows.net/mydatabasebackup1of12.bak',
     "                                                               "                                                    "
     "                                                               "                                                    "                
        URL = N'https://myazurestoreage.blob.core.windows.net/mydatabasebackup12of12.bak'
    WITH MAXTRANSFERSIZE = 4194304, BLOCKSIZE = 65536, BUFFERCOUNT = 50, COPY_ONLY, NOFORMAT, NOINIT, NAME = N'myDatabase=-Full Database Backup', NOSKIP, NOREWIND, NOUNLOAD, STATS = 5
    GO

    So this works fine, takes around an hour which is okay (I welcome any suggestions to make it quicker) .  I then try to do the same with OH scripts:

    EXECUTE dbo.DatabaseBackup
    @databases = 'myDatabase',
    @URL = 'https://myazurestoreage.blob.core.windows.net',
    @BackupType = 'FULL',
    @Compress = 'Y',
    @BlockSize = 65563,
    @BufferCount = 50,
    @MaxTransferSize = 4194304,
    @NumberOfFiles = 12,
    @verify = 'N';

    When executing this in SSMS or via job I get the following error:

    Msg 50000, Level 16, State 1, Procedure DatabaseBackup, Line 750 [Batch Start Line 0]
    The value for the parameter @BlockSize is not supported.

    Msg 50000, Level 16, State 1, Procedure DatabaseBackup, Line 764 [Batch Start Line 0]
    The value for the parameter @MaxTransferSize is not supported.

    Msg 50000, Level 16, State 1, Procedure DatabaseBackup, Line 771 [Batch Start Line 0]
    The value for the parameter @NumberOfFiles is not supported.

    Msg 50000, Level 16, State 1, Procedure DatabaseBackup, Line 911 [Batch Start Line 0]

    I have looked on OH's site (https://ola.hallengren.com/sql-server-backup.html) and sections D & E describe this so this should work.

    Thanks
    qh

    You should point this out to Ola.  He'd bee interesting in fixing this problem.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • February 16, 2018 at 4:45 am

    #1980029

    Jeff Moden - Friday, February 16, 2018 4:36 AM

    You should point this out to Ola.  He'd bee interesting in fixing this problem.

    Thanks, Jeff, I did send an a message since his email is on his site however am still waiting a reply.  

    qh

    [font="Tahoma"]Who looks outside, dreams; who looks inside, awakes. – Carl Jung.[/font]

  • February 16, 2018 at 5:16 am

    #1980035

    Looks like you've passed blocksize 65563 instead of 65536...  or is that a typo on your part?

    Thomas Rushton
    blog: https://thelonedba.wordpress.com

  • February 16, 2018 at 5:26 am

    #1980037

    ThomasRushton - Friday, February 16, 2018 5:16 AM

    Looks like you've passed blocksize 65563 instead of 65536...  or is that a typo on your part?

    Top spotting skills! No that's a typo (however I still checked it!) 😀

    qh

    [font="Tahoma"]Who looks outside, dreams; who looks inside, awakes. – Carl Jung.[/font]

  • February 16, 2018 at 12:23 pm

    #1980119

    From a quick look at Ola's stored procedure, the part of the code that checks those input parameters throws the "not supported" error if you specify block size, number of files<>1, or max transfer size along with a URL, so his stored procedure simply doesn't allow you to set those when doing a backup to URL.

    I'm posting the block size check as an illustration:

    IF
    @BlockSize NOT IN (512,1024,2048,4096,8192,16384,32768,65536)
    OR
    (@BlockSize IS NOT NULL AND @BackupSoftware = 'SQLBACKUP')
    OR
    (@BlockSize IS NOT NULL AND @BackupSoftware = 'SQLSAFE')
    OR
    (@BlockSize IS NOT NULL AND @URL IS NOT NULL) -- This is the check that's failing
    BEGIN
      SET @ErrorMessage = 'The value for the parameter @BlockSize is not supported.' + CHAR(13) + CHAR(10) + ' '
      RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
      SET @Error = @@ERROR
    END

    Cheers!

  • February 20, 2018 at 5:11 am

    #1980416

    Thanks Jacob, that's a great spot! (It was my next thing to check).
    I wonder if the man in question can possibly fix this issue?

    Thread calling Ola!  Thread calling Ola! 😀

    qh

    [font="Tahoma"]Who looks outside, dreams; who looks inside, awakes. – Carl Jung.[/font]

  • February 20, 2018 at 5:18 am

    #1980422

    quackhandle1975 - Tuesday, February 20, 2018 5:11 AM

    Thanks Jacob, that's a great spot! (It was my next thing to check).
    I wonder if the man in question can possibly fix this issue?

    Thread calling Ola!  Thread calling Ola! 😀

    qh

    I totally missed that.  Didn't even check the numbers because I made the terrible assumption that you had typed them correctly. :pinch:  Heh... he probably could fix it by doing a precheck and simply setting the correct max  in the code with a warning.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • February 20, 2018 at 5:44 am

    #1980424

    Is it the numbers that are wrong? I thought his script is saying if you are using a URL for backup, then you can't use the block size parameter.

    qh

    [font="Tahoma"]Who looks outside, dreams; who looks inside, awakes. – Carl Jung.[/font]

  • February 20, 2018 at 6:18 am

    #1980434

    quackhandle1975 - Tuesday, February 20, 2018 5:44 AM

    Is it the numbers that are wrong? I thought his script is saying if you are using a URL for backup, then you can't use the block size parameter.

    qh

    I know this has already been pointed out but the largest that the block size can be is 65536.  The script says 65563... the last two numbers are transposed.  Here's the listing from BOL...

    BLOCKSIZE = { blocksize | @blocksize_variable }

    Specifies the physical block size, in bytes. The supported sizes are 512, 1024, 2048, 4096, 8192, 16384, 32768, and 65536 (64 KB) bytes. The default is 65536 for tape devices and 512 otherwise. Typically, this option is unnecessary because BACKUP automatically selects a block size that is appropriate to the device. Explicitly stating a block size overrides the automatic selection of block size.

    If you are taking a backup that you plan to copy onto and restore from a CD-ROM, specify BLOCKSIZE=2048.

    As for the rest of the errors, I don't know Ola's code well enough to determine if the error caused by the incorrect block size put the screws to you or if, in fact, his code doesn't support such things when the target is a URL.  Of course, it's open source and sounds like you've already had a look. 😉

    I will tell you that the BACKUP command DOES allow all of the settings to be used when backing up to a UNC.  I've never tried it to a URL. (p.s. I don't use Ola's fine scripts.  Not because there's anything wrong with them... I just built my own a long time ago and before I even knew about Ola's scripts.)

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • February 20, 2018 at 6:24 am

    #1980438

    The 65563 is a typo. Should be 65536.

    qh

    [font="Tahoma"]Who looks outside, dreams; who looks inside, awakes. – Carl Jung.[/font]

  • February 20, 2018 at 6:40 am

    #1980443

    Apparently Ola's scripts are spot on in providing the errors that they did.  Please see the following URL:
    https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/sql-server-backup-to-url#limitations

    In particular, see the part where it says...

  •  Backup to multiple blobs in a single backup operation is only supported using block blobs and using a Shared Access Signature (SAS) token rather than the storage account key for the SQL Credential.
  •  Specifying BLOCKSIZE is not supported for page blobs.
  •  Specifying MAXTRANSFERSIZE is not supported page blobs.

    • --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • February 20, 2018 at 12:31 pm

    #1980521

    Thanks for heads up Jeff. I am using SAS token, (see my first post) where my backup script works, so I am using block blobs and not page.
    In theory using Ola's script should work as I am using the same parameters (I know it is coded not to).

    qh

    [font="Tahoma"]Who looks outside, dreams; who looks inside, awakes. – Carl Jung.[/font]

  • April 3, 2018 at 7:38 am

    #1985283

    I have released a new version of the script with support for striping of backups to Azure Blob Storage, MAXTRANSFERSIZE, and BLOCKSIZE.
    https://ola.hallengren.com/versions.html

    Could you please download the latest version and test again.
    https://ola.hallengren.com/downloads.html

    Ola Hallengren
    https://ola.hallengren.com

  • April 5, 2018 at 3:27 am

    #1985605

    Great work Ola, thank you.

    qh

    [font="Tahoma"]Who looks outside, dreams; who looks inside, awakes. – Carl Jung.[/font]

    • Viewing 15 posts - 1 through 15 (of 20 total)

    You must be logged in to reply to this topic. Login to reply