I am putting this here to save someone down the road some time and grief. It took me several hours to figure this one out and that's enough for the collective masses.
I have a program written for a client that uses Microsoft SQL Express 2014. As this program handles inventory and the client does monthly physical inventories I though it wise to add the ability to make a full database backup when they start, just in case something goes amiss. This is easily done with the followng T-SQL command:
BACKUP DATABASE <database name> TO DISK=<path to backup>
To run the backup I simply created the destination folder on the SQL server and then a SQL script in my code to be run via db.SQLExecute and all is done. Not.
When my SQL statement ran in Management Studio or using the ODBC test program, the backups appeared within seconds. When run in my program the backup appeared in the folder, but immediately disappeared. I also noticed that the backup file was always a little smaller than the ones created by Management Studio.
Error logs showed the backup failed, but there was no detail anywhere. I checked permissions on the folder and within SQL Server. I changed logins to use the sa account. I tried remote storage. I tried a stored procedure to do the heavy lifting. NOTHING worked.
Given that the ODBC test program from Microsoft could run the EXACT same SQL statement meant that I seemed to be dealing with a Xojo issue. But I had no real options since db.SQLExecute is pretty straightforward. It takes no optional arguments and returns nothing.
And there was the rub. When I tested with ODBC test I noticed that I got some feedback to the effect that 'X pages were processed.' So what if SQL was expecting to return data, but SQLExecute wasn't ready to accept it?
Seems strange, but I switched to using a recordset and db.SQLSelect. And (cue the choir) I have backups! Once I execute the statement I throw the recordset away, but I get completed backups.
Indeed it seems that MSSQL wants to reply to the BACKUP statement and Xojo needs to be listening.