Full backup fails when log file is included in the backup

[fvestjens]

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?

[VolkerBarth]

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.

[fvestjens]

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'.

[VolkerBarth]

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?

[chris_keating]

This unload error is because this database was upgraded. Please review: http://dcx.sap.com/index.html#sqla170/en/html/8149f24c6ce21014a572837c7f5d9dfd.html and https://apps.support.sap.com/sap/support/knowledge/preview/en/2470858.

[Breck_Carter]

Try running this command in dbisql...

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

[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.

[fvestjens]

With dbisql I get the same error.

[VolkerBarth]

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.

[Breck_Carter]

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

[fvestjens]

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

[VolkerBarth]

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?

[Breck_Carter]

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 🙂