Backup not always done with my code

[huber1]

I am using the following code (for example) for backups on the production server, after reading the Sybase documentation. I did not correctly get it, I assume:

BEGIN 
    /* Daily full backup at 10:10am to internal disk skyphos, hu: 3.2.2010 */
    BACKUP DATABASE DIRECTORY '/Volumes/skyphos/backups/sqlanywhere/backup10' 
        WAIT BEFORE START WITH CHECKPOINT LOG NO COPY
END

I thought that this should be corret, but the problem is that it is sometimes not executed. I assume it has something to do with logged in users while the command is executed.

[huber1]

I should correct my above saying: The (backup) event IS executed, but the db file is empty, i. e. 0 k, where the log file looks ok, i. e. has a size greater than 0 k. Sorry and thanks, Robert

[VolkerBarth]

I'd try to run the backup without the WAIT BEFORE clause and check whether this changes the behaviour. Adding MESSAGE TO CONSOLE statements before and after the BACKUP statement will help, too...

[Former Member]

Since you are using the WAIT BEFORE START clause, it's entirely possible it's just "blocked".

Potentially this clause could block a backup from occurring indefinitely if you are using IdleTimeout=0 or -ti 0 in your server command line arguments (and have users who leave open client applications with uncommitted data when not in the office).

If you are wondering if the backup statement is "blocked" or waiting, it should be simple to confirm. Simply execute "call sa_conn_info()" and see if any of the connections have uncommitted operations (UncommitOps). The backup won't start until each of those connections issues a commit or rollback.

---

FYI, you may want to consider alternating a backup location between days. In the (hopefully extremely rare) chance that power failure or server failure occurs during the backup, it won't affect the backup and live database.

[justin_willey]

I would strongly recommend this too - with a single backup location you run the same but real risk of losing both your live and backup databases as a result of an ill-timed incident. Hopefully you would have older backups off-line / off site, but if you have the space available it is a really good idea to rotate locations at least on alternate days.