Backing Up an MSSQL Database

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 TO DISK=

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.

pretty much anything you send to the server that generates “a result” should be run as SQLSelect even if, like in this case, it makes no sense

there are a few exceptions (like a return result that is NOT a recordset) but …

Point well made and well taken. I didn’t expect a result back from the BACKUP statement, but that’s what it wanted to do.

Also, I have discovered that you must wait for the action to complete before closing the recordset. That means a fixed delay or do what I finally did and query the SQL Error Log in a loop looking for a successful completion.

Thank you Matthew & Norm, that’s good to know. I was going to start some SQL Server work with Xojo in the near future.

For those that use SQL Server Stored Procedures, where the procedure does not return a result set, like when doing UPDATE, INSERT or DELETE statements only, the procedure output will still typically return an updated “row” count of the affected records - which sounds like it would qualify as a problem for SQLExecute in Xojo. If this was the case, it might be worth adding the following SQL setting to the beginning of the procedure, to turn off the row count output too:

SET NOCOUNT ON;

yeah as long as its not a rowset being returned then you can use sqlexecute
a return value like that should be safe in sqlexecute

my vague recollection is that backup does indeed return 1 row