The full backup of the database is not running properly when TRANSACTION is used in the backup sctript

We have an event that backups the database using the following line:

 BACKUP DATABASE DIRECTORY 'F:\\Backup\\Source\\BackupFull\\MMP_DH' TRANSACTION LOG RENAME;

When it is started I get the following error:

The maintenance plan has ended because of the following error:
Syntax error near 'backup option'
SQLSTATE: 42W04
SQLCODE:  -131

If I remove the TRANSACTION LOG RENAME part everything is running fine.

The version we use is 17.0.1.5963

What might be causing this?

asked 24 Jan '20, 04:47

Frank%20Vestjens's gravatar image

Frank Vestjens
1.3k354564
accept rate: 21%

The maintenance plans are implemented via events, so you can check the source code in the SYSEVENT table. Have you modified the event code yourself?

(I would be surprised when the builtin maintenance plan facility would generate incorrect code...)


That being said, the BACKUP statement itself looks correct IMHO.

(24 Jan '20, 04:57) Volker Barth
Replies hidden

I even get the error when connected to the database in SQL Central and use the Create Image Backup Wizard.

This works fine:

   BACKUP DATABASE DIRECTORY 'F:\\Backup';

These will give the error below:

   BACKUP DATABASE DIRECTORY 'F:\\Backup' TRANSACTION LOG TRUNCATE;
   BACKUP DATABASE DIRECTORY 'F:\\Backup' TRANSACTION LOG RENAME;
[SAP][ODBC Driver][SQL Anywhere]Syntax error near 'backup option'  
SQLCODE: -131
SQLSTATE: 42000
SQL Statement: BACKUP DATABASE DIRECTORY 'F:\\Backup' TRANSACTION LOG TRUNCATE


If I unload the database into a new database using the Unload ddatabase Wizard in SQL Central I get the following error:

* SQL error: Invalid user ID or role name 'SYS_UPGRADE_ROLE_ROLE' specified
An error occurred while attempting to unload the database 'MMP_DH'.

(24 Jan '20, 05:28) Frank Vestjens

Strange. I don't get those errors with 17.0.10.5963.

What does the following tell?

select * from syshistory where operation in ('INIT', 'UPGRADE')

And do you issue the stamements as DBA?

(24 Jan '20, 05:42) Volker Barth

Try running this command in dbisql...

BACKUP DATABASE DIRECTORY 'F:\\Backup' TRANSACTION LOG TRUNCATE;
(24 Jan '20, 12:33) Breck Carter

The error message "Syntax error near 'backup option'" is VERY suspicious... it means this exact code string, WITH the quotes,

'backup option'

appears somewhere in the code being executed.

For example:

Could not execute statement.
Syntax error near 'backup option' on line 1
SQLCODE=-131, ODBC 3 State="42000"
Line 1

BACKUP DATABASE DIRECTORY 'F:\\Backup' TRANSACTION 'backup option' LOG TRUNCATE

If you omit the 'quotes' you get this

Could not execute statement.
Syntax error near 'backup' on line 1
SQLCODE=-131, ODBC 3 State="42000"
Line 1

BACKUP DATABASE DIRECTORY 'F:\\Backup' TRANSACTION backup option LOG TRUNCATE

I suspect SQL Central... try using dbisql to see if you get the same error.

(24 Jan '20, 12:35) Breck Carter
Replies hidden

With dbisql I get the same error.

(30 Jan '20, 02:34) Frank Vestjens

Does the same error appear when you try to backup the SQL Anywhere 17 demo database?

As stated, I don't get those errors with the exact same build number.

(30 Jan '20, 03:23) Volker Barth

Please show us the exact command you used, and the resulting error you get in dbisql.

(30 Jan '20, 07:22) Breck Carter

Copied from ISQL error message using the Copy button:

Could not execute statement. Syntax error near 'backup' on line 1 SQLCODE=-131, ODBC 3 State="42000" Line 1

BACKUP DATABASE DIRECTORY 'F:\Backup' TRANSACTION backup option LOG TRUNCATE

(11 Feb '20, 02:46) Frank Vestjens

So your statement is

BACKUP DATABASE DIRECTORY 'F:\Backup' TRANSACTION backup option LOG TRUNCATE

? Then the words "backup otion" within the statement are plain wrong, it should simply read:

BACKUP DATABASE DIRECTORY 'F:\Backup' TRANSACTION LOG TRUNCATE


I'm really confused because in the previous posts the phrase "backup option" has only be part of the error messages you have mentioned, not part of the statements you have shown. And Breck's post from 2020-01-24 12:35 exactly shows the behaviour you have now posted. - Have I missed the point?

(11 Feb '20, 03:54) Volker Barth

The original question reported this error

Syntax error near 'backup option'

Now the reported error is this

Syntax error near 'backup'

The moral of the story is this, when SQL Anywhere says "Syntax error near 'some thing'" you should go and look for the EXACT STRING some thing because therein lies the problem :)


Tomorrow we will discuss semantic errors :)

(11 Feb '20, 09:47) Breck Carter
showing 3 of 12 show all flat view
Be the first one to answer this question!
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×84

question asked: 24 Jan '20, 04:47

question was seen: 848 times

last updated: 11 Feb '20, 09:54