Backups suddenly failing

[former_SQLA_member1694879]

We are moving DB's from v11 to v17 and backing up each DB to NAS

At first the backups using the drive letter wouldn't work, so we used the full name and it worked - now it's started to kick off again...

BEGIN 
DECLARE day_of_week VARCHAR(9); 
DECLARE backup_stmt LONG VARCHAR; 
SET day_of_week = substr(dayname(today()),1,3); 
SET backup_stmt = 'BACKUP DATABASE DIRECTORY '|| 
                  '''\\\\\\\\\\Storeage003\\\\DB2\\\\BACKUP\\\\CrescentVillasCA17\\\\' || day_of_week || ''' ';
EXECUTE IMMEDIATE backup_stmt;
END

and throws the toys out with

Could not execute statement.
Error during backup/restore: Could not create directory
"\\\\Storeage003\\DB2\\DB2\\BACKUP\\CrescentVillasCA17\\Sun" -- No such file or directory
SQLCODE = =-697, ODBC 3 State='HY000"
Line 1, column 1

Any ideas?

I am aware that the NAS name is a typo, but it is correct nonetheless.

Thanks

[VolkerBarth]

Is there a deliberately doubled "DB2" in the path? It does not appear in your code sample...

[former_SQLA_member1694879]

Mea Culpa - having one of 'those' days - only one DB2

[former_SQLA_member1694879]

They have come a long way with health care, but there is still no cure for stupid!

The ones that were working also had the share name! Sigh...

BEGIN 
DECLARE day_of_week VARCHAR(9); 
DECLARE backup_stmt LONG VARCHAR; 
SET day_of_week = substr(dayname(today()),1,3); 
SET backup_stmt = 'BACKUP DATABASE DIRECTORY '|| 
                  '''\\\\\\\\\\Storeage003\\\\DB_Backups\\\\DB2\\\\BACKUP\\\\ParkwoodNV17\\\\' || day_of_week || ''' ';
EXECUTE IMMEDIATE backup_stmt;
END

Sorry 😞

[VolkerBarth]

Glad you fixed it.

FWIW, you can use EXECUTE IMMEDIATE with the ESCAPES OFF clause - that should make it easier to handle all those quadruple backslashes...

[former_SQLA_member1694873]

Also FWIW, I've found that (at least with SA17) I can use forward slashes, even under Windows, and thereby avoid all the 'escaping' (don't know if this is documented or not). This works well for me because I can then deploy to both Windows and Linux (where the forward slashes are required).

[former_SQLA_member1694879]

Two useful bits of information 🙂

Thank you