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?
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.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
July 31, 2020 at 9:31 pm
nope, no line break what so ever... thanks, i am right going to create my own backup script. thanks
July 31, 2020 at 9:49 pm
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.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
July 31, 2020 at 11:04 pm
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
Change is inevitable... Change for the better is not.
August 3, 2020 at 4:25 pm
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 ?
August 3, 2020 at 4:46 pm
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/
August 3, 2020 at 5:14 pm
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/
August 4, 2020 at 4:53 pm
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.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply