Ola Hallengren backup has a bug

  • Siten0308

    SSCarpal Tunnel

    Points: 4334

    Hello,

    I am trying to use the transaction log backup Ola Hallengren scripts, until i came across an issue, where if the string is too long, and has a line break or goes to the next line, then it will give error message:

    Cannot open backup device 'X:\Backups\SERVER001\Database0001\Log\SERVER001$SERVER001
    _Database001_Log_20200731_203617.trn'. Operating system error 123(The filename, directory name, or volume label syntax is incorrect.).
    Msg 3013, Level 16, State 1, Line 1
    BACKUP LOG is terminating abnormally.

    does anyone have a work around or a fix for this?

    • This topic was modified 1 week, 1 day ago by  Siten0308.
  • Mr. Brian Gale

    SSC-Insane

    Points: 23059

    A work around - use shorter filenames for your backups.

    I personally don't use Ola's scripts just for the reason you found - if a bug crops up, I either need Ola to release an update or dig through the code to find the problem and correct it.

    My first thought here though is that if your file name has a line break in it, you have something wrong.  Filenames CANNOT, using any backup tool or method or even any filesystem that I am aware of, have a line break in them.  If your filename has a line break in it, then your filename is wrong.

    If you absolutely need to have a line break in a filename in your SCRIPT, you would need to add in a REPLACE to strip out the line breaks from your filename prior to running Ola's scripts (or any backup script).

    Basically, this to me sounds like a bug in your file naming convention and not a bug in Ola's script - putting a line break in a file name will not work no matter what you do.

  • Siten0308

    SSCarpal Tunnel

    Points: 4334

    nope, no line break what so ever... thanks, i am right going to create my own backup script. thanks

  • Mr. Brian Gale

    SSC-Insane

    Points: 23059

    Yeah, for something like backups, I like having short and easy to test/debug scripts rather than "all in one" scripts like Ola's.  That is just my preference, especially since Ola's works with so many different backup solutions and I only have 1 at my workplace.  I'd much rather have short and easy to manage/debug scripts in my environment for something as critical as backups.

    For the maintenance stuff (statistics updates, index maintenance, checkdb, etc), I'm fine with using a script from the internet as those are all baked into SQL so don't need to worry about an update to SQL Server or a backup tool causing the task to fail. That being said, those types of maintenance task scripts are pretty easy to write myself so I did it all on my own.  Resulted in multiple stored procedures (one for backup, one for checkdb, one for statistics updates, one for index rebuild, one for index reorganize, and a "master" procedure that calls those other ones).  We use RedGate SQL Backup for our backups, so all of my backup scripts are aimed at that tool.  And if any of the stored procedures fail, it is a short and easy script to dig through and fix.  Had the index one fail once on a vendor database due to them deciding in an update to turn OFF page locks on their tables and indexes, so my index reorganize tasks failed.  Something fun - Maintenance plans will fail at this too.  But I could fix it as I knew what  my scripts did and I knew why it failed.

    Also, not sure if you are doing this or not, but my advice is to TEST your backups regularly.  Do a restore of the backup and make sure you are running CHECKDB after doing your full backups.  Depending on how much downtime you have and how long the CHECKDB runs for, I'd recommend running it nightly.  The restore is to ensure that the backup was ACTUALLY successful.  Just because it was written to disk doesn't mean that network or SAN gremlins didn't fiddle with bits and corrupt your backup (or early stage disk failure, SAN firmware bugs, etc) and CHECKDB will tell you if the database is corrupt or not.  A corrupt database will often still backup AND restore successfully bringing the corruption along with it.

  • Jeff Moden

    SSC Guru

    Points: 996831

    Have you actually looked at the code in Ola's script to see what the issue may be?  It could be easier to fix your problem in his code than it is to write your own.

    --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".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

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

  • homebrew01

    SSC Guru

    Points: 55185

    Maybe just increase the length of a variable ?

    'X:\Backups\SERVER001\Database0001\Log\SERVER001$SERVER001
    _Database001_Log_20200731_203617.trn'

    Is not unusually long. Maybe there's a different problem ?

    • This reply was modified 5 days, 23 hours ago by  homebrew01.
  • Michael L John

    One Orange Chip

    Points: 25943

    Not sure this is a bug in Ola's script.  The max length is 259, you are at 95.

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

  • Michael L John

    One Orange Chip

    Points: 25943

    Is this a mapped drive?  If so, use the UNC path.

    Does the service account have access to the backup location?

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

  • Mr. Brian Gale

    SSC-Insane

    Points: 23059

    Also, another thought here, since the FULL backups are succeeding without a problem, maybe check the syntax on those vs the syntax on your tlog backups?

    I still like having scripts that I can support without jumping onto a forum for support and hoping someone has seen it before and knows a fix, but if you want to keep using Ola's scripts, comparing something that works to something that doesn't is a good approach.

    Also, another thing to check, are you using the most up to date version of his scripts?  As far as I know, the scripts do not have any "auto-update" functionality.  Would be cool if they did, but I think it is extra overhead with high risk and little benefit.

    Looking at the string, it looks like it is putting a newline in there for some reason.  I am not too familiar (ie not at all) with Ola's scripts as backups in my mind are too critical to trust to a script I found on the internet that has community support at best and has a lot of features I know I will never use.  I am wondering if it MIGHT be something goofly like a NVARCHAR to VARCHAR conversion that is interpreting a certain character as a newline?

    Permissions and UNC paths are another good thought (as pointed out by Michael L John)!  The reason that it didn't come to mind in my first reply was that the FULL backups are working.  Since Full's work, assuming the syntax and path is the same, I would expect the tlogs to succeed as well.  But it is good to check the basic things like that too!  If you write your own scripts and the problem is due to permissions or UNC paths, you will have the same problem in your own scripts as you do in Ola's.

Viewing 9 posts - 1 through 9 (of 9 total)

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